Re: python an sqlite objects

azrael wrote:
It logical that it would be more efficient and logical to use a object
oriented database, but in this case I ask because of the portable
nature of sqlite.

so, if I get it right, this should be possible [...]

Did you try it? Did it work? If so,it was pure luck. Attached is a script that shows how to do it right.

-- Gerhard # This is an example for storing pickleable Python objects in a SQLite
# database

import cPickle as pickle

try:
from pysqlite2 import dbapi2 as sqlite3
except ImportError:
import sqlite3

class Point(object):
def __init__(self, x, y):
self.x, self.y = x, y

def __repr__(self):
return "<Point(%s, %s)>" % (self.x, self.y)

def test():
con = sqlite3.connect(":memory:")
cur = con.cursor()

# Make sure you store your pickled
# cur.execute("create table pickled(id integer primary key, data blob)")
cur.execute("create table pickled(id integer primary key, data blob)")

# Here we force pickle to use the efficient binary protocol
# (protocol=2). This means you absolutely must use an SQLite BLOB field
# and make sure you use sqlite3.Binary() to bind a BLOB parameter.
p1 = Point(3, 4)
cur.execute("insert into pickled(data) values (?)", (sqlite3.Binary(pickle.dumps(p1, protocol=2)),))

# If we use old pickle protocol (protocol=0, which is also the default),
# we get away with sending ASCII bytestrings to SQLite.
p2 = Point(-5, 3.12)
cur.execute("insert into pickled(data) values (?)", (pickle.dumps(p2, protocol=0),))

# Fetch the BLOBs back from SQLite
cur.execute("select data from pickled")
for row in cur:
serialized_point = row[0]

# Deserialize the BLOB to a Python object - # pickle.loads() needs a
# bytestring.
point = pickle.loads(str(serialized_point))
print "got point back from database", point

if __name__ == "__main__":
test()

Relevant Pages

  • Re: SQLite speed tests BLOB vs flat-file for those interested
    ... SQLite has built in compacting - VACUUM. ... but for my needs I only occasionally read BLOB ... Or all tables was in one database? ...
    (alt.comp.lang.borland-delphi)
  • Re: Is it just me, or is Sqlite3 goofy?
    ... with any "heavy" database should the need arise to migrate ... Migrating data from SQLite to other ... about how the SQL Language Specification of static typing ... jeremiads on the Python list. ...
    (comp.lang.python)
  • Re: Oracle 10g Express & VB6, replace MSAccess, I hope so
    ... I'll check SQLite off the list. ... Client/Server Applications ... you should consider using a client/server database engine instead ... Each request is managed ...
    (microsoft.public.vb.database.ado)
  • Re: how would you...?
    ... The student also has a picture reference that depicts his GPA based on ... Should I study up on SQLite? ... There are other database modules than SQLite, ...
    (comp.lang.python)
  • Re: Is it just me, or is Sqlite3 goofy?
    ... be saying it has gasoline engine. ... SQL database, with an SQL engine. ... criticisms directed at SQLite. ...
    (comp.lang.python)