Python Forum
python variable in SQL query
Thread Rating:
  • 1 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
python variable in SQL query
#1
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])
[Image: bvkppsksrfavz.22.18.png]

any tips on what I do wrong?
Reply
#2
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.
Reply
#3
Actually - it's possible they're being escaped. So you can try removing the quotes, or creating a participant named 'Cleavon' or "Cleavon".
Reply
#4
(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).
Reply
#5
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

 
Reply
#6
(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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  list the files using query in python arjunaram 0 652 Mar-28-2023, 02:39 PM
Last Post: arjunaram
  python sql query single quote in a string mg24 1 996 Nov-18-2022, 08:01 PM
Last Post: deanhystad
  "SUMIF" type query in Python (help required) BlainEillimatta 0 803 Oct-06-2022, 09:08 AM
Last Post: BlainEillimatta
  MSSQL query not working in Python kat35601 0 874 Apr-12-2022, 06:44 PM
Last Post: kat35601
  Error using mariadb select query with form in python? shams 2 1,956 Jul-29-2021, 12:30 PM
Last Post: shams
  Python mysql query help please tduckman 4 4,245 Mar-13-2020, 03:42 PM
Last Post: Marbelous
  SQL query with a variable amount of parameters Antares 10 6,746 Jul-08-2019, 02:24 PM
Last Post: Antares
  Add Variable to select query UtiliseIT 2 2,602 May-03-2019, 11:45 AM
Last Post: UtiliseIT
  SQLite Query in Python rowyourboat 2 2,770 Apr-26-2019, 02:24 PM
Last Post: Larz60+
  Python Turtle and order of implementation query Parsleigh 2 2,707 Mar-04-2019, 02:43 PM
Last Post: Parsleigh

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020