4

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.

kosmas
  • 63
  • 1
  • 4

3 Answers3

2

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

Community
  • 1
  • 1
Schechter
  • 224
  • 2
  • 5
1

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)
Shady
  • 216
  • 2
  • 6
0

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.

T. Schmidbauer
  • 3
  • 4