Hello I am getting error from my code, can someone help me please?
def query_builder(self, field_name, table_name, pkey, id):
queryx=sql.SQL("select {field} from {table} where {pkey} = %s",(id)).format(
field=sql.Identifier(field_name),
table=sql.Identifier(table_name),
pkey=sql.Identifier(pkey))
self.cur.execute(queryx.as_string(self.conn))
I'm going to assume you are using psycopg2.
If so the issues are, first:
"select {field} from {table} where {pkey} = %s",(id) ..."
Do not include the argument (id) in the string. Also this is not proper form for a single value in a tuple. Python requires it be (id,), note the comma.
Second:
self.cur.execute(queryx.as_string(self.conn))
Should be:
self.cur.execute(queryx, (id,))
The execute is where you supply the argument. Also the composable sql.SQL(...) can be passed directly to execute without being run through as_string. See here sql for more examples.
UPDATE
To use "*" there are two ways:
cur.execute(sql.SQL("select * from {table} where {pkey} = %s).format(table.sql.Identifier(table_name), pkey=sql.Identifier(pkey))
--OR
cur.execute(sql.SQL("select {field} from {table} where {pkey} = %s).format(field=sql.SQL("*"), table=sql.Identifier(table_name), pkey=sql.Identifier(pkey))
Warning, the second does allow for SQL injection as sql.SQL() does not escape values.
As to multiple fields the sql section of the docs has multiple examples. For instance:
If part of your query is a variable sequence of arguments, such as a comma-separated list of field names, you can use the SQL.join() method to pass them to the query:
query = sql.SQL("select {fields} from {table}").format(
fields=sql.SQL(',').join([
sql.Identifier('field1'),
sql.Identifier('field2'),
sql.Identifier('field3'),
]),
table=sql.Identifier('some_table'))
