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, Oct 02 '15 at 19:58
Isn't it supposed to be ? for parameter replacement in cursor.execute instead of %s? — Christopher Mahan, Oct 02 '15 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, Oct 02 '15 at 20:02
Im using conn = psycopg2.connect(conn_string) cursor = conn.cursor() and after i do some things to get the right values for my variables.After this i execute cursor.execute("INSERT INTO Sounds VALUES (%s, %s, %s, %s, %s, %s, %s)",(start_time, end_time, salience_label, class_label, samplerate, bitrate, bitdepth)) — Aneokoena Aneokoena, Oct 02 '15 at 20:03
@ChristopherMahan apparently not, per http://stackoverflow.com/questions/19235686/psycopg2-insert-into-table-with-placeholders — Adam Smith, Oct 02 '15 at 20:11
@AneokoenaAneokoena Could you edit your last comment into your question so it's preserved? Looks like we'll need a bit more context, anyway. — Adam Smith, Oct 02 '15 at 20:14

Clodoaldo Neto · Accepted Answer · 2015-10-03T11:48:10.337

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.

THX MAN IT IS WORKING FINALLYYYYY ! – Aneokoena Aneokoena Oct 02 '15 at 21:08 — Aneokoena Aneokoena, Oct 02 '15 at 21:08

How to use variables in PostgreSQL in Python

1 Answers1