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"
Python psycopg2 not inserting into postgresql table - Stack Overflow
Allow to start transactions in autocommit mode
With psycopg2 and PostrgeSQL, is it faster to commit changes line by line, or is it faster to iterate across the data with an UPDATE or INSERT INTO command and commit only once the loop reaches the end?
Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection? - Database Administrators Stack Exchange
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"
Turns out I needed conn.commit() at the end
I have a webapp that I built a few months ago that uses a pandas dataframe and stores all data in the memory of the server, does some operations and presents the data. Obviously this is pretty bad design, so I am converting some of the functionality of the app from dataframes in memory to permanent storage in a database using PostgreSQL. My only SQL experience with python up until now was with SQLite which didn't require me to commit changes to the database if to avoid race conditions (not that this matters, as the database is only accessed by functions of the application that I can execute).
So my question is, is it faster to iterate through my data, inserting or updating the database and then committing each line? Or is it faster/possible to perform all the execute commands and then commit only when the loop reaches the end?
I would try both of them, but I don't want to have to clear the database and drop all the tables multiple times as I try it out.
Transactions are per-session, i.e. per-connection.
PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes.
In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from the server, but I don't find them good for much else.
Why manually issue begin and commit though, rather than using the connection methods for them?
From the psycopg2 documentation:
In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.
At the same time, from version 2.4.2, there is the autocommit attribute (emphasis added):
Read/write attribute: if
True, no transaction is handled by the driver and every statement sent to the backend has immediate effect; ifFalsea new transaction is started at the first command execution: the methodscommit()orrollback()must be manually invoked to terminate the transaction.The autocommit mode is useful to execute commands requiring to be run outside a transaction, such as
CREATE DATABASEorVACUUM.The default is
False(manual commit) as per DBAPI specification.