Welcome to anosql’s documentation!

https://badge.fury.io/py/anosql.svg http://readthedocs.org/projects/anosql/badge/?version=latest https://travis-ci.org/honza/anosql.svg?branch=master

A Python library for using SQL

Inspired by the excellent Yesql library by Kris Jenkins. In my mother tongue, ano means yes.

If you are on python3.6+ or need anosql to work with asyncio based database drivers. See the related project aiosql.

Installation

$ pip install anosql

Usage

Basics

Given a queries.sql file:

-- name: get-all-greetings
-- Get all the greetings in the database
SELECT * FROM greetings;

We can issue SQL queries, like so:

import anosql
import psycopg2
import sqlite3

# PostgreSQL
conn = psycopg2.connect('...')
queries = anosql.from_path('queries.sql', 'psycopg2')

# Or, Sqlite3...
conn = sqlite3.connect('cool.db')
queries = anosql.from_path('queries.sql', 'sqlite3)

queries.get_all_greetings(conn)
# => [(1, 'Hi')]

queries.get_all_greetings.__doc__
# => Get all the greetings in the database

queries.get_all_greetings.sql
# => SELECT * FROM greetings;

queries.available_queries
# => ['get_all_greetings']

Parameters

Often, you want to change parts of the query dynamically, particularly values in the WHERE clause. You can use parameters to do this:

-- name: get-greetings-for-language-and-length
-- Get all the greetings in the database
SELECT *
FROM greetings
WHERE lang = %s;

And they become positional parameters:

visitor_language = "en"
queries.get_all_greetings(conn, visitor_language)

Named Parameters

To make queries with many parameters more understandable and maintainable, you can give the parameters names:

-- name: get-greetings-for-language-and-length
-- Get all the greetings in the database
SELECT *
FROM greetings
WHERE lang = :lang
AND len(greeting) <= :length_limit;

If you were writing a Postgresql query, you could also format the parameters as %s(lang) and %s(length_limit).

Then, call your queries like you would any Python function with named parameters:

visitor_language = "en"

greetings_for_texting = queries.get_all_greetings(conn, lang=visitor_language, length_limit=140)

License

BSD, short and sweet