How to deploy a Python/SQLAlchemy application?

Question

Dear all:

SQLAlchemy allowed me to create a powerful database utility. Now I don't know how to deploy it. Let me explain how is it built with an example:

# objects.py
class Item(object):
 def __init__(self, name):
  self.name = name

# schema.py
from sqlalchemy import *
from objects import Item
engine=create_engine('sqlite:///mydb.db')
metadata = MetaData(engine)
item_table = Table(
 'items', metadata,
 Column('id', Integer, primary_key=True),
 Column('name', String(100))
)

item_mapper = mapper(Item, item_table)
metadata.create_all()

# application.py
from schema import engine, Item
from sqlalchemy import *

Session = sessionmaker(bind=engine)

class Browser(object):
 def __init__(self):
  self.s = Session()
 def get_by_name(self, name):
  return self.s.query(Item).filter_by(name=name)

As you can see, what I want to make available is the last interface (Browser) where I simplify the queries for the end user.

If you simply request every user to open a Python shell and from application import Browser it seems that the advantages of connection pooling are not realized, because every user creates a different Session class (as opposed to creating a different session instance).

So, should I write a server that the users connect to? Or, how would you deploy this hypothetical application?

Thank you.

Answer 1

Connection pooling happens within the same python instance, so when your users connect from remote to the database, you have to write a small server anyways, if you want to use it. You can also connect directly to a database server, resulting in (at least) one connection per user. Depends on what you want to achieve.