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+

granting GRANT ALL PRIVILEGES ON DATABASE pgdb to pguser; and changing peer to trust in hba.conf didn't help — juk, Dec 05 '12 at 03:28
Both of those are attempts to solve a problem you don't have. You'd be getting permissions errors if either of those were issues. — Craig Ringer, Dec 05 '12 at 05:47

score 250 · Accepted Answer · answered Dec 05 '12 at 03:28

250

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.

answered Dec 05 '12 at 03:28

Craig Ringer

307,061
76
688
778

23

Some answers are worth 100 up votes - even after 4 years. This is one of them! :) – Carsten Aug 25 '16 at 14:28
1

@rjurney Please submit a pull request to the psycopg2 repository to add it then :) at the most logical place you'd expect to find it in the docs. – Craig Ringer Feb 27 '17 at 06:17
3

For other people worried they may keep forgetting this step: it may also be of use to set `autocommit` to `True` for the session: http://initd.org/psycopg/docs/connection.html#connection.set_session – iff_or Aug 11 '17 at 00:35
The faq should also remind the difference between using the connection within a with() statement or a cursor... :( – Davide Inglima Mar 29 '19 at 11:07
Re: `with()` statement: a `commit()` is automatically performed at the end of a `with` block assuming no errors occurred. Note that the connection remains open. For those who found themselves here and didn't know. :) – Demitri Nov 19 '19 at 03:16
When you have a SELECT statement you don't need `commit()`, at least in my case – Carmoreno Dec 09 '20 at 16:44
@CarMoreno If the `SELECT` was read-only and didn't call any data-modifying functions or views, that is correct. It's a good habit anyway though. – Craig Ringer Dec 15 '20 at 06:54
@CraigRinger You're saying the commit is only necessary to make it visible to **other** sessions. Does this mean that if I do an update followed by a select on the updated data on the **same** session, the updated data will be found without having to make a commit in between the update and select? – Philipp Jan 27 '21 at 10:11
@Philipp Yes. https://www.postgresql.org/docs/current/mvcc-intro.html . That's necessary for most transactions to make any sense; otherwise running `BEGIN; UPDATE t SET x = x + 1; UPDATE t SET x = x - 1; COMMIT;` would cause all `x` values to go down by 1, since the second update wouldn't see the changes made by the first in the same txn. – Craig Ringer Feb 02 '21 at 04:32
@Philipp There are various complexities around the rules for when new changes become visible in the default `READ COMMITTED` isolation, mostly relating to multi-part (compound) statements, statements that call functions, etc. But for most simple purposes you can assume your statement will "see" changes committed by other txns before your statement started, *and* changes made by prior statements in the same txn. – Craig Ringer Feb 02 '21 at 04:37
@Philipp There are other isolation modes like `REPEATABLE READ` and `SERIALIZABLE` that have different rules and behaviour. It's worth understanding these, but for most applications the default isolation level `READ COMMITTED` is what you will want most of the time. – Craig Ringer Feb 02 '21 at 04:39

score 6 · Answer 2 · answered Nov 02 '20 at 20:53

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.

psycopg2 not actually inserting data

2 Answers2

Linked

Related