Python psycopg2 not inserting into postgresql table

Question

I'm using the following to try and insert a record into a postgresql database table, but it's not working. I don't get any errors, but there are no records in the table. Do I need a commit or something? I'm using the postgresql database that was installed with the Bitnami djangostack install.

import psycopg2

try:
    conn = psycopg2.connect("dbname='djangostack' user='bitnami' host='localhost' password='password'")
except:
    print "Cannot connect to db"

cur = conn.cursor()

try:
    cur.execute("""insert into cnet values ('r', 's', 'e', 'c', 'w', 's', 'i', 'd', 't')""")
except:
    print "Cannot insert"

Warning: `except:` clauses catching no specific exception are not at all helpful in debugging code. — mechanical_meat, Aug 05 '13 at 22:34
keeping autocommit=False is preferred in many cases. so commit manually after executing a query: after cursor.execute(), do conn.commit() — MinhajulAnwar, Apr 26 '18 at 15:33
Check out the answer in this post: [Link](https://stackoverflow.com/questions/1017463/postgresql-how-to-run-vacuum-from-code-outside-transaction-block) — Inaam Ilahi, Sep 06 '22 at 14:18

score 112 · Accepted Answer · answered Aug 14 '13 at 16:33

112

If don't want to have to commit each entry to the database, you can add the following line:

conn.autocommit = True

So your resulting code would be:

import psycopg2

try:
    conn = psycopg2.connect("dbname='djangostack' user='bitnami' host='localhost' password='password'")
    conn.autocommit = True
except:
    print "Cannot connect to db"

cur = conn.cursor()

try:
    cur.execute("""insert into cnet values ('r', 's', 'e', 'c', 'w', 's', 'i', 'd', 't')""")
except:
    print "Cannot insert"

answered Aug 14 '13 at 16:33

aright

2,014
2
17
16

Hi aright, do you know what happens if you don't have autocommit set to True and you never commit? It seems that the sequence of the table increments, but where are the rows? – Daniel Baughman Dec 21 '15 at 04:12
1

@DanielBaughman: It's because sequences increment even if you don't commit; see e.g. [Sequences not affected by transactions? ](https://stackoverflow.com/questions/2095917/sequences-not-affected-by-transactions) – user1071847 Mar 09 '18 at 01:14

score 55 · Answer 2 · answered Aug 05 '13 at 22:35

55

Turns out I needed conn.commit() at the end

answered Aug 05 '13 at 22:35

Superdooperhero

7,584
19
83
138

Eugene Yarmash · Answer 3 · 2017-11-06T12:11:29.227

38

psycopg2 is Python DB API-compliant, so the auto-commit feature is off by default. You need to call conn.commit to commit any pending transaction to the database. As connections (and cursors) are context managers, you can simply use the with statement to automatically commit/rollback a transaction on leaving the context:

with conn, conn.cursor() as cur:  # start a transaction and create a cursor
    cur.execute(sql)

From the docs:

When a connection exits the with block, if no exception has been raised by the block, the transaction is committed. In case of exception the transaction is rolled back.

When a cursor exits the with block it is closed, releasing any resource eventually associated with it. The state of the transaction is not affected.

edited Nov 06 '17 at 12:11

answered Oct 16 '17 at 19:22

Eugene Yarmash

142,882
41
325
378

3

thanks -- this is the best/cleanest answer, i think. – mad.meesh Apr 30 '19 at 20:03
1

this is great. I get my connection from a function and was able to implement this as: with get_connection(self.database_name, self.user_name) as conn, conn.cursor() as csr: csr.execute(statement) meaning that I could reference the first value in the second value in the with statement even though the first value was not predefined. – DonkeyKong Sep 15 '19 at 00:38
@DonkeyKong You may need to call `conn.close()` separately after the `with` block. – lk_vc Mar 01 '23 at 09:08

score 6 · Answer 4 · answered Dec 18 '19 at 16:47

6

import psycopg2
conn = psycopg2.connect("dbname='djangostack' user='bitnami' 
host='localhost' password='password'")
con.set_session(autocommit=True)

answered Dec 18 '19 at 16:47

heaven2sai

119
2
1

7

Please don't post code only answer, which will be less helpful over the period of time to understand the context. In other words - you wont get upvotes even if its the best as its hard to understand for the community. – Arun Vinoth-Precog Tech - MVP Dec 18 '19 at 17:19
2

eh, nothin wrong with just posting code. sometimes, that is a really nice and pretty way to answer questions. but yeah, i dont think that fully answered what the OP was asking for – dtc Feb 10 '21 at 03:46

Python psycopg2 not inserting into postgresql table

4 Answers4

Linked

Related