Use python to execute line in postgresql

问题:

I have imported one shapefile named tc_bf25 using qgis, and the following is my python script typed in pyscripter,

import sys
import psycopg2

conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
cur = conn.cursor()

query = """
    ALTER TABLE tc_bf25 ADD COLUMN source integer;
    ALTER TABLE tc_bf25 ADD COLUMN target integer;
    SELECT assign_vertex_id('tc_bf25', 0.0001, 'the_geom', 'gid')
;"""
cur.execute(query)

query = """
    CREATE OR REPLACE VIEW tc_bf25_ext AS
    SELECT *, startpoint(the_geom), endpoint(the_geom)
    FROM tc_bf25
;"""
cur.execute(query)

query = """
    CREATE TABLE node1 AS
    SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
          foo.p AS the_geom
    FROM (
      SELECT DISTINCT tc_bf25_ext.startpoint AS p FROM tc_bf25_ext
      UNION
      SELECT DISTINCT tc_bf25_ext.endpoint AS p FROM tc_bf25_ext
    ) foo
    GROUP BY foo.p
;"""
cur.execute(query)

query = """
    CREATE TABLE network1 AS
    SELECT a.*, b.id as start_id, c.id as end_id
    FROM tc_bf25_ext AS a
      JOIN node AS b ON a.startpoint = b.the_geom
      JOIN node AS c ON a.endpoint = c.the_geom
;"""
cur.execute(query)

query = """
    ALTER TABLE network1 ADD COLUMN shape_leng double precision;
    UPDATE network1 SET shape_leng = length(the_geom)
;"""
cur.execute(query)

I got the error at the second cur.execute(query),

But I go to pgAdmin to check result, even though no error occurs, the first cur.execute(query) didn't add new columns in my table.

What mistake did I make？ And how to fix it？

I am working with postgresql 8.4, python 2.7.6 under Windows 8.1 x64.

回答1:

When using psycopg2, autocommit is set to False by default. The first two statements both refer to table tc_bf25, but the first statement makes an uncommitted change to the table. So try running conn.commit() between statements to see if this resolves the issue

回答2:

You should run each statement individually. Do not combine multiple statements into a semicolon separated series and run them all at one. It makes error handling and fetching of results much harder.

If you still have the problem once you've made that change, show the exact statement you're having the problem with.

回答3:

Just to add to @Talvalin you can enable auto-commit by adding

psycopg2.connect("dbname='mydb',user='postgres',host ='localhost',password = '****'")
conn.autocommit = True

after you connect to your database using psycopg2