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
– mechanical_meat, Commented Aug 5, 2013 at 22:34
keeping autocommit=False is preferred in many cases. so commit manually after executing a query: after cursor.execute(), do conn.commit() — anwar
– anwar, Commented Apr 26, 2018 at 15:33

aright · Accepted Answer · 2013-08-14 16:33:21Z

124

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, 2013 at 16:33

aright

2,1342 gold badges17 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Baughman Over a year ago

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?

user1071847 Over a year ago

@DanielBaughman: It's because sequences increment even if you don't commit; see e.g. [Sequences not affected by transactions? ](stackoverflow.com/questions/2095917/…)

Superdooperhero · Accepted Answer · 2013-08-05 22:35:49Z

58

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

answered Aug 5, 2013 at 22:35

Superdooperhero

8,13319 gold badges91 silver badges155 bronze badges

Comments

Eugene Yarmash · Accepted Answer · 2017-11-06 12:11:29Z

43

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 6, 2017 at 12:11

answered Oct 16, 2017 at 19:22

Eugene Yarmash

152k44 gold badges345 silver badges391 bronze badges

3 Comments

mad.meesh Over a year ago

thanks -- this is the best/cleanest answer, i think.

DonkeyKong Over a year ago

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.

lk_vc Over a year ago

@DonkeyKong You may need to call conn.close() separately after the with block.

heaven2sai · Accepted Answer · 2019-12-18 16:47:31Z

7

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

answered Dec 18, 2019 at 16:47

heaven2sai

1292 silver badges2 bronze badges

2 Comments

Arun Vinoth PrecogTechnologies Over a year ago

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.

dtc Over a year ago

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

Collectives™ on Stack Overflow

Python psycopg2 not inserting into postgresql table

4 Answers 4

2 Comments

Comments

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

3 Comments

2 Comments

Linked

Related