Python SQL: storing objects or iterables

Assume I want to store via sqlite3 a simple list of strings:

my_list = ['a','b','c']

or a python object with properties and methods.

What I tried so far is to serialize (pickle) my_list, and the returned byte representation is b'x80x03]qx00(Xx01x00x00x00aqx01Xx01x00x00x00bqx02Xx01x00x00x00cqx03e.'. However, cannot be stored within a BLOB variable.

Should I use a string variable instead, or should I find a way to convert the serialized object into a different format to get rid of the before storing?

Note that I’m learning SQL and the fundamentals of data conversion.

Answer

Take a look into how to serialize the data. Storing complex objects into a relational database (sqlite) is not easy. I suggest saving the data as a json string or perhaps in a JSONField.

May I ask, how are you accessing the sqlite? (eg. sqlite module, via django models, flask or other)

You can take this code as a reference (I am using sqlite3 module). It think you might have missed sqlite3.Binary(binary_obj)

import sqlite3
import sqlite3
import pickle


def store_in_db(obj):
    binary_obj = pickle.dumps(obj)
    print('binary_obj: ', binary_obj)

    conn = sqlite3.connect('demo.db')
    conn.execute('CREATE TABLE IF NOT EXISTS DemoTable (binary_field BLOB)')
    conn.execute('INSERT INTO DemoTable VALUES(?)', [sqlite3.Binary(binary_obj)])
    conn.commit()
    conn.close()


def retrieve_from_db():
    conn = sqlite3.connect('demo.db')
    row = conn.execute('SELECT * FROM DemoTable').fetchone()
    obj = pickle.loads(row[0])
    conn.close()

    return obj


my_list = ['a', 'b', 'c']
store_in_db(my_list)

print(retrieve_from_db())