Skip to main content

Integrating Tornado with SQL databases

Tornado is a powerful Python web framework and asynchronous networking library. When building web applications with Tornado, it's very common to interact with a database. This article will guide you to integrate SQL databases with your Tornado application.

1. Introduction​

SQL (Structured Query Language) databases are widely used in web development due to their robustness and reliability. With the power of SQL databases, you can store, retrieve, and manipulate your application's data efficiently.

2. Choosing a SQL Database​

Several SQL databases are available, such as MySQL, PostgreSQL, and SQLite. Choose one that fits your project requirements. For this tutorial, we will use SQLite because of its simplicity and ease of setup.

3. Installing SQLite​

You can download SQLite from the official SQLite downloads page. After installation, test it by creating a database and executing a simple SQL command.

4. Tornado and SQL: The Basics​

Tornado does not include built-in support for SQL databases. However, it can work with any database library in Python. To connect Tornado with SQLite, we'll use the sqlite3 module included in Python's standard library.

5. Setting Up the Database Connection​

First, import the sqlite3 module at the beginning of your Python script:

import sqlite3

Then, establish a connection to your SQLite database:

conn = sqlite3.connect('my_database.db')

6. Using Tornado with the Database​

To execute SQL commands with Tornado, create a cursor object using the connection object's cursor() method. Then, use the cursor's execute() method:

c = conn.cursor()
c.execute('''
CREATE TABLE users(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE);
''')
conn.commit()

7. Handling Asynchronous Database Calls​

Tornado is built around a non-blocking (asynchronous) I/O model. To make your database calls non-blocking, you can use libraries such as aiomysql or aiosqlite. Here's an example with aiosqlite:

import aiosqlite
import tornado.ioloop
import tornado.web

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        async with aiosqlite.connect('my_database.db') as db:
            async with db.cursor() as cursor:
                await cursor.execute("SELECT * FROM users;")
                data = await cursor.fetchall()
        self.write(str(data))

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

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

In this code, aiosqlite.connect() opens a connection to the SQLite database, and cursor.execute() is used to execute a SELECT statement. The fetchall() method is used to fetch all rows returned by the SELECT statement.

8. Conclusion​

This tutorial has guided you through integrating an SQL database with a Tornado application. We've covered choosing a suitable SQL database, setting up the database connection, and handling asynchronous database calls. Practise these steps and try to build your own Tornado application with an SQL database.

Remember, learning by doing is the best way to understand and master these concepts. Happy coding!