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
);

Answer 1

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:

1. explicitly commit after performing execute statements:

    conn.commit()

2. 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