Easy SQL in Python
Project description
A Python library for using SQL
Inspired by the excellent Yesql library by Kris Jenkins. In my mother tongue, ano means yes.
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;
-- name: $select-users
-- Get all the users from the database,
-- and return it as a dict
SELECT * FROM USERS;
We can issue SQL queries, like so:
import anosql
import psycopg2
import sqlite3
# PostgreSQL
conn = psycopg2.connect('...')
queries = anosql.load_queries('postgres', 'queries.sql')
# Or, Sqlite3...
conn = sqlite3.connect('cool.db')
queries = anosql.load_queries('sqlite', 'queries.sql')
queries = queries.get_all_users(conn)
# [{"id": 1, "name": "Meghan"}, {"id": 2, "name": "Harry"}]
queries = queries.get_all_greetings(conn)
# => [(1, 'Hi')]
queries.get_all_greetings.__doc__
# => Get all the greetings in the database
queries.get_all_greetings.__query__
# => SELECT * FROM greetings;
queries.available_queries
# => ['get_all_greetings', 'get_all_books']
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)
Update/Insert/Delete
In order to run UPDATE, INSERT, or DELETE statements, you need to add ! to the end of your query name. Anosql will then execute it properly. It will also return the number of affected rows.
Insert queries returning autogenerated values
If you want the auto-generated primary key to be returned after you run an insert query, you can add <! to the end of your query name.
-- name: create-user<!
INSERT INTO person (name) VALUES (:name)
Adding custom query loaders.
Out of the box anosql supports SQLite and PostgreSQL via the stdlib sqlite3 database driver and psycopg2. If you would like to extend anosql to communicate with another type of database you may create a query loader class based on anosql.QueryLoader. The QueryLoader class is an abstract base class which will require you to override the process_sql and create_fn methods.
import anosql
class MyDbQueryLoader(anosql.QueryLoader):
def process_sql(self, name, op_type, sql):
# ... Provides a hook to make any custom preparations to the sql text.
return sql
def create_fn(self, name, op_type, sql, use_col_description):
# This hook lets you define logic for how to build your query methods.
# They take your driver connection and do the work of talking to your database.
# The class helps parse your SQL text, and has class level variables such as self.op_type to help you decide
# which operation a sql statement intends to perform.
#
# For examples of how to write query loader classes:
# see: `anosql.loaders.Psycopg2QueryLoader` and `anosql.loaders.SQLite3QueryLoader`.
def fn(conn, *args, **kwargs):
# ...
pass
return fn
# To register your query loader as a valid anosql db_type do:
anosql.register_query_loader("mydb", MyDbQueryLoader())
# To use make a connection to your db, and pass "mydb" as the db_type:
import mydbdriver
conn = mydbriver.connect("...")
anosql.load_queries("mydb", "path/to/sql/")
users = anosql.get_users(conn)
conn.close()
Tests
$ pip install tox $ tox
Caveats
Postgresql and sqlite only at the moment
License
BSD, short and sweet
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.