Creating aiohttp web application with SQLite database
In this article I’m going to describe using SQLite database within your aiohttp web application. Actually, using this approach you can connect any ODBC compatible database. Begin with installing required dependencies (Debian-based system assumed)
$ sudo apt-get install unixodbc unixodbc-dev python-pyodbc libsqliteodbc
First of all you need to create your database file and a schema for it. Simplest way to do this is to create
*.sql script and run it. Basic example looks like this
CREATE TABLE IF NOT EXISTS t1(
Then just run this command from a command line
sqlite3 database.db < init_database.sql where
database.db is the name of your database. It’s more convenient to store your script in separate file but you can do that from within python code as well
async def test_init_database(loop=None):
In case you create a connection with same parameters multiple times you can specify permanent parameters once and then save time and make a code cleaner
You can control transaction isolation levels within connection to your database, so make sure you have enabled
autocommit or commit your changes manually with explicit call.
Code with manual commiting
async def test_manual_commit(loop=None):
Code with autocommiting
async def test_auto_commit(loop=None):
When you need to provide parameters to your sql query use built-in ability to parametrize a query with
async def test_query_placeholders():
But why to use placeholder instead of string formatting? Imagine this synthetic example using
and when you execute such kind of code your code/data may corrupt
con = sqlite3.connect('database.db')
In this situation you end up with empty table and your data being lost. So remember to always sanitize/escape your code or rely on third-party libraries/code that will do this for you.
When not using context manager you may end up having unclosed connections in case any error occurred. You should handle closing connection by yourself enclosing code within
async def test_without_context_managers(loop=None):
Keep in mind these tips not only when working with this exact database/package but for every piece of your code. To see more examples visit aioodbc examples directory and for real world project check our this repository.
See you later folks!