使用python在PostgreSQL中执行代码行 [英] Use python to execute line in 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,但是第一个语句对表进行了未提交的更改,因此请尝试在语句之间运行conn.commit()以查看是否可以解决问题
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
这篇关于使用python在PostgreSQL中执行代码行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!