Python: Proper way to store list of strings in sqlite3 or mysql

Question

I'm trying to store a list of string values into a sql database.

list = ['please','help','me']

I've tried converting to unicode and using the array.array('u',list)

unicode_list = unicode(list)
array.array('u', list)

This is not producing the results that I want. I'm not too concerned with being able to search the value once inserted in the database. Lastly, I'm not looking to use a NOSQL alternative as this time. Any help would be greatly appreciated. Thanks in advance.

Are you trying to store each value in a separate row, in a separate column, or in one column? If in one column, consider using JSON or another well-defined serialization format. — Martijn Pieters, Dec 07 '13 at 17:17

score 13 · Accepted Answer · answered Dec 07 '13 at 17:18

13

Use a proper serialization mechanism such as JSON, and store it in a text field.

>>> json.dumps(['please','help','me'])
'["please", "help", "me"]'
>>> json.loads('["please", "help", "me"]')
[u'please', u'help', u'me']

answered Dec 07 '13 at 17:18

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

Thanks, I saw a similar solution referenced here http://stackoverflow.com/questions/11076107/python-sqlite-storing-lists-as-binaries-blobs but wasn't sure if it was a proper implementation. Any repercussions for using this technique? – django-d Dec 07 '13 at 17:23
Encoding as JSON makes sure that any problematic characters are encoded properly. – Ignacio Vazquez-Abrams Dec 07 '13 at 17:24
@IgnacioVazquez-Abrams Please post your answer on the link so people there are lead down a rabbit hole. – WinEunuuchs2Unix Feb 09 '21 at 23:36
for Chinese zh-cn: `json.dumps(['please','help','me'], ensure_ascii=False)` – C.K. Jul 09 '22 at 22:04

score 2 · Answer 2 · answered Mar 12 '19 at 15:51

To add to Ignacio Vazquez-Abrams's answer, here's the complete walkthrough for storing a list or a list of list into Sqlite DB.

>>> # Create dataframe
>>> df = pd.DataFrame({'Doc:': ["A", "B", "C"],
>>>                   'Doc_Pages': [31, 20, 45],
>>>                   'Data': [["A B C", "D E F"], ["G H I", "J K L"], ["M N O", "P Q R"]]
>>>                   })

>>> print(df)
Doc   Doc_Pages      Data
A       31       ["A B C", "D E F"]
B       20       ["G H I", "J K L"]
C       45       ["M N O", "P Q R"]

>>> # Create DB
>>> import sqlite3
>>> conn = sqlite3.connect('db.sqlite')
>>> cur = conn.cursor()
>>> cur.execute('CREATE TABLE IF NOT EXISTS TableName(ID INTEGER PRIMARY KEY AUTOINCREMENT)')

>>> # Store df into DB
>>> df.Data = df.Data.apply(lambda x: json.dumps(x))
>>> df.to_sql(name='Extracted_Data', con=conn, if_exists='replace')

>>> # Load df from DB
>>> df_new = pd.read_sql('SELECT * FROM TableName', con=conn)
>>> df_new.Data = df_new.Data.apply(lambda x: json.loads(x))

>>> print(df_new)
Doc   Doc_Pages      Data
A       31       ["A B C", "D E F"]
B       20       ["G H I", "J K L"]
C       45       ["M N O", "P Q R"]

Python: Proper way to store list of strings in sqlite3 or mysql

2 Answers2

Linked