Python Forum

Hi,

When I use an integer as variable in my SQL query it works as it should. However if work with a string It does not work, I don't get an error message but also not results.

THis is the code that works (with variable as integer)

import psycopg2 as p


conn = p.connect ("dbname='participants_db' user='postgres' host= 'localhost'")
cur = conn.cursor()
x = 5
cur.execute("UPDATE participants SET participant_paid = 'Yes' WHERE participant_id = '%s'", [x])
conn.commit()

If I change the variable to a string and change my query to %%S instead of %, I don't get an error message but also no results

x = ('Cleavon')
cur.execute("UPDATE participants SET participant_paid = 'Yes' WHERE participant_name = '%%s'", [x])

any tips on what I do wrong?

I haven't investigated deeply, but on guess is that the %%s generates the quotes for you, so when you include them in the string they're redundant. I would expect an error message from that though.

Actually - it's possible they're being escaped. So you can try removing the quotes, or creating a participant named 'Cleavon' or "Cleavon".

(Nov-22-2017, 10:12 PM)micseydel Wrote: [ -> ]Actually - it's possible they're being escaped. So you can try removing the quotes, or creating a participant named 'Cleavon' or "Cleavon".

I have tried removing the quotes without success.

When I fetch the name from the database it looks like this:

Output:
('Cleavon',), ('Cletus',),

Modifying my variable to include the comma did not result in a success either (still no error message).

from the docs

Quote: 

• The placeholder must not be quoted. Psycopg will add quotes where needed:
  >>> cur.execute("INSERT INTO numbers VALUES ('%s')", (10,)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,)) # correct
  
  

 

(Nov-23-2017, 10:01 AM)buran Wrote: [ -> ]from the docs

Quote: 

• The placeholder must not be quoted. Psycopg will add quotes where needed:
  >>> cur.execute("INSERT INTO numbers VALUES ('%s')", (10,)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,)) # correct
  
  

Thanks!, stupid I missed it