从PostgreSQL数据库保存PL/pgSQL输出到CSV文件的最简单的方法是什么?

我使用PostgreSQL 8.4 pgAdmin III和PSQL插件,我从那里运行查询。


当前回答

import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'

其他回答

如果您对特定表的所有列以及标题感兴趣,则可以使用

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

这稍微简单一点

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

据我所知,两者是等价的。

PSQL可以帮你做到这一点:

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

有关此处使用的选项的帮助,请参阅man psql。

如果你有更长的查询,你喜欢使用psql,然后把你的查询到一个文件,并使用以下命令:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'

我不得不使用\COPY,因为我收到了错误消息:

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

所以我用了:

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

它在运作