Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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!

share|improve this question
    
Normally table structures are something fixed. Dynamic tables are a design error most of the times. –  Daniel Jul 16 '14 at 19:45
1  
Possible duplicate of stackoverflow.com/questions/13793399/… –  dano Jul 16 '14 at 19:46
    
Definitely a duplicate! –  Girish Jul 16 '14 at 19:51
1  
Same problem? Yes. Duplicate? No. –  Clodoaldo Neto Jul 16 '14 at 19:55

2 Answers 2

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))
    )
share|improve this answer
    
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 Jul 16 '14 at 20:44
up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.