我已经使用qgis导入了一个名为 tc_bf25 的shapefile,以下是我在pyscripter中键入的python脚本,
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)我在第二个 cur.execute(query)时出错,
但是我去pgAdmin检查结果,即使没有错误发生,第一个 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.
我犯了什么错误make?以及如何解决它?
What mistake did I make? And how to fix it?
我正在Windows 8.1 x64下使用PostgreSQL 8.4,python 2.7.6。
I am working with postgresql 8.4, python 2.7.6 under Windows 8.1 x64.
推荐答案在使用psycopg2时,默认情况下autocommit设置为False。前两个语句均引用表tc_bf25,但是第一个语句对表进行了未提交的更改,因此请尝试在语句之间运行connmit()以查看是否可以解决问题
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 connmit() between statements to see if this resolves the issue
更多推荐
使用python在PostgreSQL中执行代码行
发布评论