How to integrate spatialite in pyramid?

[Michael Lane]

I would like to use spatialite in my project but I don't find the right way to do that. Does anybody have already done that and can give me an example?

my environment:

Windows 10:
Python version: 3.6.8
Local database: sqlite (for spatialite)
Remote database: postgresql

I find a lot of exemples where spatialite is set in the module parameter for the engine in create_engine using lib like libsqlite3, mod_spatialite, pysqlite3 in my case I use spatialiteinstall with pip (package define in setup.py)

My problem is then I don't have the control of create_engine, in the engine comming from settings in ini so I think that I have to pass the setting by the ini file but I did'nt get it. I didn't find any good example of code using same combination environment as mine (windows, pyramid, spatialite).

There are the settings of ini file:

pyramid.reload_templates = true
pyramid.reload_assets = true
pyramid.debug_authorization = true
pyramid.debug_notfound = false
pyramid.debug_routematch = true
pyramid.default_locale_name = en
pyramid.includes =
    pyramid_debugtoolbar
    pyramid_tm

sqlalchemy.url = sqlite:///%(here)s/risc.sqlite

Code where the engine is create:

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import configure_mappers
import zope.sqlalchemy


# run configure_mappers after defining all of the models to ensure
# all relationships can be setup
configure_mappers()


def get_engine(settings, prefix='sqlalchemy.'):
    return engine_from_config(settings, prefix)


def get_session_factory(engine):
    factory = sessionmaker()
    factory.configure(bind=engine)
    return factory


def get_tm_session(session_factory, transaction_manager):
    dbsession = session_factory()
    zope.sqlalchemy.register(dbsession, transaction_manager=transaction_manager)
    return dbsession


def includeme(config):
    settings = config.get_settings()
    settings['tm.manager_hook'] = 'pyramid_tm.explicit_manager'

    # use pyramid_tm to hook the transaction lifecycle to the request
    config.include('pyramid_tm')

    # use pyramid_retry to retry a request when transient exceptions occur
    config.include('pyramid_retry')

    session_factory = get_session_factory(get_engine(settings))
    config.registry['dbsession_factory'] = session_factory

    # make request.dbsession available for use in Pyramid
    config.add_request_method(
        # r.tm is the transaction manager used by pyramid_tm
    lambda r: get_tm_session(session_factory, r.tm),
    'dbsession',
    reify=True
)

And the settings module of setup.py




requires = [
    'bcrypt',
    'docutils',
    'plaster_pastedeploy',
    'pyramid',
    'pyramid_debugtoolbar',
    'pyramid_retry',
    'pyramid_tm',
    'sqlalchemy',
    'transaction',
    'zope.sqlalchemy',
    'waitress',
    'sphinx',
    'flake8',
    'pyLint',
    'pg8000',
    'geoalchemy2',
    'spatialite'
]

I'm pretty sure than I miss a little something, but I'm not able to find it my way. I little hint will be appreciated on this one.

Thanks

[andi]

 

 

Hi Michael,

 

I never used spatialite. But from looking at your example code, you seem to create a “simple sqlite” connection, whereas somehow this extension needs to be loaded. From what a quick google search revealed, there seems not to exist some special connection string, that does the magic to load this extension.

 

Looking here: https://datasette.readthedocs.io/en/stable/spatialite.html#spatial-indexing-latitude-longitude-columns it looks like you’re missing

 

conn.enable_load_extension(True)

conn.load_extension('/usr/local/lib/mod_spatialite.dylib')

 

which I would guess belongs into your `get_tm_session` function. Try debugging into that and see if it works on `dbsession` or some property on it.

 

Hope that helps, Andi

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discus...@googlegroups.com.
To post to this group, send email to pylons-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/15188080-e8eb-4e72-bf32-14dadf302909%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

1. A quick online search shows this tutorial on spatialiate with sqlalchemy:  https://geoalchemy-2.readthedocs.io/en/latest/spatialite_tutorial.html

the author is using a SqlAlchemy event to load the spatialate extension into the engine/connection/pool.

2. You are going to have a small problem with this bit:

 Local database: sqlite (for spatialite)

 Remote database: postgresql

The Pyramid cookiecutter and tutorial is designed for a single database connection.  Here, you want two database connections .

An easy way to do that is to make a second version of the database create-engine, and also duplicate the bit about adding the database to the request.  
It's not particularly hard, but it is a fair amount of work and one of the more advanced use-cases.  IIRC, there is another approach where you can configure SqlAlchemy to shard certain tables onto a first connection, and shard the other tables onto a second connection.  That approach is  a bit more difficult.

There may be a tutorial on that somewhere.

[Michael Lane]

Thanks for the answer,

I saw this page before but I don't know what to do with it! When I check the code in connection.py of spatialite, I see:

'mod_spatialite',  # Ubuntu
'mod_spatialite.so',  # Ubuntu
'mod_spatialite.dylib',  # macOS

But no definition for windows. I will make few test to make it works in my tm_session.

Thanks for the hint!

Have a nice day!

To unsubscribe from this group and stop receiving emails from it, send an email to pylons-...@googlegroups.com.

[Michael Lane]

Hi,

thanks for the answer. I saw this tutorial many time on my google research, but I don't know how to integrate this in pyramid and specifically for Windows.

I probably not clear on that one, but I'm going to have only one database at the time. The sqlite (spatialite) is used for my local development and for unit test. Postgresql will be used for production environment.

[Michael Lane]

Try to find a way to import the dll but it always fails on the load_extension

def load_spatialite(dbapi_conn, connection_record):
    dbapi_conn.enable_load_extension(True)
    dbapi_conn.load_extension('C:\\Scripts\\mod_spatialite.dll')

[Michael Lane]

Even though I put the file in a directory define in %PATH% or try with/without full path, with/without file extension, with libspatialite-4.dll or using excute with all combination mentionned before...

 dbapi_conn.execute("SELECT load_extension('mod_spatialite.dll')")