Re: Database file size issues

Andrea Gavana wrote:
>> See the following link, it might be the answer you are looking
>> for...
>> 
>> http://www.sqlite.org/lang_vacuum.html
>> 
>> In short, VACUUM tidies up the database.
> 
> Thank you so much! I was going crazy about this issue...

See also http://www.sqlite.org/pragma.html which will automatically 
reduce database size (at the expense of speed) after deletion.

By default the pages that held (now) deleted rows are not removed for 
efficiency reasons. The database shouldn't grow without bound if you 
repeatedly insert and delete the same data. If your SQLAlchemy demo does 
this for repeated inserts (no deletes) then there are probably no 
uniqueness indexes on the table. One of the demos I played with does 
this (can't remember which) but it's a feature of the schema design, not 
a bug. If you add the index you'll be able to insert and delete all day 
long and the database file won't grow bigger than its working set.

SQLAlchemy is probably the wrong place to start if you're a database 
newbie. I've been using SQLite and its Python wrappers for a couple of 
years and it still took a while to get my head around the flexibility it 
provides. The tutorial is very good, but is a little thin in same areas 
(opening existing databases and detecting whether tables needed to be 
created, handling schema changes etc). One undocumented thing that took 
me ages to track down was how to get it to close its internal SQLite 
connection when writing unit tests inside Pythonwin.

If you're a Pythoneer then you should have a look at Roger Binns' apsw, 
a thin wrapper around SQLite which keeps close to SQLite's C API. Avoid 
Python's bundled wrapper because among other things (like automagically 
starting transactions for you) it implements Python's DBAPI interface 
and you're not going to be using that.

If you play with apsw first (an hour would be enough) a lot of the 
SQLAlchemy stuff will be easier.

I was only going to post the autovacuum link, but seem to have gone on a 
bit longer than I intended. Hope it helps.

Martin