将dict对象添加到Postgresql [英] Adding dict object to postgresql

问题描述

所以我在Python3.5上使用psycopg2将一些数据插入到postgresql数据库中.我想做的是有两列为字符串，最后一列只是一个dict对象.我不需要搜索字典，只需将其从数据库中拉出并使用它即可.

So I am using psycopg2 on Python3.5 to insert some data into a postgresql database. What I would like to do is have two columns that are strings and have the last column just be a dict object. I don't need to search the dict, just be able to pull it out of the database and use it.

例如:

uuid = "testName"
otherString = ""
dict = {'id':'122','name':'test','number':'444-444-4444'}

# add code here to store two strings and dict to postgresql

cur.execute('''SELECT dict FROM table where uuid = %s''', 'testName')
newDict = cur.fetchone()
print(newDict['number'])

这有可能吗，如果可以的话，我将如何去做呢?

Is this possible, and if so how would I go about doing this?

推荐答案

如果您的PostgreSQL版本足够新(9.4+)，而psycopg版本> = 2.5.4，则所有键均为字符串，并且值可以表示为JSON，最好将其存储到JSONB列中.然后，如果需要，该列也将是可搜索的.只需创建表即可

If your PostgreSQL version is sufficiently new (9.4+) and psycopg version is >= 2.5.4 all the keys are strings and values can be represented as JSON, it would be best to store this into a JSONB column. Then, should the need arise, the column would be searchable too. Just create the table simply as

CREATE TABLE thetable (
    uuid TEXT,
    dict JSONB
);

(...并根据需要自然添加索引，主键等...) 将字典发送到PostgreSQL时，您只需要用 Json 适配器;从PostgreSQL接收时，JSONB值将自动转换为字典，因此插入将变为

(... and naturally add indexes, primary keys etc as needed...) When sending the dictionary to PostgreSQL you just need to wrap it with the Json adapter; when receiving from PostgreSQL the JSONB value would be automatically converted into a dictionary, thus inserting would become

from psycopg2.extras import Json, DictCursor

cur = conn.cursor(cursor_factory=DictCursor)

cur.execute('INSERT into thetable (uuid, dict) values (%s, %s)',
    ['testName', Json({'id':'122','name':'test','number':'444-444-4444'})])

然后选择就像

cur.execute('SELECT dict FROM thetable where uuid = %s', ['testName'])
row = cur.fetchone()
print(row['dict']) # its now a dictionary object with all the keys restored
print(row['dict']['number']) # the value of the number key

使用JSONB，PostgreSQL不仅可以将字典作为文本转储，而且可以更有效地存储值.此外，还可以对数据进行查询，例如，只需从JSONB列中选择一些字段即可:

With JSONB, PostgreSQL can store the values more efficiently than just dumping the dictionary as text. Additionally, it becomes possible to do queries with the data, for example just select the some of the fields from the JSONB column:

>>> cur.execute("SELECT dict->>'id', dict->>'number' FROM thetable")
>>> cur.fetchone()
['122', '444-444-4444']

或者您可以根据需要在查询中使用它们:

or you could use them in queries if needed:

>>> cur.execute("SELECT uuid FROM thetable WHERE dict->>'number' = %s',
    ['444-444-4444'])
>>> cur.fetchall()
[['testName', {'id': '122', 'name': 'test', 'number': '444-444-4444'}]]

这篇关于将dict对象添加到Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！