使用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屋！