您想在服务器上还是在客户机上得到结果文件?

服务器端

如果您想要一些易于重用或自动化的东西,您可以使用PostgreSQL内置的 COPY 命令。例如

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

这种方法完全在远程服务器上运行 -它不能写入您的本地PC。它还需要以Postgres“超级用户”(通常称为“根”)的身份运行,因为Postgres不能阻止它对该计算机的本地文件系统进行讨厌的操作。

这并不意味着你必须以超级用户的身份进行连接(自动化将是另一种安全风险),因为你可以使用 the SECURITY DEFINER option to CREATE FUNCTION 使一个函数 像超级用户一样运行 .

关键的一点是,您的函数需要执行额外的检查,而不仅仅是绕过安全性,这样您就可以编写一个函数来导出所需的准确数据,或者编写一些可以接受各种选项的函数,只要它们符合严格的白名单。你需要检查两件事:

1. 哪个 文件夹 是否允许用户在磁盘上读/写?例如,这可能是一个特定的目录,文件名可能必须具有适当的前缀或扩展名。
2. 哪个 桌子 用户是否能够在数据库中读/写?这通常由 GRANT 在数据库中,但是函数现在作为超级用户运行,因此通常是“越界”的表将完全可以访问。您可能不想让某人调用您的函数并在您的__users_157;table_

我已经写了 a blog post expanding on this approach 包括导出(或导入)满足严格条件的文件和表的一些函数示例。

客户端

另一种方法是 在客户端执行文件处理 ,即在应用程序或脚本中。Postgres服务器不需要知道您要复制到什么文件,它只需要吐出数据,客户机将其放在某个地方。

它的基本语法是 COPY TO STDOUT 命令和图形工具(如pgadmin)将它包装在一个很好的对话框中。

这个 psql 命令行客户端 有一个特殊的“meta命令”调用 \copy ,它采用与“real”相同的所有选项 COPY ,但在客户端内部运行:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

注意,没有终止 ; ,因为元命令以换行符终止,与SQL命令不同。

从 the docs :

不要将copy与psql指令\ copy混淆。\ copy调用copy-from-stdin或copy-to-stdout,然后在psql客户机可访问的文件中获取/存储数据。因此,当使用\copy时,文件的可访问性和访问权限取决于客户机而不是服务器。

应用程序编程语言 可以 也支持推送或获取数据,但通常不能使用 COPY FROM STDIN / TO STDOUT 在标准的SQL语句中,因为没有连接输入/输出流的方法。PHP的PostgreSQL处理程序( 不 PDO)包括非常基本的 pg_copy_from 和 pg_copy_to 在PHP数组中复制或复制的函数,对于大型数据集来说,这可能是无效的。

有几种解决方案:

一 psql 命令

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

这有很大的优势,您可以通过ssh使用它,比如 ssh postgres@host command -使你能够

2后格雷斯 copy 命令

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql交互(或不交互)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

所有这些都可以在脚本中使用,但我更喜欢1。

4 pgadmin,但这不可编写脚本。

在终端中(连接到数据库时)将输出设置为cvs文件

1)将字段分隔符设置为 ',' :

\f ','

2)设置输出格式不对齐:

\a

3)仅显示元组:

\t

4)设置输出:

\o '/tmp/yourOutputFile.csv'

5)执行查询:

:select * from YOUR_TABLE

6)输出:

\o

然后您将能够在此位置找到您的csv文件:

cd /tmp

使用 scp 使用nano命令或编辑:

nano /tmp/yourOutputFile.csv

如果你对 全部的 特定表的列以及标题,可以使用

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

这比

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

据我所知,这是等效的。

我必须使用\副本,因为我收到错误消息:

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

所以我用了:

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

而且它在起作用

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 有关此处使用的选项的帮助。

csv导出统一

这个信息没有很好地表达出来。因为这是我第二次需要推导这个,所以我把这个放在这里提醒自己,如果没有其他的话。

要做到这一点(让csv退出Postgres),最好的方法是使用 COPY ... TO STDOUT 命令。尽管你不想按照这里答案中的方式来做。使用命令的正确方法是:

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

记住只有一个命令!

它非常适合在ssh上使用:

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

它非常适合在Docker内部通过ssh使用:

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

在本地机器上更是如此:

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

还是在本地机器上的Docker内部?以下内容:

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

或者在kubernetes集群上,在docker中,通过https??

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

多功能,多逗号!

你还会吗?

是的,我有,这是我的笔记:

抄袭

使用 /copy 在任何系统上有效地执行文件操作 psql 命令正在上运行,作为正在执行该命令的用户 1 . 如果连接到远程服务器,在执行的系统上复制数据文件很简单 PSQL 到/从远程服务器。

COPY 在服务器上作为后端进程用户帐户执行文件操作(默认 postgres )文件路径和权限将被相应地检查和应用。如果使用 TO STDOUT 然后跳过文件权限检查。

这两个选项都需要随后的文件移动,如果 PSQL 不是在您希望结果csv最终驻留的系统上执行。根据我的经验,这是最有可能的情况,当您主要使用远程服务器时。

为了简单的csv输出,通过ssh将TCP/IP隧道配置到远程系统更为复杂,但是对于其他输出格式(二进制),最好是 复制 通过隧道连接,执行本地 PSQL . 类似地,对于大型导入,将源文件移动到服务器并使用 拷贝 可能是最高性能的选项。

PSQL参数

使用psql参数,您可以像csv一样格式化输出,但也有一些缺点,例如必须记住禁用寻呼机而不获取头:

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

其他工具

不,我只想在不编译和/或安装工具的情况下从服务器中获取csv。

在pgadmin iii中,有一个选项可从查询窗口导出到文件。在主菜单中,它是“查询”->执行到文件,或者有一个按钮执行相同的操作(它是一个带蓝色软盘的绿色三角形,而不是只运行查询的普通绿色三角形)。如果您没有从查询窗口运行查询,那么我将按照imsop的建议执行,并使用copy命令。

我正在研究AWS Redshift,它不支持 COPY TO 特征。

不过,我的BI工具支持以制表符分隔的CSV,因此我使用了以下内容:

 psql -h  dblocation  -p port -U user  -d dbname  -F $'\t' --no-align -c " SELECT *   FROM TABLE" > outfile.csv

我写了一个叫做 psql2csv 它封装了 COPY query TO STDOUT 模式,产生正确的csv。它的接口类似于 psql .

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

假设查询是stdin的内容(如果存在)或最后一个参数。所有其他参数都会转发到psql,除了:

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

如果您有更长的查询,并且希望使用psql,那么将查询放到一个文件中,并使用以下命令:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv

我尝试了几件事,但很少有人能给我想要的带标题细节的csv。

这就是对我有用的。

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv

新版本-PSQL 12-将支持 --csv .

psql - devel

——猪瘟病毒

切换到csv(逗号分隔值)输出模式。这相当于 \ pset格式csv .

---

CSVI场域

指定要在csv输出格式中使用的字段分隔符。如果分隔符出现在字段值中,则该字段将按照标准的csv规则以双引号输出。默认值为逗号。

用途:

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv

要下载以列名为标题的csv文件,请使用以下命令:

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;

JackDB 作为Web浏览器中的数据库客户端,这非常容易。尤其是当你在Heroku的时候。

它允许您连接到远程数据库并对其运行SQL查询。

^Source jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif

连接数据库后,可以运行查询并导出到csv或txt(请参见右下角)。

^{注: 我和杰克德没有任何关系。我现在使用他们的免费服务,认为这是一个伟大的产品。}

我强烈推荐 DataGrip 是JetBrains的数据库IDE。您可以将一个SQL查询保存到一个csv文件中,并且可以轻松地设置ssh隧道。

我没有与数据报关联,我只是喜欢这个产品!

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'