python - Adding dict object to postgresql

Question

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.

so for instance:

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?

Answer 1

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
);

(... 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'})])

and selecting would be as simple as

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

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'}]]