psycopg2 not actually inserting data
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
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.