Pysqlite tables in RAM

[Ranjitha]

Hi all,

I'm relatively new to python and am facing a problem with database
access

I want to store my data in a database on the disk. I also want to be
able to reload the tables into the RAM whenever I have a lot of disk
accesses and commit the changes back to the database. There is an
option of storing the data in the RAM where you connect to :memory:
instead of a DB file. The problem with this is that the data is lost
everytime you close the connection to the database. Could somebody
suggest a way to load the tables into the RAM as tables and not as some
lists or dictionaries?

Thanks,
Simba

 

[Bruno Desthuilliers]

Hi all,

I'm relatively new to python

And to databases ?

and am facing a problem with database
access

I want to store my data in a database on the disk. I also want to be
able to reload the tables into the RAM whenever I have a lot of disk
accesses and commit the changes back to the database.

This should be the database duty, not yours. Serious RDBMS are highly
optimized wrt/ caching and file I/O, and there are very few chances you
can do anything better by yourself.

There is an
option of storing the data in the RAM where you connect to :memory:
instead of a DB file. The problem with this is that the data is lost
everytime you close the connection to the database.

Seems quite obvious !-)

Could somebody
suggest a way to load the tables into the RAM as tables and not as some
lists or dictionaries?

There's nothing like a "table" in Python's builtin datatypes !-)

More seriously: don't bother.

Focus first on writing correct code. Then, *if* and *when* you *really*
have a performance problem, *first* use a profiler to check where the
*real* problem is. If it then happens that SQLite is the bottleneck, try
switching to a real RDBMS like PostgreSQL.

Remember the 3 golden rules about optimisation:
1/ don't optimize
2/ don't optimize
3/ for the experts only: don't optimize


My 2 cents...

 

[Steve Holden]

Hi all,

I'm relatively new to python and am facing a problem with database
access

I want to store my data in a database on the disk. I also want to be
able to reload the tables into the RAM whenever I have a lot of disk
accesses and commit the changes back to the database. There is an
option of storing the data in the RAM where you connect to :memory:
instead of a DB file. The problem with this is that the data is lost
everytime you close the connection to the database. Could somebody
suggest a way to load the tables into the RAM as tables and not as some
lists or dictionaries?

As long as your data isn't too voluminous it's quite practical to read
it from a SQLite database on disk into a SQLite database in memory,
given the same table structures on each.

But if the data isn't too voluminous then probably any
halfway-reasonable database will also cache it effectively. So you may
win less performance that you expect.

regards
Steve

 

[John Salerno]

Hi all,

I'm relatively new to python and am facing a problem with database
access

I want to store my data in a database on the disk. I also want to be
able to reload the tables into the RAM whenever I have a lot of disk
accesses and commit the changes back to the database. There is an
option of storing the data in the RAM where you connect to :memory:
instead of a DB file. The problem with this is that the data is lost
everytime you close the connection to the database. Could somebody
suggest a way to load the tables into the RAM as tables and not as some
lists or dictionaries?

Thanks,
Simba

Just a side note: judging from your subject line, are you using the
pysqlite extension? In case you are, SQLite support comes with the
standard library in 2.5 as sqlite3, in case you want to switch.

 

[Fredrik Lundh]

I want to store my data in a database on the disk. I also want to be
able to reload the tables into the RAM whenever I have a lot of disk
accesses and commit the changes back to the database.

using the cache_size and synchronous pragmas sounds like a better way to
trade reliability against speed/memory use. e.g.

table_memory = 100000000 # bytes
cur.execute("pragma cache_size = %d;" % (table_memory / 1500))

....

cur.execute("pragma synchronous = off;")
# do lots of stuff
cur.execute("pragma synchronous = full;")

for more on this, see: http://www.sqlite.org/pragma.html

</F>

 

[Ranjitha]

using the cache_size and synchronous pragmas sounds like a better way to
trade reliability against speed/memory use. e.g.

table_memory = 100000000 # bytes
cur.execute("pragma cache_size = %d;" % (table_memory / 1500))

...

cur.execute("pragma synchronous = off;")
# do lots of stuff
cur.execute("pragma synchronous = full;")

for more on this, see: http://www.sqlite.org/pragma.html

</F>

Thanks a lot for the help but could you please elaborate on this. I'm
finding it difficult to follow the link you sent me. The jargon seems
all too new for me.

Thanks,
Ranjitha

 

[John Machin]

Thanks a lot for the help but could you please elaborate on this. I'm
finding it difficult to follow the link you sent me. The jargon seems
all too new for me.

For a start, you don't need to read the whole page, just the sections
on "cache_size" [the more memory in the cache, the faster it will run
-- up to a point, which you could experiment with] and "synchronous"
[off = faster/dangerous, on = slower/safer].

It would help greatly if you said which particular words or phrases you
don't understand, plus give an assurance that you have made *some*
effort to help yourself e.g. googled the puzzling words/phrases ...

Have you actually tried doing what Fredrik suggested?