如何通过查询关系数据库获取CSV字符串? [英] How to get a CSV string from querying a relational DB?

问题描述

我正在查询关系数据库，我需要将结果作为CSV字符串.我无法像在无服务器环境中运行一样将其保存在磁盘上(我无法访问磁盘).

I'm querying a relational Database and I need the result as a CSV string. I can't save it on the disk as is running in a serverless environment (I don't have access to disk).

有什么主意吗?

推荐答案

PyGreSQL的游标具有方法复制到.它接受类似文件的对象作为 stream 对象(该对象必须具有 write()方法). io.StringIO 确实满足此条件，并且不需要访问磁盘，因此应该可以做到:

PyGreSQL's Cursor has method copy_to. It accept as stream file-like object (which must have a write() method). io.StringIO does meet this condition and do not need access to disk, so it should be possible to do:

import io
csv_io = io.StringIO()
# here connect to your DB and get cursor
cursor.copy_to(csv_io, "SELECT * FROM table", format="csv", decode=True)
csv_io.seek(0)
csv_str = csv_io.read()

说明:许多python模块接受类文件对象，这意味着您可以使用 io.StringIO()或 io.BytesIO()代替真实的文件句柄.这些mimick文件分别以文本和字节模式打开.与文件一样，读者也有位置，因此我确实尝试在使用后开始.最后一行确实创建了 csv_str ，它只是普通的 str .请记住要根据您的需要调整SQL查询.

Explanation: many python modules accept file-like object, meaning you can use io.StringIO() or io.BytesIO() in place of true file-handles. These mimick file opened in text and bytes modes respectively. As with files there is position of reader, so I do seek to begin after usage. Last line does create csv_str which is just plain str. Remember to adjust SQL query to your needs.

注意:我没有测试上面的代码，请自己尝试并编写是否可以正常工作.

Note: I do not tested above code, please try it yourself and write if it works as intended.

这篇关于如何通过查询关系数据库获取CSV字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！