Python/PostgreSQL insert row having no effect

Question

I am using the pg8000 module to connect a python script to a postgreSQL database and I want to insert some new rows. When I run the script I get no errors and it appears to run fine, but when I check the table afterwards, nothing was actually added to it.

What is confusing me is that if I print out the query statement I am executing with the pg8000 cursor object, and then just copy/paste and execute it in the pgadmin interface, it properly inserts a new row.

Another strange thing is that the table has an ID field which is based on a SERIAL, auto-updating itself every time a row is inserted. This serial is updating when I run the script. I can tell b/c the next ID to add, for example, is 6000. If I run the script (which should add about 1000 rows but doesnt), then insert a row manually, the new row has an ID of 7000.

Here's the loop creating and executing the statements:

for row in new_signs:
    query = "INSERT INTO {0}_signs (source_link, destination_link, exit_number) VALUES ({1},{2},'{3}');".format(city,row[0],row[1],row[2])
    print query
    cursor.execute(query)

And some example output:

INSERT INTO osm_newyork_signs (source_link, destination_link, exit_number) VALUES (56423,1833854,'26');
INSERT INTO osm_newyork_signs (source_link, destination_link, exit_number) VALUES (353212,310961,'45');
INSERT INTO osm_newyork_signs (source_link, destination_link, exit_number)    VALUES (203823,1862344,'63N');

And the table definition:

CREATE TABLE public.osm_newyork_signs
(
  source_link integer NOT NULL,
  destination_link integer NOT NULL,
  exit_number text,
  "Branch_RouteID" character varying(64),
  "Branch_RouteDir" character varying(6),
  "Sign_TextType" character varying(6),
  "Sign_Text" character varying(255),
  "Toward_RouteID" character varying(64),
  "Straight_On" character varying(255),
  id integer NOT NULL DEFAULT nextval('osm_newyork_signs_id_seq'::regclass),
  CONSTRAINT newyork_signs_pkey PRIMARY KEY (id),
  CONSTRAINT check_branch_toward CHECK ("Sign_TextType"::text = 'B'::text OR     "Sign_TextType"::text = 'T'::text OR "Sign_TextType" IS NULL)
)
WITH (
  OIDS=FALSE
);

Do you by any chance use a session and forgot to commit the queries? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jun 8, 2016 at 18:19
Not that I am aware of. I just create a connection using conn = pg.connect(user='', password='', host='', database='') cursor = conn.cursor() — wmebane
– wmebane, Commented Jun 8, 2016 at 18:23

Oladipo · Accepted Answer · 2020-08-20 23:18:41Z

9

Since autocommit is off by default unless you explicitly call conn.commit(), any actions done in the transaction will be undone. However, ID numbers are not rolled back and hence you are seeing that behavior.

You have two options:

explicitly commit after performing execute statements:

    conn.commit()

enable autocommit on your DB connection (before you create cursor):

    conn.autocommit = True

You can read more about this at http://pythonhosted.org/pg8000/quickstart.html#key-points

edited Aug 20, 2020 at 23:18

Oladipo

1,6893 gold badges18 silver badges33 bronze badges

answered Jun 8, 2016 at 18:24

Praveen Yalagandula

4,8042 gold badges16 silver badges14 bronze badges

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

2 Comments

wmebane Over a year ago

I was thinking the commit came from the cursor, so when I didn't see a cursor.commit method on the documentation I figured it was on by default. Thanks!

wmebane Over a year ago

Out of curiosity, do you know why the serial ID would change without a commit?

Collectives™ on Stack Overflow

Python/PostgreSQL insert row having no effect

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related