Badges

Usage

Install from pypi, for instance by running pip install aiosql.

Then write parametric SQL queries in a file and execute it from Python methods, eg this greetings.sql file:

-- name: get_all_greetings()
-- Get all the greetings in the database
select greeting_id, greeting
  from greetings
 order by 1;

-- name: get_user_by_username(username)^
-- Get a user from the database using a named parameter
select user_id, username, name
  from users
  where username = :username;

This example has an imaginary SQLite database with greetings and users. It prints greetings in various languages to the user and showcases the basic feature of being able to load queries from a SQL file and call them by name in python code. Query parameter declarations (eg (username)) are optional, and enforced when provided.

You can use aiosql to load the queries in this file for use in your Python application:

import aiosql
import sqlite3

queries = aiosql.from_path("greetings.sql", "sqlite3")

with sqlite3.connect("greetings.db") as conn:
    user = queries.get_user_by_username(conn, username="willvaughn")
    # user: (1, "willvaughn", "William")

    for _, greeting in queries.get_all_greetings(conn):
        # scan: (1, "Hi"), (2, "Aloha"), (3, "Hola"), …
        print(f"{greeting}, {user[2]}!")
    # Hi, William!
    # Aloha, William!
    # …

Or even in an asynchronous way, with two SQL queries running in parallel using aiosqlite and asyncio:

import asyncio
import aiosql
import aiosqlite

queries = aiosql.from_path("greetings.sql", "aiosqlite")

async def main():
    async with aiosqlite.connect("greetings.db") as conn:
        user = await queries.get_user_by_username(conn, username="willvaughn")

        async for _, greeting in queries.get_all_greetings(conn):
            print(f"{greeting}, {user[2]}!")

asyncio.run(main())

It may seem inconvenient to provide a connection on each call. You may have a look at the AnoDB DB class which wraps both a database connection and queries in one connection-like extended object, including performing automatic reconnection when needed. The wrapper also allows to cache query results.

Why you might want to use this

• You think SQL is pretty good, and writing SQL is an important part of your applications.

• You don’t want to write your SQL in strings intermixed with your python code.

• You’re not using an ORM like SQLAlchemy or Django , with large (100k lines) code imprints vs under 1000 for aiosql and about 300 for anodb, and you don’t need to or don’t want to write SQL-like code with a Python syntax.

• You want to be able to reuse your SQL in other contexts, eg loading it into psql or other database tools.

Why you might NOT want to use this

• You’re looking for an ORM.

• You aren’t comfortable writing SQL code.

• You don’t have anything in your application that requires complicated SQL beyond basic CRUD operations.

• Dynamically loaded objects built at runtime really bother you.