The engine doesn't use a connection pool by default when you use a SQLite file
database. Each usage of the Engine will grant a brand new sqlite connection,
with no relationship to any previous SQLite connections. An Engine is a
source of many connections, not a connection itself.
So you need to establish the state you need on each Connection as it is created
using an event:
from sqlalchemy import event, create_engine
engine = create_engine("sqlite:///file.db")
@event.listens_for(engine, "connect")
def connect(dbapi_connection, connection_rec):
dbapi_connection.enable_load_extension(True)
dbapi_connection.do_sqlite_things()
the above event handler will be called whenever SQLAlchemy calls upon pysqlite
to create a new connection.
On Mar 16, 2012, at 2:45 PM, Jan wrote:
> Dear all,
>
> I'm fighting with sqlite/spatialite since some days.
> I want to use GeoAlchemy with sqlite/spatialite - but I think my
> problem starts at SQLAlchemy.
>
> I can connect/create sqlite database without any problem.
> Furthermore, I'm able to connect to a sqlite/spatialite database and
> load spatialite extension directly with pysqlite2 (see example below).
> Moreover, I can run the untitest for spatialite of GeoAlchemy
> successfully.
>
> But, this untitest uses only a memory database.
> When I change this to a database stored on disk (an already existing
> as well a non-extisting one) I'm getting an error (on Windows as well
> as Linux [Ubuntu]) at
> connection.enable_load_extension(True).
> It tells me: pysqlite2.dbapi2.ProgrammingError: Cannot operate on a
> closed database.
>
> I tested relative and absolute paths (on both, Windows and Ubuntu) but
> without any success.
>
> I know that the error message is produced by pysqlite2, but, as there
> is no problem in extension loading using the pysqlite2 library
> directly, I assume that the problem should be found in sqlalchemy and
> its connection/engine initializing.
> But maybe there is no real problem and I only missed something (e.g.
> opening some kind of connection to the database).
>
> Any help would be appreciated.
> Many thanks in advance.
>
> Best wishes,
>
> Jan
>
> ------------------------------------------------------------------------------------------------------------------------------
> Here is a small example using sqlalchemy:
>
> from sqlalchemy import (create_engine, MetaData)
> from pysqlite2 import dbapi2 as sqlite
>
> # this is the line for using the memory database (runs without any
> error)
> #engine = create_engine('sqlite://', module=sqlite, echo=True)
> # this is for a relative path on Windows and Ubuntu (ends up in an
> error at enable_load_extension)
> #engine = create_engine('sqlite:///testdb.sqlite', module=sqlite,
> echo=True)
> # this is for an absolute path on Windows (ends up in an error at
> enable_load_extension)
> #engine = create_engine('sqlite:///C:\\testdb.sqlite', module=sqlite,
> echo=True)
> # this is for an absolute path on Ubuntu (ends up in an error at
> enable_load_extension)
> engine = create_engine('sqlite:////usr/home/jthiele/testdb.sqlite',
> module=sqlite, echo=True)
>
> connection = engine.raw_connection().connection
> # the following line end up in an error
> (pysqlite2.dbapi2.ProgrammingError: Cannot operate on a closed
> database.)
> # when not using the memory database:
> connection.enable_load_extension(True)
>
> # this will only be reached when using the memory database
> metadata = MetaData(engine)
> session = sessionmaker(bind=engine)()
> # this for Windows:
> #session.execute("select load_extension('libspatialite-1.dll')")
> # this for Ubuntu:
> session.execute("select load_extension('/usr/lib/libspatialite.so')")
> session.execute("SELECT InitSpatialMetaData()")
> connection.enable_load_extension(False)
> ...
>
> ---------------------------------------------------------------------------------------------------------
> Here is the working example with pysqlite2 (directly):
>
> from pysqlite2 import dbapi2 as sqlite
> DB = sqlite.connect('C:\\testdb.sqlite')
> DB.enable_load_extension(True)
> DB.execute('SELECT load_extension("libspatialite-2.dll")')
> DBCursor = DB.cursor()
> DBCursor.execute("SELECT * FROM LK_Goettingen_DLM25")
> for row in DBCursor:
> print row
> DBCursor.close()
> DB.close()
>
> ------------------------------------------------------------------------------------------------------------
> These are the versions used:
> On Windows XP:
> Python 2.7.2
> pysqlite 2.6.3
> sqlalchemy 0.7.5
> sqlite 3.7.10
> spatialite 2.3.1
> On Ubuntu 11.10:
> Python 2.7.2
> pysqlite 2.6.3
> sqlalchemy 0.7.5
> sqlite 3.7.7
> spatialite 2.4.0
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
