CODEWITHSUNDEEP
pythonpostgresqlpostgresql-9.1sql-insertpsycopg2
Torxed
Torxed

Reputation: 23490

.execute('INSERT ...') does not actually insert data into table

warning: brain fart imminent

I've been at this for a bit now, and I'm guessing this is just a "I'm tired, can't get it through my head" type of issue so I'm hoping you will break this down for me..

I'll explain this backwards because it makes more sense..

I verify the contents of my table by doing:

SELECT DISTINCT DATE(time) AS UNIQUE_DATES FROM reports;

I'm expecting:

2014-06-17
2014-06-16

But 17 is missing..
So naturally I check my query_string by doing:

import psycopg2
database = psycopg2.connect(dbname=database, user=user, password=password, database=database)
cursor = database.cursor()
query_string = b"INSERT INTO reports (time) VALUES " + b','.join(cursor.mogrify('(%s)', value) for value in values) + b';')
print(query_string)
cursor.execute(query_string)

resulting in:

b"INSERT INTO reports(time) VALUES ('2014-06-17 10:00:08'),('2014-06-17 14:00:05');"

And no error messages.
And lo and behold.. if query:

SELECT * FROM pg_stat_activity;

The INSERT shows up. (all tho be it at random times considering how fast that query would fly by into <IDLE> state.. but it's there)

But then again, it's not in the database.. How is this possible? If i take the command and execute it in psql on my own sure enough it's inserted..

It makes no sense.. It would make sense if another PID was actually performing executions on the database, which i can't really say there is because again that last SELECT shows no active sessions, they're all idle..

Upvotes: 0

Views: 124

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123400

You need to commit the transaction:

connection.commit()

See the Transactions control section of the psycopg2 documentation.

psql operates in a different transaction mode; it auto-commits all statements. From the psql documentation:

AUTOCOMMIT
When on (the default), each SQL command is automatically committed upon successful completion. To postpone commit in this mode, you must enter a BEGIN or START TRANSACTION SQL command. When off or unset, SQL commands are not committed until you explicitly issue COMMIT or END.

Upvotes: 4

Related Questions