Advice on database access approach in a custom environment using Python Pyramid

Question

I’m new to Pyramid. I’ve used Python for a few months. I've 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 the cx_Oracle package. 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'm not sure). In my Pyramid app, 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 and login. What I’m really looking for is a good approach from experienced Pyramid users before going down the wrong track. Many thanks.

Answer 1

You should definitely use SQLAlchemy as it makes use of connection pooling and such. In your case SQLAlchemy would use cx_Oracle underneath but make you be able to concentrate writing actual code instead of maintaining connections/pooling them/and such.

You should follow the patterns in Wiki2 tutorial to set up basic SQLAlchemy.

Answer 2

So, basically, the question boils down to "how do I use my existing API with Pyramid", right? This is quite easy as Pyramid is database-agnostic and very transparent in this area despite what you say :) Basically, you import it, call its methods, retrieve data and send it to the template:

import pokemon_api as api

@view_config(route_name='list_pokemon', renderer='pokemon_list.mako')
def list_pokemon(request):
    # To illustrate how to get data from the request to send to your API
    batch_start = request.GET.get('batch_start', 0)
    batch_end = batch_start + request.GET.get('batch_size', 0)
    sort_order = request.GET.get('sort_by', 'name')

    # Here we call our API - we don't actually care where it gets the data from, it's a black box
    pokemon = api.retrieve_pokemon(from=batch_start, to=batch_end, sort=sort_order)

    # send the data to the renderer/template
    return {
        'pokemon': pokemon
    }

@view_config(route_name='add_pokemon', request_method='POST')
def add_pokemon(request):
    """
    Add a new Pokemon
    """
    name = request.POST.get('name', 0)
    weight = request.GET.get('weight', 0)
    hight = request.GET.get('hight', 0)

    api.create(name=name, weight=weight, height=height)

    # go back to the listing
    return HTTPFound('/pokemon_list')

and if your API needs some initialization, you can do it at startup time

import pokemon_api as api

def main(global_config, **settings):
    """ This function returns a Pyramid WSGI application.
    """
    config = Configurator(settings=settings)
    ...

    api.init("MAGIC_CONNECTION_STRING")
    return config.make_wsgi_app()

Of course, this assumes your API already handles transactions, connections, pooling and other boring stuff :)

One last point to mention - in Python you generally don't import things inside methods, you import them at the top of the file at module-level scope. There are exceptions for that rule, but I don't see why you would need that in this case. Also, importing a module should be free of side-effects (which you might have since "importing my wrapper in my views.py __init__ method" seems to be causing trouble).