7 Answers
7

You can't store the object itself in the DB. What you do is to store the data from the object and reconstruct it later.

A good way is to use the excellent SQLAlchemy library. It lets you map your defined class to a table in the database. Every mapped attribute will be stored, and can be used to reconstruct the object. Querying the database returns instances of your class.

With it you can use not only sqlite, but most databases - It currently also supports Postgres, MySQL, Oracle, MS-SQL, Firebird, MaxDB, MS Access, Sybase, Informix and IBM DB2. And you can have your user choose which one she wants to use, because you can basically switch between those databases without changing the code at all.

There are also a lot of cool features - like automatic JOINs, polymorphing...

First,thank you all for your answers/guide/tips!!! and @ nosklo ,thanks you sample code worked like a charm :) Thanks a lot. I'll explore more about sqlalchemy with your example- Seems like that will suit my requirement..thanks you all again.
–
lakshmipathiJan 12 '10 at 10:03

First,thank you all for your answers/guide/tips!!! and @ nosklo ,thanks you sample code worked like a charm :) Thanks a lot. I'll explore more about sqlalchemy with your example- Seems like that will suit my requirement more than pickle..thanks you all again.
–
lakshmipathiJan 12 '10 at 10:05

Yeah, IMHO pickle is not worth it. It seems cool and all, but you can't filter/join/agreggate data since the object is stored as a blob, defeating the purpose of using a database in first place.
–
noskloJan 12 '10 at 10:20

+1 Great answer. I think pick works only for object which need no searching.
–
Adam MatanJan 12 '10 at 10:25

You can use object relational mapping. This creates, in effect, a "virtual object database" that can be used from within the programming language (Wikipedia). For python, there is a nice toolkit for that: sqlalchemy.

One option is to use an O/R mapper like SQLObject. It will do most of the plumbing to persist the Python object to a database, and it supports SQLite. As mentioned elsewhere you can also serialise the object using a method such as pickle, which dumps out a representation of the object that it can reconstruct by reading back in and parsing.

You can use pickle to serialize the object. The serialized object can be inserted to the sqlite DB as a bytearray field.

f=open('object.dump', 'rw')
pickle.dump(obj, f)

Now read object.dump from the file, and write it to the sqlite DB. You might want to write it as a binary data type; read about storing binary data and blob in SQLite here. Note that according to this source, SQLite limits the size of such datafield to 1Mb.

I think that a better option would be serializing your object into a file, and keeping the file name, not contents, in the database.

This is far from good practise. Storing pickled objects as blobs in a database subverts the reasoning for using a database, searchability. storing the filenames in the database because of file size restrictions does not make this any better.
–
devsndFeb 28 '13 at 10:28

There is relatively simple way to store and compare objects, eaven to index those objects right way and to restrict (with ubique) columns containing objects. And all of that without using ORM engines. Objects mast be stored using pickle dump (so performance might be a issue) Here is example for storing python tuples, indexing restricting and comparing. This method can be easily applied to any other python class. All that is needed is explained in python sqlite3 documentation (somebody already posted the link). Anyway here it is all put together in the following example: