On Mon, Jul 11, 2016 at 10:44 AM, Niall <[email protected]> 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 <[email protected]>
--
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/pylons-discuss/CAH9f%3DuoDdw5O3cEM4Y5Q0GbL3YHvPCK6EVpELMXHiGiTG49M2g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
