psycopg2 not actually inserting data

Question

I need to insert JSON data from tornado to postgres, so here's test like this:

from psycopg2 import connect  conn = connect("user='pguser' host='localhost' dbname='pgdb' password='pgpass'") cursor = conn.cursor()  data = '[{"id":"sdf","name":"wqe","author":"vb"}]'  for row in eval(data):   print row   cursor.execute("""INSERT INTO books(id,name,author) VALUES('%s','%s','%s')""" % \         (row['id'], row['name'], row['author'])   )  >>> cursor.execute("SELECT * FROM books") >>> cursor.fetchall() [('sdf', 'wqe', 'vb')] >>>  $> psql -d pgdb -U pguser -W Password for user pguser:  psql (9.1.6) Type "help" for help.  pgdb=> select * from books;  id | name | author  ----+------+-------- (0 rows) 

As you can see after doing select in python shell, there's some data, but in psql there's 0 rows! What may I be doing wrong?

Python 2.7.2+

Answer 1

You didn't commit the transaction.

Psycopg2 opens a transaction automatically, and you must tell it to commit in order to make the data visible to other sessions.

See the psycopg2 FAQ and the connection.commit() method.

Answer 2

Just had the same perplexing issue. To put options together:

as @Craig Ringer writes after cursor.execute you can run connection.commit

cursor.execute('INSERT INTO table VALUES(DEFAULT, %s)', email) ... connection.commit() 

OR after connect set autocommit

connection = connect("user='pguser' host='localhost' dbname='pgdb' password='pgpass'") connection.autocommit = True 

OR use set_session to set autocommit

connection = connect("user='pguser' host='localhost' dbname='pgdb' password='pgpass'")  connection.set_session(autocommit=True) 

All worked for me.