'Postgres: Python: TypeError: SQL.__init__() takes 2 positional arguments but 3 were given

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))
postgresql


Solution 1:[1]

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'))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions