A tiny library for submitting raw SQL via SQLAlchemy
Project description
sqla-raw
An opinionated, minimalist library for fetching data from a SQLAlchemy connection, when you don't need or want an ORM. You know how to write SQL; sqla-raw
makes it E-Z to send that raw SQL to your database and get results, saving a lot of DBAPI boilerplate and providing a simple and consistent interface with a result format that is straightfoward to introspect.
Really not much more than a single method (raw.db.result()
) that submits raw SQL via a SQLAlchemy Engine connection. By default, db.result()
returns all results as a list of dictionaries, keyed by column names. (See 'Usage' below for other options)
For convenience, result_from_file()
and result_by_name()
allow you to store your SQL in separate local files for submission to the database via result()
Engine instantiation is handled implicitly by the first call to result()
; any subsequent calls use a connection from the pool. The connection string for the Engine is set by DATABASE_URL
in the environment. All other Engine settings use SQLAlchemy defaults. (Affording explicit creation and disposal of the Engine and exposing the setting of other parameters might be a useful area for further development, if it can be kept simple.)
Installation
pip install sqla-raw[pg]
Usage
Configure your database connection string by setting $DATABASE_URL
in your environment.
>>> from raw import db
>>> x = db.result('select version()');
>>> x
[{'version': 'PostgreSQL 10.10 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit'}]
Because it's SQLAlchemy, you can safely use named parameters in your SQL string with colon-prepended :key
format, and assign values in kwargs
.
>>> db.result('select :foo as bar', foo='baz')
[{'bar': 'baz'}]
Jinja templating
You can also use Jinja2 templating syntax to interpolate the query, if desired. db.result()
inspects the query for template tags ("{%.*%}"
) and renders the template to SQL before submitting if tags are present. (It uses a SandboxedEnvironment
for some measure of injection safety, but avoid this option with untrusted inputs, for obvious reasons.)
Options
Passing argument returns
to db.result()
(or result_from_file()
) overrides the default result formatting: returns="tuples"
brings back a list of tuples with row values instead of dictionaries, and returns="proxy"
returns the plain SQLAlchemy ResultProxy object directly, for further handling by the caller. The "proxy"
option allows access to methods (e.g. fetchone()
or fetchmany()
) that sqla-raw
default usage hides behind its facade; it can also be good for SQL statements (such as inserts
without returning
or DDL) that are not expected to return results — although by default these will return an empty list.
SQL file handling
For longer or more complex queries, it is generally more convenient and maintainable to save your SQL in its own file, rather than include it inline in your Python program. Doing so also allows the queries to be tested and/or reused in your preferred database client tool. sqla-raw
provides two ways to do this:
result_from_file()
takes a path (any file-like object should also work) and reads your query from there, rather than taking a SQL string argument directly. Contents of the file are handed off to result() so the rest functions identitically.
result_by_name()
looks for SQL files in a local directory — ${PWD}/query_files
by default, or you may specify any arbitrary filesystem location by setting $QUERY_PATH
in the environment. The query_name
arguement is the stem of the desired file.
Tests
pytest
tests are located in tests/. Install test prerequisites with pip install -r tests/requirements.txt
; then they can be run with: python setup.py test
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.
Source Distribution
Built Distribution
Hashes for sqla_raw-1.0.0rc1-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | b0079c6951a9e8e3ecc432c06a437f63f0e7e4b44beacf55d41a8978edfb6b45 |
|
MD5 | f51c01b1dd475e855f8457feb803d9d5 |
|
BLAKE2b-256 | bba5b82077ad61ee1b111f074310c92c1e6b0d3edf4f4d5231a6fe2afe3bbddc |