Basics of database interaction in Tornado

Tornado is a powerful Python web framework and an asynchronous networking library. It's especially good at handling multiple simultaneous connections, which makes it suitable for applications that demand high performance and scalability. In this tutorial, we will cover the basics of interacting with databases in Tornado.

Tornado and Database Interaction

Tornado is not bundled with a database adapter like some other frameworks, but it can be easily integrated with a variety of database systems. This tutorial will show you how to use Tornado with a simple SQLite database, but the principles can be applied to other databases as well.

Prerequisites

Before we start, make sure you have the following installed:

• Python 3.6 or later
• Tornado
• SQLite3
• AioSQLite (Asynchronous SQLite library for Python)

You can install the required packages with pip:

pip install tornado aiomysql sqlite3

Creating a Database Connection

In Tornado, we use asynchronous database libraries to handle database connections. For SQLite, we use AioSQLite. Here's how to create a connection:

import aiomysql
import tornado.ioloop
import tornado.web

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        db = await aiomysql.connect('database.db', loop=self.application.loop)
        self.write("Hello, world")

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

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

In the code above, we create a connection to the 'database.db' SQLite database in the get method of our request handler.

Executing Database Queries

Now that we have a connection, we can execute SQL queries. Let's create a simple table and insert some data:

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        db = await aiomysql.connect('database.db', loop=self.application.loop)
        
        cursor = await db.cursor()
        await cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
        await cursor.execute("INSERT INTO users (name) VALUES ('John Doe')")
        await db.commit()
        
        self.write("Data inserted")

In this example, we first create a cursor from our database connection. We then execute two queries: one to create a table called 'users' if it doesn't exist, and another to insert a user named 'John Doe' into the table.

Fetching Data From the Database

Fetching data from the database is just as simple:

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        db = await aiomysql.connect('database.db', loop=self.application.loop)
        
        cursor = await db.cursor()
        await cursor.execute("SELECT * FROM users")
        users = await cursor.fetchall()
        
        for user in users:
            self.write(f"User: {user[1]}<br>")

Here, we execute a SELECT statement to get all users from the table, then use the fetchall method of the cursor to retrieve all rows. The rows are returned as a list of tuples, where each tuple represents a row.

Closing the Database Connection

It's good practice to close your database connections when you're done with them. Here's how:

class MainHandler(tornado.web.RequestHandler):
    async def get(self):
        db = await aiomysql.connect('database.db', loop=self.application.loop)
        
        cursor = await db.cursor()
        await cursor.execute("SELECT * FROM users")
        users = await cursor.fetchall()
        
        for user in users:
            self.write(f"User: {user[1]}<br>")
        
        await db.close()

Conclusion

That's it! You now know the basics of interacting with databases in Tornado. Of course, this is just the start. In a real-world application, you would need to handle errors, use prepared statements to prevent SQL injection, and possibly use a database ORM to make your code cleaner and more maintainable. But this tutorial should give you a good start. Happy coding!