Python SQL: storing objects or iterables

Question

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'\x80\x03]q\x00(X\x01\x00\x00\x00aq\x01X\x01\x00\x00\x00bq\x02X\x01\x00\x00\x00cq\x03e.'. 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.

The \ are not part of the byte string; they're introduced when you print it out — Jiří Baum
– Jiří Baum, Commented Sep 21, 2021 at 10:54
Also, a blob can definitely store \ ; if you're having trouble, you're probably missing the use of placeholders and parameters when running your SQL statements — Jiří Baum
– Jiří Baum, Commented Sep 21, 2021 at 11:07

Nishan Paudel · Accepted Answer · 2021-09-22 06:04:33Z

1

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

edited Sep 22, 2021 at 6:04

answered Sep 21, 2021 at 11:03

Nishan Paudel

13510 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

snakecharmerb Over a year ago

What is the purpose of calling sqlite3.Binary? bytes instances can be saved directly.

Collectives™ on Stack Overflow

Python SQL: storing objects or iterables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related