5

I have a SQL database that I store python lists in. Currently I convert the list to a string and then insert it into the database (using sqlite3) i.e.

foo = [1,2,3]
foo = str(foo)

#Establish connection with database code here and get cursor 'cur'

cur.execute("INSERT INTO Table VALUES(?, ?)", (uniqueKey, foo,))

It seems strange to convert my list to a string first, is there a better way to do this?

rwolst
  • 12,904
  • 16
  • 54
  • 75
  • 2 ways. 1. Normalize tables, to you need setup new table for list value. so you get something like "TABLE list(id)" and "TABLE list_values(list_id, value)". 2. You can serialize the list. Ex. Json, XML and so on (its not a very good practice in SQL). – user1759572 Sep 02 '13 at 22:33
  • you can store your lists as blobs: http://stackoverflow.com/questions/537077/python-sqlite3-how-to-convert-a-list-to-a-blob-cell – alecxe Sep 02 '13 at 22:35

2 Answers2

5

Replace your (key, listdata) table with (key, index, listitem). The unique key for the table becomes (key, index) instead of just key, and you'll want to ensure as a consistency condition that the set of indexes in the table for any given key is contiguous starting from 0.

You may or may not also need to distinguish between a key whose list is empty and a key that doesn't exist at all. One way is to have two tables (one of lists, and one of their elements), so that an empty but existing list is naturally represented as a row in the lists table with no corresponding rows in the elements table. Another way is just to fudge it and say that a row with index=null implies that the list for that key is empty.

Note that this is worthwhile if (and probably only if) you want to act on the elements of the list using SQL (for example writing a query to pull the last element of every list in the table). If you don't need to do that, then it's not completely unreasonable to treat your lists as opaque data in the DB. You're just losing the ability for the DB to "understand" it.

The remaining question then is how best to serialize/deserialize the list. str/eval does the job, but is a little worrying. You might consider json.dumps / json.loads, which for a list of integers is the same string format but with more safety restrictions in the parser. Or you could use a more compact binary representation if space is an issue.

Steve Jessop
  • 273,490
  • 39
  • 460
  • 699
  • 1
    One thing to be considered is that the OP might not really *need* to store an indexed list in the DB to begin with, and that your data might conceptually work just as well as a set that can be sorted later, and you can use a simpler 1:N relationship. I.e. it might be the case that the OP mentions lists mostly because they're the "default" collection in Python, not because he needs all their behaviour. – millimoose Sep 02 '13 at 22:52
  • 1
    @millimoose: good point. If all that's really needed is an unordered 1:N mapping (like a Python `set`) then the `index` column isn't needed, although of course the `key` column needs to be made non-unique. – Steve Jessop Sep 02 '13 at 22:53
  • Thanks Steve, this is actually just what I needed. My Python list is a 2D list with rows indexed by dates, so I will use that as the `index` value in the SQL table and store the rest of the corresponding row as opaque data in `listitem`. – rwolst Sep 02 '13 at 23:08
0

2 ways.

  1. Normalize tables, to you need setup new table for list value. so you get something like "TABLE list(id)" and "TABLE list_values(list_id, value)".

  2. You can serialize the list and put in a column. Ex. Json, XML and so on (its not a very good practice in SQL).

user1759572
  • 683
  • 4
  • 11