python操作postgresql存储json与列表数据

天黑前最后的余辉

已于 2024-08-05 15:59:11 修改

阅读量4k

点赞数 1

分类专栏：我的博客文章标签： python postgresql

于 2020-09-01 11:16:57 首次发布

本文链接：https://blog.youkuaiyun.com/a961634066/article/details/108336442

版权

我的博客专栏收录该内容

25 篇文章

订阅专栏

本文介绍如何使用Python与PostgreSQL交互，包括创建包含JSON和列表字段的表、插入JSON数据、存储和查询列表数据的方法。还介绍了直接使用SQL进行数据操作的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

自己摸索瞎记录的，以备下次使用，有知道更好方法，请指出

1. 建表sql

CREATE TABLE table_name (
    aaa json NULL,
	bbb json[] NULL,
	ccc text[] NULL）

2. 插入sql

(1) 存储json

保存时，先dumps，然后传参存储，查询后再loads

import json

from psycopg2.extras import Json

cur = conn.cursor()
params = {"a":1, "b":2}
cur.execute('insert into table_name(aaa) values (%s)',[json.dumps(params)])

（2）存储list

dict数组

dict_list = [{"a": 1},{"b": 2}]
# 我的做法,不dumps，会报错...dict....,dumps时，加入ensure_ascii，不会将中文转为unicode
new_dict_list = [json.dumps(tmp, ensure_ascii=False) for tmp in dict_list]
insert_sql = 'insert into table_name(bbb) values (%s::json[])' % new_dict_list

text，varchar数组未测试，原理上应该跟json一样，下次亲测后更新......

(3) 数组中是字符串查询（数组中是否包含某值）

sql = "select * from table_name where '123'=any(ccc)"


# 取数组中元素，索引从1开始
sql = "select ccc[1] from table_name"

（4）数组中是json对象，根据json中的字段查询

-> 操作符：它会提取 JSON 对象中的值，并保持该值的原始数据类型。
->> 操作符：它会提取 JSON 对象中的值，但无论原始数据类型是什么，提取出的结果都会被转换为文本类型

使用发现：取值时，两个都可以使用，做判断时，只能使用->>

sql = "select * from table_name where bbb->>id='1'"

3. 不使用python，原始插入

用单引号
insert into test values(1,'{1,2,3}');
insert into test values(2,'{4,5,6}');
用array
insert into test values(3,array[7,8,9]);
insert into test values(4,array[4,5,6]);