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.
