Save a python list in SQLite

Question

I'm trying to save a list like ex.
mylist = [ 196, 107, 207, 180, etc.] in a table in SQLite. The list is only integers and I want to keep the list as it is. Can I do that? I tried with execute ''' insert into table(field) value(?)''' , (mylist). But always get error msg.

which error message? show code! show table definitions!

Daniel
– Daniel

2014-08-05 23:06:11 +00:00
Commented Aug 5, 2014 at 23:06 — Daniel
– Daniel, Commented Aug 5, 2014 at 23:06

Shady · Accepted Answer · 2022-01-18 17:30:32Z

3

Convert the list to a string on saving, using str(). Then convert it back to a list on loading using eval()

Try this to see for yourself:

x = [1, 2]
print(type(x))
y = str(x)
print(type(y))
z = eval(y)
print(type(z))
print(x)
print(y)
print(z)

answered Jan 18, 2022 at 17:30

Shady

2562 silver badges6 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:11:39Z

You can not store a list of items(Integers in your case) in a column of table. You could either use a different database like MongoDB where would could store a 'document'

{'my_list': [196, 107, 207, 180, etc]}

or come up with a different schema where you have something like the following columns (ID, list_id, list_member). You will then have a new row for each member in your list. Assuming you'll have other lists? you use the same table and have a different list_id. To get the items in your list:
SELECT list_member FROM 'table_name' WHERE list_id is 1

ID     list_id        list_member
 1       1                  196
 2       1                  107
 3       1                  207
 4       1                  180
 5       1                  etc
 6       2                  123
 7       2                  etc

There is also a more detailed answer here How to store a list in a column of a database table

T. Schmidbauer · Accepted Answer · 2022-01-24 22:11:55Z

2

Another way would be to convert the list to a json string using the builtin json module.

to_dump = [1,2,3,4]
sql_as_text = json.dumps(to_dump)

(write the "sql_as_text" variable as text to your database)

And when you want to reuse your data, just do it the other way around

to_dump = json.loads(sql_as_text)

This method is also applicable for python dictionaries.

answered Jan 24, 2022 at 22:11

T. Schmidbauer

235 bronze badges

Collectives™ on Stack Overflow

Save a python list in SQLite

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related