Re: Seeking advice on direction with database connectivity from Pyramid

[Niall]

The first post was meant to be a draft and posted by mistake. Here is, hopefully, a clearer post.

I’m new to Pyramid. I’ve used Python for a few months. I created a Python application on Linux to maintain an Oracle database using weekly data feeds from a vendor. To get that done, one of the things I did was to create a customized database wrapper class using cx_Oracle. I had specific requirements for maintaining history in the DB. All Oracle access goes through the methods in this wrapper.  I now want to use Pyramid to create a simple reporting browser interface to the Oracle DB. To allow me the greatest flexibility, I’d like to use the wrapper I already have to get to the data on Oracle instead of Alchemy (or possibly with it, I don’t know).

I’ve considered importing my wrapper in my views.py __init__ method but that seems to get executed with every browser submit. Can anyone suggest how I might create a persistent connection to Oracle that I can use over and over from my reporting application which uses my wrapper class? I’m finding Pyramid a bit opaque. I’m never sure what’s happening behind the scenes but I’m willing to operate on trust until I get the swing of it. I need the benefit of the automatic authorization/ authentication, session mgt and login.

What I’m really looking for here is a good approach from experienced Pyramid users before going down the wrong track.

On Monday, July 11, 2016 at 12:43:41 PM UTC-4, Niall wrote:

I'm trying to use Pyramid to create a simple reporting web interface to an existing Oracle database. 

In creating/maintaining this database, I created a wrapper in the form of a python class, which has all the methods I need to put data into the db and query the data. I've standardized on Python dictionaries for all result sets and would like to keep it that way for the web interface.

I now want to use this db wrapper class to get to the database from the Pyramid app. I would like the class to persist while the web app runs. I've considered accessing the wrapper from my __init__.py program but I'm unclear how my views.py class methods will be able to access it then. I considered instantiating the wrapper class in the __init__ method of my views.py program but noticed that each browser submit causes a new db connection. I'd like to use the same connection, or connection pooling.

Could someone kindly advise on a correct approach. I'm relatively new to Pyramid and started using Python about a year back for batch business processing.

Many thanks folks.

[Jonathan Vanasco]

The easiest thing is to look how various integrations are done in the Pyramid scaffolds and some open source projects.

The most popular approach is to create a connection pool that is available to all requests; and each request will create/manage/close a connection as-needed.

The default implementation of sqlalchemy in the scaffolds does this by using a reified request property.  Touching `request.dbsession` creates the connection on-demand and re-uses it throughout the life of the request. 

Packages like pyramid_tm will wrap the request lifecycle in a tween, then issue a commit or rollback as necessary.

Without knowing much of your setup, i think you could probably do something like this to edit less of your code:

1. setup some factory to create a connection pool shared across requests

2. use a reified request method to grab a connection for the dbsession as needed.
3. either: explicitly pass the dbsession into each function on your db layer, or take a class-based approach where you have an instance of your library's object and that manages a single dbsession from the pool

There are a lot of ways to handle this though.  It sounds like you have so-far taken a non-standard approach.  As others chime in, you'll have to weigh the benefits of more standard/maintainable code vs faster development times.

[Mike Orr]

On Mon, Jul 11, 2016 at 10:44 AM, Niall <niall...@gmail.com> wrote:
> The first post was meant to be a draft and posted by mistake. Here is,
> hopefully, a clearer post.
>
>
> I’m new to Pyramid. I’ve used Python for a few months. I created a Python
> application on Linux to maintain an Oracle database using weekly data feeds
> from a vendor. To get that done, one of the things I did was to create a
> customized database wrapper class using cx_Oracle. I had specific
> requirements for maintaining history in the DB. All Oracle access goes
> through the methods in this wrapper. I now want to use Pyramid to create a
> simple reporting browser interface to the Oracle DB. To allow me the
> greatest flexibility, I’d like to use the wrapper I already have to get to
> the data on Oracle instead of Alchemy (or possibly with it, I don’t know).
>
> I’ve considered importing my wrapper in my views.py __init__ method but that
> seems to get executed with every browser submit. Can anyone suggest how I
> might create a persistent connection to Oracle that I can use over and over
> from my reporting application which uses my wrapper class? I’m finding
> Pyramid a bit opaque. I’m never sure what’s happening behind the scenes but
> I’m willing to operate on trust until I get the swing of it. I need the
> benefit of the automatic authorization/ authentication, session mgt and
> login.
>
> What I’m really looking for here is a good approach from experienced Pyramid
> users before going down the wrong track.

SQLAlchemy has several different levels you can use:

1. Connection pool. This simply keeps idle connections open for reuse.

2. Engine: contains the parameters for connecting and a connection pool.

3. Connection: a wrapper for a DB-API connection. You can execute text
SQL statements; e.g., conn.execute("select foo from bar")

4. Transaction: a wrapper around the database's BEGIN/COMMIT/ROLLBACK.

5. The SQL builder. Assemble queries pythonically. Can be used with 2, 3, or 4.

6. The ORM and SQLAlchemy session. Like ActiveRecord, it automatically
converts row records to your Python class instances, and converts
attribute writes to SQL writes. The session can wrap 2, 3, or 4. You
can execute SQL directly in the session's transaction with
'session.execute(...)'. The session always uses a transaction to talk
to the database.

7. Pyramid's standard SQLAlchemy configuration. This is what jvanasco
was talking about. You make a global engine (#2) and put it in the
Pyramid registry. Then you make a Request property that creates a
SQLAlchemy session on demand (#6). Thus you can get a db session in
your view code. The scaffold also registers the session with
'pyramid_tm': this is a transaction manager. When request processing
ends, the TM commits the transaction; or if a non-4xx exception
occurred it rolls it back. You can also register multiple db and
non-db transactions and the TM will commit or roll back all of them.

8. If you don't want to use the ORM, you can make a request property
that returns an engine or connection (#2, #3, #4, #5). I don't know
whether you could register that with Pyramid's transaction manager;
you'd probably have to use a 'zope.sqlalchemy' class or write your own
adapter. But you don't have to use the transaction manager.

So with your Oracle wrapper class, the standard approach would be #7:
use a SQLAlchemy session and rewrite your code for SQLAlchemy's ORM or
SQL builder.

But if you don't want to do that, 'request.sa_session.bind' is the
active connection, and it has an attribute with the DB-API connection.
You could pass that to your Oracle class. But it's a request-local
variable so you'd want to instantiate the Oracle class each request.
Note that this may confuse the Session's transaction and 'pyramid_tm',
because they don't know you're executing SQL commands, so they may
think no session exists yet or nothing has happened in it.

If you don't want to use the session or SQL builder at all, then you
can drop to a lower level. #4 would give you a connection and a
transaction, and from that you can get the DB-API connection within
the transaction.

Or you can go all the way down to #1. In this case SQLAlchemy is only
providing a connection pool over the DB-API. There's no SQLAlchemy
engine or connection, and SQLAlchemy's Oracle-specific code is
bypassed. This is described in:

http://docs.sqlalchemy.org/en/rel_1_0/core/pooling.html#pooling-plain-db-api-connections

You can make a Request property that returns a connection based on
this. You'll have to manage any transactions yourself. The biggest
thing is don't share a transaction between requests, and make sure the
transaction is committed or rolled back by the end of the request. I
thought there was a RequestEnding type event for this or a
'request.add_finalizer(callback)' but I don't see it. Maybe you could
find or make a transaction manager adapter for your connection.

--
Mike Orr <slugg...@gmail.com>

[Mike Orr]

On Mon, Jul 11, 2016 at 10:11 PM, Mike Orr <slugg...@gmail.com> wrote:
> ends, the TM commits the transaction; or if a non-4xx exception
> occurred it rolls it back.

I guess it's really non-2xx or non-3xx, which are success cases. The
non-4xx has more to do with the debug toolbar and interactive
traceback.

[Jonathan Vanasco]

On Tuesday, July 12, 2016 at 1:11:30 AM UTC-4, Mike Orr wrote:

8. If you don't want to use the ORM, you can make a request property
that returns an engine or connection (#2, #3, #4, #5). I don't know
whether you could register that with Pyramid's transaction manager;
you'd probably have to use a 'zope.sqlalchemy' class or write your own
adapter. But you don't have to use the transaction manager.

as a variation on this... If you setup the ORM you can work directly with the dbSession's connection (using core, the underlying cursor, etc), and all that work will happen within the pyramid_tm transaction

however, sqlalchemy won't know that you did anything since you bypassed it.  you need to import `mark_changed` from zope.sqlalchemy and then do `mark_changed(dbSession)`.  otherwise the default rollback will issue instead of a commit.

[Niall]

Thanks for taking the time to respond. If I'm understanding what you're both saying then the best approach may be to abandon using my customized db wrapper and go back to using standard sqlalchemy. Since i have not really started the reporting app yet, that's certainly doable. One of the reasons I was opting for the custom solution was that it would give me a separate place to put all the packaging of the data to be returned to the web app. I'm dealing with complex data where I will normally need to touch 5 to 8 tables to put all the pieces needed together in a query. My db structure is granular. Instead of using things like Oracle stored procs and/or complex views, which would anchor me in future to Oracle, I want to keep that logic in the application, outside the database. I originally wrote the db load application using sqlite on a Windows machine and then ported it to Oracle on Linux, simply by re-writing the db wrapper. I'd like to continue in that vein so that, should the company decide to move to another DB in the future, it won't be an issue for my application. So now, assuming I go with pure sqlAlchemy, is there a way I can deploy a python class between my web app's view callables and the database to house the functionality I was going to put in my custom wrapper?

As I said before, at this point I'm simply trying to decide the route to take, not the actual detailed specifics of the solution.

One other thing occurs to me:- Since Pyramid has all this routing infrastructure built in, would another solution possibly be to build a separate app which would service database requests and create a layer (where I could put my custom data packaging)? My reporting app could then route db requests using urls to this separate database servicing app?

I'm sorry if this is not 100% clear. I'm trying to ensure I have a layer in my design where business logic is implemented with as little technical clutter as possible.

[Paul Everitt]

SQLAlchemy and Pyramid have a few trillion ways to scratch the “python class between my view and the database”.

I like Michael’s pyramid_services approach:

  https://github.com/mmerickel/pyramid_services

If you want less, just have SQLAlchemy models which:

- Have custom property accessors to transform columns however you’d like

- Have @staticmethod query functions

- Then, make a route factory that passes in correct results into the view as the “context”.

—Paul

--
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/24a9bf2e-b228-4864-a147-958808b9807b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

On Wednesday, July 13, 2016 at 11:03:04 AM UTC-4, Niall wrote:

If I'm understanding what you're both saying then the best approach may be to abandon using my customized db wrapper and go back to using standard sqlalchemy

Not really.

I'm suggesting that it may be *quickest* to use SqlAlchemy (or model a service after it) to handle the database connections.  Between the zope.sqlalchemy package, pyramid_tm, and the sqlalchemy scaffold you will have a system that connects to oracle and wraps every request in a transaction, and does it all using a proven connection pool.

You can then obtain an underlying connection from SqlAlchemy and then alter your existing code to use that connection, instead of connecting to oracle yourself.

Please remember: you haven't shared any of your code for this "db wrapper" so we don't know what it's doing.

[Mike Orr]

On Wed, Jul 13, 2016 at 8:03 AM, Niall <niall...@gmail.com> wrote:
> Thanks for taking the time to respond. If I'm understanding what you're both
> saying then the best approach may be to abandon using my customized db
> wrapper and go back to using standard sqlalchemy. Since i have not really
> started the reporting app yet, that's certainly doable. One of the reasons I
> was opting for the custom solution was that it would give me a separate
> place to put all the packaging of the data to be returned to the web app.
> I'm dealing with complex data where I will normally need to touch 5 to 8
> tables to put all the pieces needed together in a query. My db structure is
> granular. Instead of using things like Oracle stored procs and/or complex
> views, which would anchor me in future to Oracle, I want to keep that logic
> in the application, outside the database. I originally wrote the db load
> application using sqlite on a Windows machine and then ported it to Oracle
> on Linux, simply by re-writing the db wrapper. I'd like to continue in that
> vein so that, should the company decide to move to another DB in the future,
> it won't be an issue for my application.

If the ability to switch database types is more important to you than
your Oracle code, then rewriting it in SQLAlchemy would be a good
choice. If you're ever going to do it, you might as well do it now
when you're overhauling your application anyway.

But as a speaker at a PostgreSQL conference said, it's also worth
taking advantage of your database's unique features, because if you
use it in the least-common-denominator fashion you're not getting the
most out of it. Which option you choose -- a database-neutral approach
or a database-specific approach -- depends on the application. In one
application I use PostgreSQL and have started using its unique types
and full-text search and may use PostGIS. Another application has to
work both as a website and a desktop application (the wxPython wrapper
spwans a Pylons application and an embedded browser that uses it), so
we initially designed it for both SQLite (desktop) and PostgreSQL
(webserver). But at one point I got foreign key errors from PostgreSQL
because it's stricter than SQLite, and rather than unravel them we
just went with SQLite exclusively because it's performing fine on the
webserver.

> One other thing occurs to me:- Since Pyramid has all this routing
> infrastructure built in, would another solution possibly be to build a

> separate app which would service database requests and create a layer > So now, assuming I go with pure

> sqlAlchemy, is there a way I can deploy a python class between my web app's
> view callables and the database to house the functionality I was going to
> put in my custom wrapper?
> As I said before, at this point I'm simply trying to decide the route to
> take, not the actual detailed specifics of the solution.

> (where
> I could put my custom data packaging)? My reporting app could then route db
> requests using urls to this separate database servicing app?

My current structure is to have small view callables that handle the
input validation and template variables and Pyramid-specific stuff,
and a separate library tree for high-level queries and other logic
that's not closely tied to Pyramid our the output format. That way my
business logic is separate from both the views, the models, the
templates, and Pyramid, so if I wanted to use it outside the Pyramid
application or port it to another framework it would be clear which
code would be relatively easy and which difficult.

Sometimes I allow a little Pyramid into the library modules, such as
the request when I need a lot of things out of it or I need to
generate URLs, or the HTTP exceptions because it's silly to make a
custom exception just to catch it and raise an HTTPException (and unit
tests can deal with HTTP exceptions just as easily as any other
exception), and 'pyramid.compat' for Python 2/3 compatible stuff.

That would give you an applcation with view modules calling library
modules. You can go a step further with 'pyramid_services', where you
register service classes and then ask for them, but when I tried it it
was overkill. It has a niche if you might sometimes want to use an
alternate interpretation, then you can ask Pyramid, "Give me an object
that does X (an interface)". But if you have only one implementation
and no likelyhood of a second one, why not just import it directly?

I have actually taken a step in the opposite direction and gone to
function-based views and library functions, based on a talk at PyCon
that said that functions and modules are better organization for code
than classes, and more readable for future maintainers. So Iried it in
a Pyramid app and it is pretty readable: you don't have to look
closely for subtle 'self' references and inherited dependencies. The
talk argued that classes are for data, not code, and if you have a
complex set of state data then put it in an object and pass it between
your functions. I don't go as far as that, and passing a state object
as the first argument reminds me of the horrors of C. And it doesn't
work for some things; for instance, I have a class to do model field
updating. You initialize it with a model and call a method to update a
field by name. It checks whether the value has changed and if so,
updates the model attribute and adds it to a set of field names. Then
at the end I have a set of which fields have changed, which I can put
in an audit record. You can't reduce that class to functions without
having to carry the set of changed fields around, which is excessively
verbose. So that kind of thing I keep a class.

Another approach would be a separate data-server application, so one
application would be calling another, perhaps as an HTTP service.
That may be useful in specialized cases where you really want to keep
the data code in a separate process, either for some structural reason
or to serve multilingual clients. But as a general pattern for a
Pyramid application it sounds like more trouble than it's worth. When
you get to the integration test section in the Pyramid manual it says
that it's often better to create a unit test that sets up a minimal
Configurator rather than loading the entire application if you don't
need it. It's that kind of thing. If what you immediately need is a
logic function, it's better to call that directly than to wrap it in a
view which then has to parse the post params and make some kind of
response or renderable dict. But that scales out too: it's better to
call a view callable than to shoehorn your request into HTTP, which is
what you'd do if you have one website call another. Sometimes that's
worthwhile, as in the aforementioned common server for multilingual
clients. But it's a lot of overhead if you don't have to.

And if you have an extensive Javascript front end, then maybe your web
application reduces down to zero, and the Javascript code can just
call the data server directly. That's how GMail works.

--
Mike Orr <slugg...@gmail.com>