阅读和wirte postgres脚本使用python [英] Read and wirte postgres script using python

问题描述

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

I have postgres tables and i want to run a PostgreSQL script file on these tables using python and then write the result of the queries in a csv file. The script file have multiple queries separated by semicolon ;. Sample script is shown below

脚本文件：

--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文件。这意味着第一个查询结果被第二个查询覆盖，第二个查询结果依次覆盖，直到最后一个查询。

When i run the python code, it executes successfully without any error but the problem i am facing is, during writing the result of the queries to a csv file. Only the result of last executed query is written to the csv file. It means that first query result is overwrite by the second query, second by third and so on till the last query.

这是我的Python代码：

import psycopg2
import sys
import csv
import datetime, time

def run_sql_file(filename, connection):
'''
    The function takes a filename and a connection as input
    and will run the SQL query on the given connection  
'''
    start = time.time()

    file = open(filename, 'r')
    sql = s = " ".join(file.readlines())
   #sql = sql1[3:]
    print "Start executing: " + " at " + str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M")) + "\n"
    print "Query:\n", sql + "\n"
    cursor = connection.cursor()
    cursor.execute(sql)
    records = cursor.fetchall()
    with open('Report.csv', 'a') as f:
        writer = csv.writer(f, delimiter=',')
        for row in records:
            writer.writerow(row)
    connection.commit()
    end = time.time()
    row_count = sum(1 for row in records)
    print "Done Executing:", filename
    print "Number of rows returned:", row_count
    print "Time elapsed to run the query:",str((end - start)*1000) + ' ms'
    print "\t ==============================="

def main():    
    connection = psycopg2.connect("host='localhost' dbname='central' user='postgres' password='tpltrakker'")
    run_sql_file("script.sql", connection)
    connection.close()

if __name__ == "__main__":
    main()

我的代码有什么问题？

推荐答案

这是最简单的输出每个查询作为不同的文件。 copy_expert

This is the simplest to output each query as a different file. copy_expert

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()

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

If you want to append the second output to the same file then just keep the first file object opened.

注意有必要 copy 输出到 stdout 以使其可用于 copy_expert

Notice that it is necessary that copy outputs to stdout to make it available to copy_expert

这篇关于阅读和wirte postgres脚本使用python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！