python - cursor.execute("INSERT INTO im_entry.test ("+entrym+") VALUES ('"+p+"');")

Question

   entrym='entry'
   entrym=entrym+ str(idx)

   cursor.execute("INSERT INTO im_entry.test ("+entrym+") VALUES ('"+p+"');")

I am using a query like this, where entry1, entry2 etc. are my database tables. The program doesn't show any errors, but the p value does not get inserted in the db. What is wrong here? Please help me.

Answer 1

By default, psycopg2 starts transactions for you automatically, which means that you have to tell it to commit. Note that commit is a method of the connection, not the cursor.

conn = psycopg2.connection('...')
cur = conn.cursor()
cur.execute("...")
conn.commit()

The intent is that you can group multiple statements together in a single transaction, so other queries won't see half-made changes, but also for performance reasons.

Also note that you should always use placeholders, instead of concatenating strings together.
E.g.:

cur.execute("INSERT INTO im_entry.test (colname) VALUES (%s)", [p])

Otherwise you risk making SQL injection attacks possible.