Is it possible to save a list of values into a SQLite column?

Question

I want 3 columns to have 9 different values, like a list in Python. Is it possible? If not in SQLite, then on another database engine?

Answer 1

Generally, you do this by stringifying the list (with repr()), and then saving the string. On reading the string from the database, use eval() to re-create the list. Be careful, though that you are certain no user-generated data can get into the column, or the eval() is a security risk.

Answer 2

You must serialize the list (or other Python object) into a string of bytes, aka "BLOB";-), through your favorite means (marshal is good for lists of elementary values such as numbers or strings &c, cPickle if you want a very general solution, etc), and deserialize it when you fetch it back. Of course, that basically carries the list (or other Python object) as a passive "payload" -- can't meaningfully use it in WHERE clauses, ORDER BY, etc.

Relational databases just don't deal all that well with non-atomic values and would prefer other, normalized alternatives (store the list's items in a different table which includes a "listID" column, put the "listID" in your main table, etc). NON-relational databases, while they typically have limitations wrt relational ones (e.g., no joins), may offer more direct support for your requirement.

Some relational DBs do have non-relational extensions. For example, PostGreSQL supports an array data type (not quite as general as Python's lists -- PgSQL's arrays are intrinsically homogeneous).

Answer 3

Your question is difficult to understand. Here it is again:

I want 3 columns to have 9 different values, like a list in Python. Is it possible? If not in SQLite, then on another database engine?

Here is what I believe you are asking: is it possible to take a Python list of 9 different values, and save the values under a particular column in a database?

The answer to this question is "yes". I suggest using a Python ORM library instead of trying to write the SQL code yourself. This example code uses Autumn:

import autumn
import autumn.util
from autumn.util import create_table

# get a database connection object
my_test_db = autumn.util.AutoConn("my_test.db")


# code to create the database table
_create_sql = """\
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   value INTEGER NOT NULL,
   UNIQUE(value)
);"""

# create the table, dropping any previous table of same name
create_table(my_test_db, _create_sql)

# create ORM class; Autumn introspects the database to find out columns
class MyTest(autumn.model.Model):
    db = my_test_db


lst = [3, 6, 9, 2, 4, 8, 1, 5, 7]  # list of 9 unique values

for n in lst:
    row = MyTest(value=n)  # create MyTest() row instance with value initialized
    row.save()  # write the data to the database

Run this code, then exit Python and run sqlite3 my_test.db. Then run this SQL command inside SQLite: select * from mytest; Here is the result:

1|3
2|6
3|9
4|2
5|4
6|8
7|1
8|5
9|7

This example pulls values from one list, and uses the values to populate one column from the database. It could be trivially extended to add additional columns and populate them as well.

If this is not the answer you are looking for, please rephrase your request to clarify.

P.S. This example uses autumn.util. The setup.py included with the current release of Autumn does not install util.py in the correct place; you will need to finish the setup of Autumn by hand.

You could use a more mature ORM such as SQLAlchemy or the ORM from Django. However, I really do like Autumn, especially for SQLite.