Mapping python objects into msql... 
Author Message
 Mapping python objects into msql...

I *finally* managed to get through to the FTP site with mSQL today so I
could begin evaluating it as a potential replacement for my currently
handcrafted database based on bsddb.  Naturally, I'll be using it through a
python interface.  I have a few questions (not all Python-specific I
realize) and would appreciate any and all feedback people can give me.

1. I'm storing Python instances in a database by storing their __dict__
attribute (sort of like pickle/shelve, but I use marshal instead).  Most of
the fields are simple strings or numbers, but two are tuples and some of the
strings, though normally reasonably small, can get rather large.

    a. What do people use for mapping arbitrary length lists/tuples (of
       strings in this case) onto mSQL?  I need to be able to perform
       SELECTs on those fields.

    b. Is there some way to represent strings that can be long (up to 300
       characters in my case) but are typically short (under 30 characters)
       that doesn't suffer the storage penalty for 300-character strings in
       all cases?  (Is this a non-issue due to the generally greater storage
       density of mSQL than bsddb?)

2. How does performance accessing an mSQL database compare with other
databases like bsddb?  (I realize this is a rather loaded question, since
bsddb doesn't itself support queries.  They must be coded in C or Python.)
My database records typically have about 20 fields each.  The databases have
between 100 and 10,000 records each. Pointers to any and all
performance-related information would be appreciated.

Thanks,

Skip Montanaro     |  Today's Acronym: YAUWWWI (yow'-ee) Yet Another Useless

(518)372-5583      |  ---  Check out Musi-Cal: http://www.*-*-*.com/



Sun, 08 Nov 1998 03:00:00 GMT  
 Mapping python objects into msql...

|>     a. What do people use for mapping arbitrary length lists/tuples (of
|>        strings in this case) onto mSQL?  I need to be able to perform
|>        SELECTs on those fields.
|>     b. Is there some way to represent strings that can be long (up to 300
|>        characters in my case) but are typically short (under 30 characters)
|>        that doesn't suffer the storage penalty for 300-character strings in
|>        all cases?  (Is this a non-issue due to the generally greater storage
|>        density of mSQL than bsddb?)

Something I have learnt from lurking on the mSQL mailing list: One option is to
wait for the release of mSQL 2.0, which sounds like it will be weeks rather than
months.  It will support variable length character fields.  I think some
alternative solutions are discussed in the mSQL FAQ.

|> 2. How does performance accessing an mSQL database compare with other
|> databases like bsddb?  (I realize this is a rather loaded question, since
|> bsddb doesn't itself support queries.  They must be coded in C or Python.)
|> My database records typically have about 20 fields each.  The databases have
|> between 100 and 10,000 records each. Pointers to any and all
|> performance-related information would be appreciated.

I have recently completed a big change-over from bsddb to mSQL and I am very
happy with it, but not because of performance, which has plummeted.  Reliability
and stability seem much improved, the software is being very actively supported,
and it is nice to be able to check the contents of databases on the fly without
having to worry about interfering with running Python processes.  If my Python
crashes the database is unaffected... wish you could say the same thing about
bsddb!

Performance comparisons would depend on your application.  If you have a unique
primary key and can do look ups based on that I am sure you will be OK.  Joins on
the other hand can be very slow.  I was using the Btrees in bsddb and so I have
to wait for mSQL 2.0 (which will include Btree indexing) to regain the
performance I had.

A final word of warning... make sure you use the mSQL module on www.python.org,
as it is much newer than the one at the mSQL site.

Marko

.Marko Balabanovic.......Department of Computer Science....................

.Gates Building 1A       Phone: 415 725 8783            Fax: 415 725 1449 .
.Stanford CA 94305-9010  Url:   http://robotics.stanford.edu/people/marko .
.USA.......................................................................



Mon, 09 Nov 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Python mSQL Module v2.1 for mSQL 2.0 (and hopefully mSQL 1.X too!)

2. Python and mSQL

3. msql and python

4. python and msql

5. mSQL 2.0 and Python 1.4

6. Python/mSQL Interface

7. python<->msql?

8. First release of python mSQL interface available.

9. Python interface to msql

10. Object To Relational Mapping War Stories

11. Object <-> XML Mapping

12. Object <-> Relational Mapping (TopLink)

 

 
Powered by phpBB® Forum Software