python - 无法从数据库返回数组

Question

我的 Snowflake 数据库中有一个表。我想从列中获取数据。

数据示例:

id          tid           categories
------------------------------------------------------------------------------
v-123       t-123         ["tx-b5mmqcpooa0p","tx-b5mmqcpood0p"]
b-123       t-124         ["tx-b5mmfcpooa0p","tx-b4mmqcpood0p"]
x-123       t-125         []
b-123       t-124         ["tx-b5mmfcpooa0p","tx-b4mmqcpood0p","tx-bmqcpood0p"]

我尝试使用pandas:

sql = SELECT id, tid, categories FROM table 
df = pandas.read.sql(con, sql)

我得到了这个结果:

id          tid           categories
---------------------------------------------------------------------------
"v-123"       "t-123"         " ["tx-b5mmqcpooa0p","tx-b5mmqcpood0p"] "
"b-123"       "t-124"         " ["tx-b5mmfcpooa0p","tx-b4mmqcpood0p"] "
"x-123"       "t-125"         " [] "
"b-123"       "t-124"         " ["tx-b5mmfcpooa0p","tx-b4mmqcpood0p","tx-bmqcpood0p"] "

“categories”的类型是字符串而不是数组(列表)。

我做错了什么？

谢谢!

Answer 1

雪花文档 https://docs.snowflake.net/manuals/user-guide/sqlalchemy.html#variant-array-and-object-support建议使用 json.loads 转换为数组。