Database Connection Manager for Pyramid using minimal SQLAlchemy

Question

I made a starter (pcreate -s starter myproject) project in Pyramid. How do I make database connection available to my application when needed

I looked here: Subscribing Database Conenction.

Below is the code:

@subscriber(NewRequest)
def new_request_subscriber(event):
    request = event.request
    settings = request.registry.settings
    request.db = sqlite3.connect(settings['db'])
    request.add_finished_callback(close_db_connection)

def close_db_connection(request):
    request.db.close()

I am worried about the performance as it will make a database connection for every request whether or not we use it or not.

Is it fine or should I do it in some other way?How can I use sqlalchemy(in a minimal way,may be 10 to 15 lines of code)and make it better?

Note:I don't want to use sqlalchemy orm(deep learning curve).Therefore I avoided (pcreate -s alchemy MyProject)

Answer 1

SQLAlchemy solves quite a few problems you're about to discover if you choose to do everything yourself :) Like connection pooling, transaction management, thread-local sessions etc.

If you don't want to use the ORM part of SQLAlchemy and prefer to use literal SQL everywhere (say hello to SQL injection when you meet it), you can easily do something like

result = session.execute("""SELECT spam, eggs FROM blah WHERE moo='foo'""")
for row in result:
    print "spam = %s" % row.spam
    print "eggs = %s" % row.eggs

I would just start with an app created from the "alchemy" template and remove the parts you're not going to use. Which probably are just the definition of MyModel in models.py and the sample view in views.py.

Answer 2

You can use config.add_request_method() to do what you need:

def db_connect(request)
    def db_close(request):
        if conn is not None:
            conn.close()
    conn = sqlite3.connect(request.registry.settings['db'])
    request.add_finished_callback(db_close)  
    return conn          

config.add_request_method(db_connect, 'db', reify=True)

What this does is that when you use 'request.db' in your code, it will call the db_connect() function to get the connection, reify=True means that it will only call the function once to get the connection. And add_finished_callback() will call the db_close() function to close the connection.

That way the dababase connection will only be initialized only when you use request.db, and will be closed when a a connection has been initialized.