Integrating SqlAlchemy with Tornado
Tornado has been an excellent webserver with a minimal framework feature set. I love the way how signed cookies have been implemented. So clean and easy to use.
When I started to use tornado for a project there are a few hurdles you might come across.
- Lack of ORM
- No form library
- no cache support
Since tornado is more of a webserver and minimal featured framework, I dont expect it to support the above. But in a project you need to use third party libraries to make it work
Note: I am blocking my tornado ioloop using this approach. I cant think of a use case where your DB needs to be async. If your reads fail you cant render the data anyway. If your writes fail you need to let your user know.
SqlAlchemy is by far an advanced ORM which supports a lot of options. Lets write some models and hook into a tornado app. Here is the typical structure of my tornado app, but feel free to use your own.
#!python - project |--- __init__.py |--- app.py |--- settings.py |--- models.py |--- utils.py |--- static/ | |-- css, js , images |--- templates |-- some/templates.html
#!python from sqlalchemy import create_engine from sqlalchemy import Column, Integer, String, DateTime, Boolean engine = create_engine('mysql://root:password@localhost/somedb', echo=False) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) username = Column(String(30), nullable=False) first_name = Column(String(30), nullable=False) last_name = Column(String(30), nullable=False) email = Column(String(75), nullable=False) password = Column(String(128), nullable=False) def __repr__(self): return "<User('%s')>" % (self.username) users_table = User.__table__ metadata = Base.metadata def create_all(): metadata.create_all(engine)
We are using SqlAlchemy’s declarative syntax to define models. We can define all our models import our models.py and import them into app.py for CRUD. I hardcoded the database credentials for blog post purpose but you should be defining them in settings.py and import the settings into models.py and replace the db credentials with one in settings.py.
Here is the part of app.py which sets the db connection as a attribute to the application object to ensure that we have one DB connection.
from sqlalchemy.orm import scoped_session, sessionmaker from models import * # import the engine to bind class Application(tornado.web.Application): def __init__(self): handlers = [ (r"/users", UsersHandler), ] settings = dict( cookie_secret="some_long_secret_and_other_settins" ) tornado.web.Application.__init__(self, handlers, **settings) # Have one global connection. self.db = scoped_session(sessionmaker(bind=engine)) class BaseHandler(tornado.web.RequestHandler): @property def db(self): return self.application.db def get_current_user(self): user_id = self.get_secure_cookie("user") if not user_id: return None return self.db.query(User).get(user_id)
As you can see we are importing the sqlalchemy sessionmaker to create a db session and set the session object as an attribute of application. All handlers will be inherting from BaseHandler there by making the sqlalchemy session object accessible.
self.db.query(User).filter_by(first_name="mark").all() => [<User('mark'), <User('markshooter'), <User('markt')] self.db.query(User).get(1) => [<User('mark')] user = User(username="yashh", email="email@example.com", password="some") self.db.add(user) self.db.commit() user.id => 2 self.db.remove(user) self.db.commit() # deletes the user from table
Form Handling For form handling I use WTForms. A pretty simple form library where we can define a Form class and set some attributes and their types. WTForms ships with good validators like email , url , required, integer, etc. Its pretty easy to write a custom validator as well.
Cache For cache you can pip install python-memcache and set cache object as part of application just like the db object.
import memcache self.cache = memcache.Client(['127.0.0.1:11211'], debug=0) self.cache.set('foo', 'bar') self.cache.get('foo')
With these libraries you can get a decent amount of work done.