使用python在PostgreSQL中执行代码行

本文介绍了使用python在PostgreSQL中执行代码行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经使用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