将PostgreSQL行转换为文本，但将列分开 [英] Cast a PostgreSQL row to text, but keep columns separate

问题描述

我正在使用Python开发Postgres查看器。我需要将所有列都转换为文本，以便可以在GUI中显示它们。我不知道每个表格有多少列或它们的名称，因为这应该是通用查看器。 Google为我提供了此代码：

I am working on a Postgres viewer using Python. I need to convert all columns to text so I can display them in my GUI. I don't know how many columns there are per table or the names of them as this is supposed to be a generic viewer. Google got me this code:

SELECT t.*::text FROM table AS t;

但是，它将以下行连接起来：

However, this concatenates the row like this:

t
----------------|-------
(712,982,dfdfv)

我需要的是它（当然带有文本），就像普通的 SELECT * 确实：

What I need is this (with type text of course), just like a normal SELECT * does:

id  | vendor_id | vendor_barcode
----|-----------|---------------
712 | 982       | dfdfv

Edit1：我无法在Python中转换数据类型，因为None会变成'None '。

I can't convert the datatypes in Python, because a None will become a 'None'.

Edit2：我需要cursor.description（）中的列名，所以不能使用t。* :: text。

I need the column names from cursor.description(), so I can't use t.*::text.

推荐答案

好，所以@Jon Clements提供的一种解决方案是：

Ok, so one solution offered by @Jon Clements is:

 c = connection.cursor()
 c.execute("SELECT * FROM my_table")
 rows = (str(col) if col is not None else None for col in c.fetchall())
 for row in rows:
     print (row)

我的最终解决方案是：

c.execute("SELECT * FROM my_table LIMIT 1")
select_sql = "SELECT "
for row in c.description:
    column_name = row.name
    if type_ == 17: #binary data
        select_sql += " '<binary data>',"
    else:
        select_sql += " CASE WHEN %s::text='' THEN '''''' ELSE %s::text END," % (column_name, column_name)
select_sql = select_sql[:-1] + " FROM my_table" #remove trailing comma and add table name
c.execute(select_sql)

将空白字符串转换为''并保留 None 值。我的代码实际上更广泛，因为它需要创建treeview列等。希望这对以后会来这里的人有所帮助。

Which converts blank strings to '' and preserves None values. My code is actually more extensive, because it needs to create treeview columns and etc. Hopefully this helps out anyone coming here in the future.

这篇关于将PostgreSQL行转换为文本，但将列分开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！