How to make SQLAlchemy in Tornado to be async?

In the world of internet improvement, asynchronous programming has grown in importance to efficiently manage excessive degrees of concurrency. Tornado is a popular asynchronous web framework for Python, regarded for handling thousands of concurrent transactions easily. SQLAlchemy, alternatively, is an effective Object-Relational Mapping (ORM) library that simplifies database interactions in Python applications. Combining the power of Tornado and SQLAlchemy can result in sturdy and high-performance internet applications. In this article, we explore how to make SQLAlchemy work easily in a Tornado application, whilst leveraging the power of asynchronous programming.

Concepts Related to the Topic

Before diving into the practical programs, allow’s in brief speak a few vital principles regarding this topic:

Asynchronous Functions:

• Asynchronous programming is a programming paradigm that permits obligations to run concurrently, permitting more green and responsive useful resource usage in packages.
• In Python, the async and watch-for keywords are used to explain asynchronous code.
• Asynchronous code may be stopped and restarted, allowing extra tasks to be performed without blocking the principle thread.

Tornado:

• Tornado is a Python internet framework designed for building asynchronous net applications.
• It makes use of an occasion loop to successfully deal with I/O-certain operations, making it suitable for high-concurrency scenarios.
• Tornado gives a non-blocking off server, making it ideal for long-lived connections and actual-time programs like chat servers and streaming services.

SQLAlchemy:

• SQLAlchemy is a famous Python library that simplifies database interactions by imparting an ORM.
• It allows builders to paint with databases using Python objects instead of uncooked SQL queries.
• SQLAlchemy supports various database engines, making it a versatile preference for database integration in Python programs.

Coroutine:

• In Python, a coroutine is a special type of function that may be paused and resumed.
• Coroutines are defined with the async def syntax and are used to write asynchronous code.
• In Tornado, coroutines are regularly used to handle asynchronous operations like database queries.

Tornado’s Asynchronous Decorators:

• Tornado affords decorators like @gen.Coroutine and @gen.Asynchronous to mark functions as asynchronous.
• These decorators enable the usage of coroutines within Tornado programs.

SQLAlchemy in Tornado to be Async

Now that we have a foundational understanding of the basic concepts, let’s move on to the steps needed to get SQLAlchemy running asynchronously in a Tornado application.

Step 1: Set the environment

Make sure you have the following options installed before you begin.

• Python
• Tornado
• SQLAlchemy
• A compatible database engine (e.g., SQLite, MySQL, PostgreSQL)

You can install Tornado and SQLAlchemy with pip:

pip install tornado sqlalchemy

Step 2: Run the Tornado Application

To create a Tornado application Start by defining a basic Tornado web application. This will be the basis for integrating SQLAlchemy asynchronously.

Python3

import tornado.ioloop
import tornado.web

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        self.write("Hello, Tornado!")

def make_app():
    return tornado.web.Application([
        (r"/", MainHandler),
    ])

if __name__ == "__main__":
    app = make_app()
    app.listen(8888)
    tornado.ioloop.IOLoop.current().start()

Step 3: Configure SQLAlchemy

Next, you will need to configure SQLAlchemy to work with your chosen database engine. SQLAlchemy supports multiple databases, so make sure you have the correct database driver installed. Here is an example of configuring SQLAlchemy for SQLite:

Python3

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Create a SQLAlchemy engine
engine = create_engine("sqlite:///mydatabase.db")

# Create a session factory
SessionFactory = sessionmaker(bind=engine)

Step 4: Use Asynchronous SQLAlchemy Queries

To make SQLAlchemy asynchronous, you will need to use coroutines and Tornado asynchronous preparers. In this example, we use the @gen.coroutine decorator to tag the query_database. In the coroutine, we create a session, execute an asynchronous database query, and close the session on the finally block. Let’s create an asynchronous task for a simple database query:

Python3

from tornado import gen

@gen.coroutine
def query_database():
    # Create a session
    session = SessionFactory()

    try:
        # Asynchronously query the database
        result = session.query(User).filter(User.name == "Alice").first()

        # Process the result
        if result:
            print("User found:", result.name)
        else:
            print("User not found")

    finally:
        # Close the session
        session.close()

Step 5: Add Asynchronous SQLAlchemy to Tornado Handlers

To use asynchronous SQLAlchemy queries in your Tornado handler, simply call the query_database function in the Tornado handler. Be sure to use the yield keyword when calling an asynchronous function to wait for the result:

Python3

class UserHandler(tornado.web.RequestHandler):
    async def get(self):
        # Call the asynchronous database query
        await query_database()
        self.write("User query complete")

if __name__ == "__main__":
    app = make_app()
    app.listen(8888)
    tornado.ioloop.IOLoop.current().start()

When you call await query_database(), you allow the Tornado event loop to continue its other tasks while it waits for the database query to complete.

Full Code Implementation (main.py)

Python3

import tornado.ioloop
import tornado.web
from tornado import gen
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# Define the SQLAlchemy model
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

# Configure SQLAlchemy
engine = create_engine("sqlite:///mydatabase.db")
SessionFactory = sessionmaker(bind=engine)

# Define asynchronous SQLAlchemy query
@gen.coroutine
def query_user_by_name(name):
    session = SessionFactory()

    try:
        result = session.query(User).filter(User.name == name).first()
        raise gen.Return(result)
    finally:
        session.close()

# Tornado request handler
class UserHandler(tornado.web.RequestHandler):
    async def get(self, name):
        user = await query_user_by_name(name)
        if user:
            self.write(f"User found: {user.name}")
        else:
            self.write("User not found")

def make_app():
    return tornado.web.Application([
        (r"/user/([^/]+)", UserHandler),
    ])

if __name__ == "__main__":
    app = make_app()
    app.listen(8888)
    tornado.ioloop.IOLoop.current().start()

*Here, This code snippets are for the reference purpose. In real life scenario, all the codes are written in different files.

Video Output