• Stars
    star
    1,294
  • Rank 36,356 (Top 0.8 %)
  • Language
    Python
  • License
    Other
  • Created almost 6 years ago
  • Updated about 1 month ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Simple SQL in Python

aiosql - Simple SQL in Python

SQL is code. Write it, version control it, comment it, and run it using files. Writing your SQL code in Python programs as strings doesn't allow you to easily reuse them in SQL GUIs or CLI tools like psql. With aiosql you can organize your SQL statements in .sql files, load them into your python application as methods to call without losing the ability to use them as you would any other SQL file.

This project supports standard PEP 249 and asyncio based drivers for SQLite (sqlite3, aiosqlite, apsw), PostgreSQL (psycopg (3), psycopg2, pg8000, pygresql, asyncpg), MySQL (PyMySQL, mysqlclient, mysql-connector), MariaDB (mariadb) and DuckDB (duckdb), out of the box. Note that some detailed feature support may vary depending on the underlying driver and database engine actual capabilities.

This module is an implementation of Kris Jenkins' yesql Clojure library to the Python ecosystem. Extensions to support other database drivers can be written by you! See: Database Driver Adapters. Feel free to pull request!

Badges

Build status Code Coverage Tests Issues Contributors Pypi Downloads Stars Version Code Size Databases Drivers Language Count Top Language Python Versions Badges BSD 2-Clause License

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^
-- 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.

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 asynchroneous 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:
        # Parallel queries!
        greetings, user = await asyncio.gather(
            queries.get_all_greetings(conn),
            queries.get_user_by_username(conn, username="willvaughn")
        )

        for _, greeting in greetings:
            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 query functions in one connection-like extended object, including managing automatic reconnection if needed.

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 about 800 for aiosql, and you don't need to.
  • You want to be able to reuse your SQL in other contexts. 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.

More Repositories

1

sublimetext2-customize-vintage-mode

Accompanying Sublime Text 2 configurations for my blog post on setting up Sublime in vintage mode.
37
star
2

haskellbook-solutions

My (incomplete && ugly) notes and solutions to haskellbook
Haskell
24
star
3

ergast-python

Python HTTP client to the Ergast Motor Racing Data API
Python
9
star
4

generator-backbone-module

Yeoman generator for creating distributable backbone modules
JavaScript
8
star
5

monolog-gitter-im

Sends monolog notifications through the https://gitter.im api to a targeted gitter chat room.
PHP
7
star
6

generator-react-hapi-universal

Scaffold a Universal JavaScript App using React and hapi
JavaScript
7
star
7

comtrade-coffee

Sample usage of the comtrade UN database API
JavaScript
5
star
8

checkbox-group

React Checkbox Group Component
JavaScript
4
star
9

generator-hapi-nack

hapijs micro-service generator
JavaScript
3
star
10

generator-hapi-es2015

Scaffold a hapijs micro service
JavaScript
3
star
11

file-parser

PHP composer package for parsing data contained in a file into a php array.
PHP
3
star
12

wranglingf1data

F1 data science experiments in python based on *Wrangling F1 Data with R* book
Jupyter Notebook
2
star
13

generator-react-nack

Scaffold a react component
JavaScript
2
star
14

generator-es6nm

Scaffold out a node module written in ES6
JavaScript
1
star
15

litmus

Set of very simple exercises designed to gage how a person operates a computer and writes JS
JavaScript
1
star
16

stateless-radio

A Stateless React Component for Radio Inputs.
JavaScript
1
star
17

chalksay

When you chalksay, you turn console.log into console.fun!
JavaScript
1
star
18

sensei-list

Abstracted base class for lists in backbone.js. Heavily inspired by ("ripped off from") Marionette.CollectionView
JavaScript
1
star
19

monolog-symfony2-console

Monolog Handler for Symfony2 Console Component output.
PHP
1
star
20

hapi-pkg

Hapi Plugin which provides a JSON API to package.json properties. (and other objects too)
JavaScript
1
star
21

backbone-collection-wax

Backbone.Collection mixin which works with backbone-model-wax to provide methods for controlling 'on'/'off' state in collections
JavaScript
1
star
22

backbone-model-wax

Backbone.Model mixin which works with backbone-collection-wax to provide methods for controlling 'on'/'off' state in collections
JavaScript
1
star