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.
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']
Is there a specific amount of items in the list?
If there is, try using a for loop to insert every item into its own column.
Also you could try joining the list together and when retrieving it splitting the string like this:
list = ['Hope','this','helps']
'|'.join(list)
After loading:
list = result.split('|')
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"]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With