sqla-raw 1.7.0

A tiny library for submitting raw SQL via SQLAlchemy

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 SQLAlchemy RowMapping dictionaries, keyed by column names. (See 'Usage – Options' 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()

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 16.3 (Debian 16.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 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.)

>>> sql = """
... select * from a_table
... {% if filter_value %}
... where a_column = :filter_value
... {% endif %}
... """
>>> db.result(sql)
[{'a_column': 1}, {'a_column': 2}, {'a_column': 7}, {'a_column': 9}]
>>> db.result(sql, filter_value=7)
[{'a_column': 7}]

Options

Passing argument returns to db.result() (or result_from_file()) overrides the default result formatting: returns="tuples" brings back a list of Row named tuples with row values instead of dictionaries, and returns="proxy" returns the plain SQLAlchemy CursorResult 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, you may find it more convenient and maintainable to save your SQL in its own file, rather than include it inline as a string 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 identically.

• result_by_name() looks up files with a .sql extension in a local directory — it looks in ${PWD}/query_files by default, or you may specify any arbitrary filesystem location by setting $QUERY_PATH in the environment. The query_name argument is the stem of the desired file, i.e. the base name of the file without the .sql extension.

SQLAlchemy Engine invocation

By default, Engine instantiation is handled implicitly on first call to result(); subsequent calls use a connection from the pool. The default connection string for the Engine is set by DATABASE_URL in the environment, and all other Engine settings use SQLAlchemy defaults. This allows you to simply call result() and start querying $DATABASE_URL immediately with a minimum of fuss.

In case you require multiple database connections, or more control over Engine parameters, db.engine() wraps sqlalchemy.create_engine(), so you can set a different connection string or pass additional settings as keyword arguments (see https://docs.sqlalchemy.org/en/latest/core/engines.html for options). Once db.engine() is explicitly invoked, the engine so instantiated remains as the active connection pool unless changed again.

Snowflake database keypair authentication

Assuming you have a Snowflake user set up with keypair authentication, db.engine() can be configured to connect that way by setting PRIVATE_KEY_PATH in the environment to point to your local PEM-formatted private key file along with PRIVATE_KEY_PASSPHRASE setting the passphrase for the key.

As with other database drivers, snowflake-sqlalchemy is optional and not a strict dependency of sqla-raw so will need to be installed separately for Snowflake connections.

Exception handling

Obviously, when interacting with a database, any number of things can go wrong, that may or may not be the fault of your code. Besides obvious syntax errors, inputs to parameters might be the wrong type, the database could be unreachable, credentials incorrect or changed, etc. Early development versions of sqla-raw tried to catch any database exceptions and return them formatted like results, on the theory that any calling program wouldn't want to halt execution on such errors. On further reflection, it doesn't seem like a library should be making that decision, and sqla-raw as of version 1.x allows any exceptions it may encounter to be raised in the usual way. Any calling code that does not wish to halt on these exceptions may of course simply wrap the call to any raw.db method in a try/except block itself. In hindsight, it probably should have been clear this was the right way to do it all along.

Tests

pytest tests are located in tests/. Install test prerequisites with pip install -r tests/requirements.txt; then they can be run with: pytest --cov

Alternatives and prior art

These are all fine projects, and if sqla-raw appeals to you at all, you owe it to yourself to take a look at them. These and sqla-raw are all similar tools with similar SQL-first, non-ORM philosophies. I haven't benchmarked performance for any one of them, but 3 out of 4 use SQLAlchemy under the covers, and I'd be surprised if there are big differences among at least those three. Until some notable difference in performance turns up, the best choice for you is most likely a matter of taste.

• aiosql
  • Supports standard and async I/O
  • Turns SQL files into callable methods
    • Nothing wrong with that, but different from the interface chosen for sqla-raw (which takes the SQL or file name as argument to a single result() method)
    • Relies on special comments in the SQL
  • Not SQLAlchemy; supports a more limited set of database drivers
  • Doesn't handle database connect instantiation (expects to be given a conn object)
• PugSQL
  • Based on Clojure's HugSQL library
  • Uses SQLAlchemy
  • Similar API to aiosql, with commented SQL files used to create methods
    • Also generates modules from folders of SQL files, and can load multiple such modules
• Records
  • Another SQLAlchemy facade, and a big inspiration for sqla-raw
  • Doesn't seem to be actively maintained (This may have improved somewhat – it's been updated in the last year)
  • Formats results as a specialized Record class, based on tablib
    • Again, nothing wrong with that — sqla-raw favors a standard list-of-dicts format for results instead, as lighter weight and easier to introspect