Thread: [SQLObject] Temporary databases / Closing the :memory: database connection
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Maciej (M. B. <ma...@op...> - 2013-10-16 08:42:42
|
Hi guys, I'd like to use an in-memory sqlite database for testing, and I have a problem where I don't seem to be able to let go of a database which was once created. Here's a test that shows what I mean: class Foo(sqlobject.SQLObject): bar = sqlobject.UnicodeCol(length=250, unique=True) db_uri = 'sqlite:/:memory:' conn = sqlobject.connectionForURI(db_uri) sqlobject.sqlhub.processConnection = conn Foo.createTable() # All is good so far. Now let's drop the database. conn.close() del conn # From http://www.sqlite.org/inmemorydb.html: # "The database is automatically deleted and memory is reclaimed when the last connection to the database closes." # Let's create a fresh in-memory database. conn = sqlobject.connectionForURI(db_uri) sqlobject.sqlhub.processConnection = conn Foo.createTable() # Here, an exception is thrown: sqlobject.dberrors.OperationalError: table foo already exists I must have somehow gotten a connection to the same in-memory database that was created on the first call to connectionForURI(). After looking at dbconnection.py, I came up with an invasive method: sqlobject.dbconnection.TheURIOpener.cachedURIs = {} conn = sqlobject.connectionForURI(db_uri) sqlobject.sqlhub.processConnection = conn Foo.createTable() It does what I need, but it doesn't seem like it's what SQLObject developers intended. Do you have any recommendations? Maciej |
From: Oleg B. <ph...@ph...> - 2013-10-16 13:19:36
|
Hi! On Wed, Oct 16, 2013 at 09:41:54AM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > Hi guys, > > I'd like to use an in-memory sqlite database for testing, and I have a > problem where I don't seem to be able to let go of a database which was > once created. Here's a test that shows what I mean: > > class Foo(sqlobject.SQLObject): > bar = sqlobject.UnicodeCol(length=250, unique=True) > > db_uri = 'sqlite:/:memory:' > conn = sqlobject.connectionForURI(db_uri) > sqlobject.sqlhub.processConnection = conn > Foo.createTable() > # All is good so far. Now let's drop the database. > conn.close() > del conn > # From http://www.sqlite.org/inmemorydb.html: > # "The database is automatically deleted and memory is reclaimed when the > last connection to the database closes." > # Let's create a fresh in-memory database. > conn = sqlobject.connectionForURI(db_uri) > sqlobject.sqlhub.processConnection = conn > Foo.createTable() > # Here, an exception is thrown: > sqlobject.dberrors.OperationalError: table foo already exists > > I must have somehow gotten a connection to the same in-memory database that > was created on the first call to connectionForURI(). After looking at Yes. Actually, even worse than that - connectionForURI caches SQLObject's connections by URIs (you get the same SQLObject's connection with the same URI) and SQLiteConnection caches a DBAPI connection to the in-memory DB. > dbconnection.py, I came up with an invasive method: > > sqlobject.dbconnection.TheURIOpener.cachedURIs = {} > conn = sqlobject.connectionForURI(db_uri) > sqlobject.sqlhub.processConnection = conn > Foo.createTable() > > It does what I need, but it doesn't seem like it's what SQLObject > developers intended. Do you have any recommendations? I don't. Do you want to propose an API? Or better a patch? Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Maciej (M. B. <ma...@op...> - 2013-10-16 21:59:12
|
2013/10/16 Oleg Broytman <ph...@ph...> > > It does what I need, but it doesn't seem like it's what SQLObject > > developers intended. Do you have any recommendations? > > I don't. Do you want to propose an API? Or better a patch? Hey, thanks for the reply. I'm looking at the code right now, I need to look more carefully at the current structure before I can suggest an API or a patch. In general, I can see 4 possibilities: - when creating a connection, add an option to not cache that connection - add an option to clear / expire all the connections in the current cache - add an option to the close() method so it causes the connection (and the URI) to expire from the cache - there already is special handling for in-memory databases, so maybe add a little more special handling for them; for example free the memory on close(). This could potentially break existing applications, does SQLObject make any guarantees about in-memory databases? Maciej |
From: Oleg B. <ph...@ph...> - 2013-10-16 22:25:52
|
On Wed, Oct 16, 2013 at 10:58:24PM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > 2013/10/16 Oleg Broytman <ph...@ph...> > > > It does what I need, but it doesn't seem like it's what SQLObject > > > developers intended. Do you have any recommendations? > > > > I don't. Do you want to propose an API? Or better a patch? > > Hey, thanks for the reply. I'm looking at the code right now, I need > to look more carefully at the current structure before I can suggest > an API or a patch. In general, I can see 4 possibilities: > > - when creating a connection, add an option to not cache that connection That would be easy, just one additional parameter in connectionForURI: cache=True (or useCache or something like that). The approach could lead to problems, though. Every SQLObject's connection maintain its own cache of retrieved rows, so different connections for the same URI will have different caches, so the same row could appear in a few caches; that would lead to unsynchronized read/write operations. > - add an option to clear / expire all the connections in the current cache What gives? Usually if there are a few connections with different URIs the user needs all of them. > - add an option to the close() method so it causes the connection (and > the URI) to expire from the cache Not sure it could be implemented reliably. Connection doesn't know its own DB URI, and reconstructing it could give an equivalent but different URI. > - there already is special handling for in-memory databases, so > maybe add a little more special handling for them; for example free > the memory on close(). That's a good approach; requires testing. > This could potentially break existing > applications, does SQLObject make any guarantees about in-memory > databases? Not much. I don't know anyone who uses the memory database for any purpose other than testing. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Petr J. <pet...@tp...> - 2013-10-17 15:21:05
|
On 17 October 2013 00:25, Oleg Broytman <ph...@ph...> wrote: > Not much. I don't know anyone who uses the memory database for any > purpose other than testing. > Hi, maybe off topic, but I am using in memory database in production environment. The main purpose is to reduce writing to CF card. Petr |
From: Oleg B. <ph...@ph...> - 2013-10-17 15:27:03
|
On Thu, Oct 17, 2013 at 05:20:57PM +0200, Petr Jake?? <pet...@tp...> wrote: > On 17 October 2013 00:25, Oleg Broytman <ph...@ph...> wrote: > > > Not much. I don't know anyone who uses the memory database for any > > purpose other than testing. > > Hi, maybe off topic, but I am using in memory database in production > environment. > The main purpose is to reduce writing to CF card. Do you close and reopen the memory database in you program(s)? Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Petr J. <pet...@tp...> - 2013-10-17 16:24:10
|
On 17 October 2013 17:26, Oleg Broytman <ph...@ph...> wrote: > Do you close and reopen the memory database in you program(s)? Nope. I am only creating table once at the start of the program: connectionInMemory = connectionForURI('sqlite:/:memory:') class TextyServisnihoDispleje(SQLObject): _connection = connectionInMemory level=StringCol(length=1, default=None) text=UnicodeCol(length=20, default=None) cislovatRadek=EnumCol(enumValues=('Y','N'), default='Y') akce=StringCol(length=100, default=None) predek=ForeignKey("TextyServisnihoDispleje", default=None) potomci=MultipleJoin("TextyServisnihoDispleje", joinColumn="predek_id") TextyServisnihoDispleje.createTable() |
From: Oleg B. <ph...@ph...> - 2013-10-17 16:29:03
|
On Thu, Oct 17, 2013 at 06:24:02PM +0200, Petr Jake?? <pet...@tp...> wrote: > On 17 October 2013 17:26, Oleg Broytman <ph...@ph...> wrote: > > Do you close and reopen the memory database in you program(s)? > > Nope. I am only creating table once at the start of the program: I see. Thank you! Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2013-10-17 17:46:29
Attachments:
sqlite-memorydb.patch
|
Hi! On Wed, Oct 16, 2013 at 10:58:24PM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > - there already is special handling for in-memory databases, so > maybe add a little more special handling for them; for example free > the memory on close(). The patch to test is attached. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Maciej (M. B. <ma...@op...> - 2013-10-17 21:02:41
|
2013/10/17 Oleg Broytman <ph...@ph...> > > Hi! > > On Wed, Oct 16, 2013 at 10:58:24PM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > > - there already is special handling for in-memory databases, so > > maybe add a little more special handling for them; for example free > > the memory on close(). > > The patch to test is attached. Thanks! I applied the patch and ran the the following test: import sqlobject class Foo(sqlobject.SQLObject): bar = sqlobject.UnicodeCol(length=250, unique=True) db_uri = 'sqlite:/:memory:?cache=false' while True: sqlobject.sqlhub.processConnection = sqlobject.connectionForURI(db_uri) Foo.createTable() sqlobject.sqlhub.processConnection.close() sqlobject.sqlhub.processConnection = None sqlobject.dbconnection.TheURIOpener.cachedURIs = {} The process' memory kept growing. It started at around 30MB and was steadily raising up to 300MB at which point I stopped the process. I didn't do any more digging yet. Maciej |
From: Oleg B. <ph...@ph...> - 2013-10-19 16:10:28
|
Hi! On Thu, Oct 17, 2013 at 10:01:53PM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > import sqlobject > > class Foo(sqlobject.SQLObject): > bar = sqlobject.UnicodeCol(length=250, unique=True) > > db_uri = 'sqlite:/:memory:?cache=false' > while True: > sqlobject.sqlhub.processConnection = sqlobject.connectionForURI(db_uri) > Foo.createTable() > sqlobject.sqlhub.processConnection.close() > sqlobject.sqlhub.processConnection = None > sqlobject.dbconnection.TheURIOpener.cachedURIs = {} > > The process' memory kept growing. It started at around 30MB and was > steadily raising up to 300MB at which point I stopped the process. I > didn't do any more digging yet. The program eats my memory very quickly and fails with MemoryError in a few seconds. On Sat, Oct 19, 2013 at 11:44:03AM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > On Fri, Oct 18, 2013 at 01:14:46AM +0400, Oleg Broytman wrote: > > It would be interesting to test if the problem lies in SQLite, > > PySQLite or SQLObject. > > Looks like it's not SQLite, the following runs with stable memory use at > about 7MB, I ran it for about an hour: > > import sqlite3 > while True: > conn = sqlite3.connect(":memory:") > c = conn.cursor() > c.execute('CREATE TABLE foo (bar TEXT);') > conn.commit() > conn.close() This one works for me, eats 9M at the start but doesn't grow. And this one also works perfectly: import sqlobject class Foo(sqlobject.SQLObject): bar = sqlobject.UnicodeCol(length=250, unique=True) db_uri = "sqlite:/:memory:" while True: Foo.setConnection(db_uri) Foo.createTable() Foo._connection.close() It takes 15M at startup but doesn't grow. Very puzzling! Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2013-10-17 21:15:04
|
On Thu, Oct 17, 2013 at 10:01:53PM +0100, "Maciej (Matchek) Blizi??ski" <ma...@op...> wrote: > 2013/10/17 Oleg Broytman <ph...@ph...> > import sqlobject > > class Foo(sqlobject.SQLObject): > bar = sqlobject.UnicodeCol(length=250, unique=True) > > db_uri = 'sqlite:/:memory:?cache=false' > while True: > sqlobject.sqlhub.processConnection = sqlobject.connectionForURI(db_uri) > Foo.createTable() > sqlobject.sqlhub.processConnection.close() > sqlobject.sqlhub.processConnection = None > sqlobject.dbconnection.TheURIOpener.cachedURIs = {} > > The process' memory kept growing. It started at around 30MB and was > steadily raising up to 300MB It would be interesting to test if the problem lies in SQLite, PySQLite or SQLObject. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Maciej (M. B. <ma...@op...> - 2013-10-19 10:44:51
|
On Fri, Oct 18, 2013 at 01:14:46AM +0400, Oleg Broytman wrote: > It would be interesting to test if the problem lies in SQLite, > PySQLite or SQLObject. Looks like it's not SQLite, the following runs with stable memory use at about 7MB, I ran it for about an hour: import sqlite3 while True: conn = sqlite3.connect(":memory:") c = conn.cursor() c.execute('CREATE TABLE foo (bar TEXT);') conn.commit() conn.close() At least we know that it can work. I guess the next step will be to use a memory profiler to figure out what still holds the references but I haven't gotten around to it yet. Maciej |