[ACCEPTED]-Python, Sqlite3 - How to convert a list to a BLOB cell-sqlite

It seems that Brian's solution fits your 9 needs, but keep in mind that with that method 8 your just storing the data as a string.

If 7 you want to store the raw binary data into 6 the database (so it doesn't take up as much 5 space), convert your data to a Binary sqlite object 4 and then add it to your database.

query = u'''insert into testtable VALUES(?)'''
b = sqlite3.Binary(some_binarydata)
cur.execute(query,(b,))
con.commit()

(For some 3 reason this doesn't seem to be documented 2 in the python documentation)

Here are some 1 notes on sqlite BLOB data restrictions:

http://effbot.org/zone/sqlite-blob.htm

Assuming you want it treated as a sequence 5 of 8-bit unsigned values, use the array module.

a = array.array('B', data)
>>> a.tostring()
'\x00\x01\x02\x03\x04\x05'

Use 4 different typecodes than 'B' if you want to 3 treat the data as different types. eg. 'b' for 2 a sequence of signed bytes, or 'i' for a signed 1 integer.

I have the same problem, and I'm thinking 12 about solving this in another way.

I think 11 the pickle module is done exactly for something 10 like this (serialization on python objects)

Example 9 (this one is for dumping to file... but 8 I think it's easily changeble for db storage)

Saving:

# Save a dictionary into a pickle file.
import pickle
favorite_color = { "lion": "yellow", "kitty": "red" }
pickle.dump( favorite_color, open( "save.p", "w" ) )

Loading:

# Load the dictionary back from the pickle file.
import pickle
favorite_color = pickle.load( open( "save.p" ) )

IMHO 7 I think this way is more elegant and safer(it 6 works for any python object).

That's my 2 5 cents

UPDATE: After doing a bit of search on my idea, they show some gotchas on 4 my solution ( I can't make sql searches 3 on that field). But I still think that it's 2 a decent solution (if you don't need to 1 search that field.

See this general solution at SourceForge 9 which covers any arbitrary Python object 8 (including list, tuple, dictionary, etc):

y_serial.py 7 module :: warehouse Python objects with 6 SQLite

"Serialization + persistance 5 :: in a few lines of code, compress and 4 annotate Python objects into SQLite; then 3 later retrieve them chronologically by keywords 2 without any SQL. Most useful "standard" module 1 for a database to store schema-less data."

http://yserial.sourceforge.net

It is possible to store object data as pickle 9 dump, jason etc but it is also possible 8 to index, them, restrict them and run select 7 queries that use those indices. Here is 6 example with tuples, that can be easily 5 applied for any other python class. All 4 that is needed is explained in python sqlite3 3 documentation (somebody already posted the 2 link). Anyway here it is all put together 1 in the following example:

import sqlite3
import pickle

def adapt_tuple(tuple):
    return pickle.dumps(tuple)    

sqlite3.register_adapter(tuple, adapt_tuple)    #cannot use pickle.dumps directly because of inadequate argument signature 
sqlite3.register_converter("tuple", pickle.loads)

def collate_tuple(string1, string2):
    return cmp(pickle.loads(string1), pickle.loads(string2))

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

con.create_collation("cmptuple", collate_tuple)

cur = con.cursor()
cur.execute("create table test(p tuple unique collate cmptuple) ")
cur.execute("create index tuple_collated_index on test(p collate cmptuple)")

cur.execute("select name, type  from sqlite_master") # where type = 'table'")
print(cur.fetchall())

p = (1,2,3)
p1 = (1,2)

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("insert into test(p) values (?)", (p1,))
cur.execute("insert into test(p) values (?)", ((10, 1),))
cur.execute("insert into test(p) values (?)", (tuple((9, 33)) ,))
cur.execute("insert into test(p) values (?)", (((9, 5), 33) ,))

try:
    cur.execute("insert into test(p) values (?)", (tuple((9, 33)) ,))
except Exception as e:
    print e

cur.execute("select p from test order by p")
print "\nwith declared types and default collate on column:"
for raw in cur:
    print raw

cur.execute("select p from test order by p collate cmptuple")
print "\nwith declared types collate:"
for raw in cur:
    print raw

con.create_function('pycmp', 2, cmp)

print "\nselect grater than using cmp function:"
cur.execute("select p from test where pycmp(p,?) >= 0", ((10, ),) )
for raw in cur:
    print raw

cur.execute("select p from test where pycmp(p,?) >= 0", ((3,)))
for raw in cur:
    print raw 

print "\nselect grater than using collate:"
cur.execute("select p from test where p > ?", ((10,),) )
for raw in cur:
    print raw  

cur.execute("explain query plan select p from test where p > ?", ((3,)))
for raw in cur:
    print raw

cur.close()
con.close()