How to insert variable data into postgresql?

Question

I have created a database with postgresql. I am able to insert string values into table. I want to insert variable data into table. But I am getting invalid syntax for integer error.

import psycopg2

temp= 30
hum = 50

conn = psycopg2.connect('dbname=test')

cur = conn.cursor()

cur.execute("INSERT INTO DHT11 (temperature, humidity) VALUES('temp','hum')")
conn.commit() 
cur.execute('select * from DHT11')


results = cur.fetchall()

for result in results:

    print(result)

Please provide the entire error output. See [ask], [help/on-topic]. — AMC, Jul 06 '20 at 20:44

score 0 · Answer 1 · answered Jul 06 '20 at 20:35

Please use parameters when passing values into your queries, like so:

    import psycopg2

    temp= 30
    hum = 50

    conn = psycopg2.connect('dbname=test')

    cur = conn.cursor()

    cur.execute("INSERT INTO DHT11 (temperature, humidity) VALUES(%s, %s)", (temp, hum))
    conn.commit() 
    cur.execute('select * from DHT11')


    results = cur.fetchall()

    for result in results:

        print(result)

When you use this form you protect yourself from SQL Injection. The driver also takes care of escaping and quoting the values you are passing into the query.

In your original code, putting 'temp' and 'hum' in your query string tried to put the literal values, temp and hum into your table. The strings are not integers.

How to insert variable data into postgresql?

1 Answers1