Using python and postgres, variables within execute function?

Question

I had a question regarding the usage of variables inside a python function which accesses the PostgreSQL server. For example, the following:

def delete():
    cur.execute(
    """DELETE FROM potluck 
    WHERE name = var_1;"""

However, If I wanted the update function to take in variables for var_1, how would I do so?

For example, I want my function to be in the form:

def delete(var_1):
    cur.execute(
    """DELETE FROM potluck 
    WHERE name = %s;""", (var_1))

However, just typing that didn't work.

In addition, how about in the case when:

def delete(name, var_1):
    cur.execute(
    """DELETE FROM potluck 
    WHERE %s = %s;""", (name, var_1))

where I don't want "name" to have quotation marks when it is inserted into the string?

Any help would be appreciated!

Normally table structures are something fixed. Dynamic tables are a design error most of the times. — Daniel
– Daniel, Commented Jul 16, 2014 at 19:45
Possible duplicate of stackoverflow.com/questions/13793399/… — dano
– dano, Commented Jul 16, 2014 at 19:46

Clodoaldo Neto · Accepted Answer · 2014-07-16 19:55:09Z

4

To pass identifiers use psycopg2.extensions.AsIs

from psycopg2.extensions import AsIs

def update(table_name, var_1, var_2):
    cur.execute("""
        UPDATE %s
        SET %s = 'Y'
        WHERE %s = 'John';
        """,
        (AsIs(table_name), AsIs(var_1), AsIs(var_2))
    )

answered Jul 16, 2014 at 19:55

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

jj172 Over a year ago

sorry, I changed the question drastically since you answered, so I apologize for wasting your time. However, I did figure out the answer! Thanks!

jj172 · Accepted Answer · 2014-07-16 21:04:59Z

SOLVED:

I see what I was doing wrong. The only change I needed to make was add a comma after var_1, since: "For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple".

For example:

def delete(var_1):
    cur.execute(
    """DELETE FROM potluck 
    WHERE name = %s;""", (var_1,))

This works. I got the info from:

http://initd.org/psycopg/docs/usage.html#sql-injection

In the second case, then please reference the other answer below, which uses AsIs. That works. For example:

def delete(name, var_1):
    cur.execute(
    """DELETE FROM potluck 
    WHERE %s = %s;""", (AsIs(name), var_1))

That does the trick. Thanks!

Collectives™ on Stack Overflow

Using python and postgres, variables within execute function?

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related