I can't use variables in psycopg2

LpCoutinho 0
Viewed 43

I need insert values in may DB... if i try this way it works:

INSERT INTO mqtt_recvd_log(recvd_cmd_id,mqtt_topic,mqtt_payload)VALUES(1,'d/status/3C61050259A4','A4 21 07 40 F5 20 85 F4 08 00 00 00 08 00 08 00 08 00 05 09 16 12 1C ');

But I want to insert by variable. So I tried several ways and none was successful:

topic = 'd/status/3C61050259A4'
payload= 'A4 21 07 40 F5 20 85 F4 08 00 00 00 08 00 08 00 08 00 05 09 16 12 1C'

conn = psycopg2.connect("dbname='test' user='postgres' password='admin' host='localhost'")
cur = conn.cursor()
cur.execute("""
            INSERT INTO mqtt_recvd_log (recvd_cmd_id,mqtt_topic,mqtt_payload)
            VALUES (1, %s, %s)
            """, (topic, payload))
records = cur.fetchall()
print(records)

and:

sql_command = "INSERT INTO mqtt_recvd_log(recvd_cmd_id,mqtt_topic,mqtt_payload)VALUES(1,'%s','%s');"
        
conn = psycopg2.connect("dbname='test' user='postgres' password='admin' host='localhost'")
        
cur = conn.cursor()
        
cur.execute(sql_command, topic, payload)
        
records = cur.fetchall()
print(records)

and

cur.execute(f"INSERT INTO mqtt_recvd_log(recvd_cmd_id,mqtt_topic,mqtt_payload)VALUES(1,'{topic}','{payload}');")

in all cases returns the error:

records = cur.fetchall()
psycopg2.ProgrammingError: no results to fetch

I've looked here and elsewhere on how to fix this error but haven't been successful. can anybody help me?

psycopg2 python
2 Answers

fetchall() call must follow a query to fetch the rows. Calling fetchall() following an insert or update command will not have rows to return.

A ProgrammingError is raised if the previous call to execute*() did not produce any result set.

cur.execute("SELECT * FROM mqtt_recvd_log")
records = cur.fetchall()
for row in records:
  print(row)
userjxh20

Your first example should work if you use RETURNING. Also might as well just use fetchone() as you will only have a single record returned:

topic = 'd/status/3C61050259A4'
payload= 'A4 21 07 40 F5 20 85 F4 08 00 00 00 08 00 08 00 08 00 05 09 16 12 1C'

conn = psycopg2.connect("dbname='test' user='postgres' password='admin' host='localhost'")
cur = conn.cursor()
cur.execute("""
            INSERT INTO mqtt_recvd_log (recvd_cmd_id,mqtt_topic,mqtt_payload)
            VALUES (1, %s, %s) RETURNING recvd_cmd_id,mqtt_topic,mqtt_payload
            """, (topic, payload))
records = cur.fetchone()
print(records)

FYI, your second example will fail because the parameter values topic and payload need to be in a sequence e.g. a tuple or list.

Your third example form is an invitation to SQL injection and should never be used. Per docs Parameters:

Warning

Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

It would be worth it to read the Parameters link above a couple of times.

Adrian Klaver0
Related