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

Question

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

Adrian Klaver · Accepted Answer · 2022-05-03 22:11:39Z

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

edited May 3, 2022 at 22:11

answered May 3, 2022 at 15:17

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jack Oz Over a year ago

Adrian thank yo so much, it really works. I appreciate it. I have 2 short questions 1)what if I want to obtain all the fields? When I send "*" argument into the field_name parameter I receive an error. 2)If I want to obtain multiple fields like FullName, EmailAddress, how to structure the arguments?

Jack Oz Over a year ago

Yes, I do use psycopg2, Than you so much.

Jack Oz Over a year ago

I understand thank so much for your time, Adrian. It is very clear.

Adrian Klaver Over a year ago

If it answers your question will you accept the question and/or upvote it?

Jack Oz Over a year ago

Hi Adrian, of course I would. I was searching for where I can upvote for you, I am new in stackoverflow.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related