CODEWITHSUNDEEP
postgresqlpython-3.xpsycopg2
Giannis
Giannis

Reputation: 5526

Psycopg2 insert not saved

A database copy I was not aware of, was listening to the default port 5432, which is where the inserts were happening. I should be passing the custom port for the DB I was meant to be connected.

Something that I think may be significant: id returned from fetchall() is different from the one actually inserted directly from psql.

The script I execute:

import os
import psycopg2

conn_config = {
    'host': os.environ['DB_HOST'],
    'dbname': os.environ['DB_NAME'],
    'user': os.environ['DB_USER'],
    'password': os.environ['DB_PASSWD']
}
conn = psycopg2.connect(**conn_config)
cur = conn.cursor()
sql = """INSERT INTO file(file_title, file_descrip) VALUES ('test','giannis') RETURNING file_id;"""
cur.execute(sql)
print(cur.fetchall())
conn.commit()
cur.close()
conn.close()

Output:

>>>[(76,)]

Connecting with the same credentials, from the same machine to the DB:

select * from file where file.file_id=76;
 file_id | file_title | file_stream | file_descrip | obj_uuid
---------+------------+-------------+--------------+----------
(0 rows)

And from the same session within psql, copying the above SQL statement:

INSERT INTO file(file_title, file_descrip) VALUES ('test','giannis') RETURNING file_id;
 file_id
---------
      57
(1 row)

INSERT 0 1
my_db=> select * from file where file.file_id=57;
-[ RECORD 1 ]+-------------------------------------
file_id      | 57
file_title   | test
file_stream  |
file_descrip | giannis
obj_uuid     | 396d5d3b-efe1-422a-a6b4-d9b21381d4be

Upvotes: 1

Views: 3420

Answers (1)

hoyland
hoyland

Reputation: 1824

It looks like you don't commit the transaction. Call conn.commit() before the connection closes.

Upvotes: 4

Related Questions