How to use variables in PostgreSQL in Python

Question

My Sounds table has 7 columns: Start_Time, End_Time, Salience_Label, Class_label, Bitrate, Bitdepth, Samplerate.

I want to insert some values into this table with the command

cursor.execute("INSERT INTO Sounds VALUES (%s, %s, %s, %s, %s, %s, %s)",(start_time, end_time, salience_label, class_label, samplerate, bitrate, bitdepth))



try:
   conn = psycopg2.connect(conn_string)
   cursor = conn.cursor()

.... doing staff for getting values for my variables ...

   cursor.execute("INSERT INTO Sounds VALUES (%s, %s, %s, %s, %s, %s, %s)",(start_time, end_time, salience_label, class_label, samplerate, bitrate, bitdepth))
   print "Connected!\n"
except:
 print "I am unable to connect to the database"

cursor.close()
conn.close()
print('Close conection')

when i check the table Sounds there are no values in the table.I have also a except: print "I am unable to connect to the database" and at the terminal i got this message. — Aneokoena Aneokoena
– Aneokoena Aneokoena, Commented Oct 2, 2015 at 19:58
Isn't it supposed to be ? for parameter replacement in cursor.execute instead of %s? — Christopher Mahan
– Christopher Mahan, Commented Oct 2, 2015 at 19:58
Please include the exact error message in the question. Also, give a bit more context, as even though the failure occurs on cursor.execute, the error/mistake/omission causing it is probably earlier. (How do you obtain cursor?) — das-g
– das-g, Commented Oct 2, 2015 at 20:02
@ChristopherMahan apparently not, per stackoverflow.com/questions/19235686/… — Adam Smith
– Adam Smith, Commented Oct 2, 2015 at 20:11

Clodoaldo Neto · Accepted Answer · 2015-10-03 11:48:10Z

1

While testing do not catch exceptions. Make the parameters a single tuple as Psycopg will adapt it to a record. Use mogrify to check what is being sent to the server:

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()

insert = "insert into Sounds values %s"
parameters = (
    start_time, end_time, salience_label, 
    class_label, samplerate, bitrate, bitdepth
)

print cursor.mogrify(insert, (parameters,))
cursor.execute(insert, (parameters,))

conn.commit()
cursor.close()
conn.close()

BTW, the good practice is to name the columns which will receive the data like in:

insert into t (col_a, col_b) values (1, 'a')

That will avoid some problems.

edited Oct 3, 2015 at 11:48

answered Oct 2, 2015 at 20:49

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aneokoena Aneokoena Over a year ago

THX MAN IT IS WORKING FINALLYYYYY !

Collectives™ on Stack Overflow

How to use variables in PostgreSQL in Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related