阅读并使用python编写postgres脚本

Question

我有postgres表，我想使用python在这些表上运行PostgreSQL脚本文件，然后将查询结果写入csv文件。脚本文件有多个用分号;分隔的查询。示例脚本如下所示

脚本文件：

--Duplication Check
select p.*, c.name
from scale_polygons_v3 c inner join cartographic_v3 p
on (metaphone(c.name_displ, 20) LIKE metaphone(p.name, 20)) AND c.kind NOT IN (9,10)
where ST_Contains(c.geom, p.geom);

--Area Check
select sp.areaid,sp.name_displ,p.road_id,p.name
from scale_polygons_v3 sp, pak_roads_20162207 p
where st_contains(sp.geom,p.geom) and sp.kind = 1
and p.areaid != sp.areaid;

当我运行python代码时，它成功地执行，没有任何错误，但我面临的问题是，在将查询结果写入csv文件时。只有上一次执行的查询的结果写入csv文件。这意味着第一个查询结果被第二个查询覆盖，第二个被第三个查询覆盖，直到最后一个查询。在

这是我的python代码：

^{pr2}$

我的代码怎么了？在

Answer 1

如果您能够稍微更改SQL脚本，那么下面是一个解决方法：

#!/usr/bin/env python

import psycopg2

script = '''
    declare cur1 cursor for
        select * from (values(1,2),(3,4)) as t(x,y);

    declare cur2 cursor for
        select 'a','b','c';
    '''
print script

conn = psycopg2.connect('');

# Cursors exists and available only inside the transaction
conn.autocommit = False;

# Create cursors from script
conn.cursor().execute(script);

# Read names of cursors
cursors = conn.cursor();
cursors.execute('select name from pg_cursors;')
cur_names = cursors.fetchall()

# Read data from each available cursor
for cname in cur_names:
    print cname[0]
    cur = conn.cursor()
    cur.execute('fetch all from ' + cname[0])
    rows = cur.fetchall()
    # Here you can save the data to the file
    print rows


conn.rollback()

print 'done'

免责声明：我对Python完全是新手。在

Answer 2

这是将每个查询输出为不同文件的最简单方法。^{}

query = '''
    select p.*, c.name
    from
        scale_polygons_v3 c
        inner join
        cartographic_v3 p on metaphone(c.name_displ, 20) LIKE metaphone(p.name, 20) and c.kind not in (9,10)
    where ST_Contains(c.geom, p.geom)
'''
copy = "copy ({}) to stdout (format csv)".format(query)
f = open('Report.csv', 'wb')
cursor.copy_expert(copy, f, size=8192)
f.close()

query = '''
    select sp.areaid,sp.name_displ,p.road_id,p.name
    from scale_polygons_v3 sp, pak_roads_20162207 p
    where st_contains(sp.geom,p.geom) and sp.kind = 1 and p.areaid != sp.areaid;
'''
copy = "copy ({}) to stdout (format csv)".format(query)
f = open('Report2.csv', 'wb')
cursor.copy_expert(copy, f, size=8192)
f.close()

如果您想将第二个输出附加到同一个文件，那么只需保持第一个file对象打开。在

请注意，^{}必须输出到stdout以使其对copy_expert可用