gpt4 book ai didi

postgresql - 如何使用 psycopg2 将 postgresql 的结果保存到 csv/excel 文件?

转载 作者:行者123 更新时间:2023-11-29 11:11:24 24 4
gpt4 key购买 nike

我在 postgresql 中使用 driving_distance 来查找所有节点之间的距离,这是我在 pyscripter 中的 python 脚本,

import sys

#set up psycopg2 environment
import psycopg2

#driving_distance module
query = """
select *
from driving_distance ($$
select
gid as id,
start_id::int4 as source,
end_id::int4 as target,
shape_leng::double precision as cost
from network
$$, %s, %s, %s, %s
)
;"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'")
cur = conn.cursor()

#count rows in the table
cur.execute("select count(*) from network")
result = cur.fetchone()
k = result[0] + 1

#run loops
rs = []
i = 1
while i <= k:
cur.execute(query, (i, 1000000, False, False))
rs.append(cur.fetchall())
i = i + 1

#print result
for record in rs:
print record
conn.close()

结果很好,在 python 解释器中的部分内容如下所示,

[(1, 2, 35789.4069722436), (2, 2, 31060.0761437413), (3, 19, 30915.1312550546), (4, 3, 33438.0715007666), (5, 4, 29149.0894812718), (6, 7, 25504.020006665), (7, 7, 29594.741802956), (8, 5, 20736.2427352646), (9, 10, 19545.809601197), (10, 8, 22609.5146670393), (11, 9, 14134.5400189648), (12, 11, 12266.7845493204), (13, 18, 17426.7449057031), (14, 21, 11754.7277029158), (15, 18, 13128.3548040769), (16, 20, 21924.2253916803), (17, 11, 15209.9969992088), (18, 20, 26316.7797545076), (19, 13, 604.414419026164), (20, 16, 740.652673783403), (21, 15, 0.0), (22, 15, 2378.768084459)]
[(1, 2, 38168.1750567026), (2, 2, 33438.8442282003), (3, 19, 33293.8993395136), (4, 3, 35816.8395852256), (5, 4, 31527.8575657308), (6, 7, 27882.788091124), (7, 7, 31973.509887415), (8, 5, 23115.0108197236), (9, 10, 21924.577685656), (10, 8, 24988.2827514983), (11, 9, 16513.3081034238), (12, 11, 14645.5526337793), (13, 18, 19805.5129901621), (14, 21, 14133.4957873748), (15, 18, 15507.1228885359), (16, 20, 24302.9934761393), (17, 11, 17588.7650836678), (18, 20, 28695.5478389666), (19, 13, 2983.18250348516), (20, 16, 3119.4207582424), (21, 15, 2378.768084459), (22, 15, 0.0)]

我想将这些结果导出到一个新的 csv 或 excel 文件中,我已经查看了这些相关的帖子和​​网站,

但是在pyscripter下还是不能导出这些,怎么办?

我在 Windows 8.1 x64 下使用 postgresql 8.4、python 2.7.6。


更新#1:我尝试了 Talvalin 提供的以下代码(谢谢!),

import sys

#set up psycopg2 environment
import psycopg2

#driving_distance module
query = """
select *
from driving_distance ($$
select
gid as id,
start_id::int4 as source,
end_id::int4 as target,
shape_leng::double precision as cost
from network
$$, %s, %s, %s, %s
)
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'TC_routing' user = 'postgres' host = 'localhost' password = '****'")
cur = conn.cursor()

outputquery = 'copy ({0}) to stdout with csv header'.format(query)

with open('resultsfile', 'w') as f:
cur.copy_expert(outputquery, f)

conn.close()

但是下面有错误,

>>> 

Traceback (most recent call last):
File "C:/Users/Heinz/Desktop/python_test/driving_distance_loop_test.py", line 27, in <module>
cur.copy_expert(outputquery, f)
ProgrammingError: 錯誤: 在"語法錯誤"附近發生 %
LINE 10: $$, %s, %s, %s, %s
^

也许我需要在上面的代码中添加更多内容。

最佳答案

基于 Psycopg2's cursor.copy_expert()Postgres COPY文档和您的原始代码示例,请尝试一下。我在我的笔记本电脑上测试了类似的查询导出,所以我有理由相信这应该有效,但如果有任何问题,请告诉我。

import sys

#set up psycopg2 environment
import psycopg2

#driving_distance module
#note the lack of trailing semi-colon in the query string, as per the Postgres documentation
query = """
select *
from driving_distance ($$
select
gid as id,
start_id::int4 as source,
end_id::int4 as target,
shape_leng::double precision as cost
from network
$$, %s, %s, %s, %s
)
"""

#make connection between python and postgresql
conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = 'xxxx'")
cur = conn.cursor()

outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(query)

with open('resultsfile', 'w') as f:
cur.copy_expert(outputquery, f)

conn.close()

关于postgresql - 如何使用 psycopg2 将 postgresql 的结果保存到 csv/excel 文件?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22776849/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com