interloper-sql
SQL for interloper: ibis_view, portable views authored as Ibis expressions,
and behind the postgres extra a SQL database destination, SQLDestination,
a DatabaseDestination over SQLAlchemy Core with the SQLConnection that
holds the database URL.
The base package depends on Ibis only, so a package that defines ibis_views
pulls in no database driver. interloper-sql[postgres] adds SQLAlchemy and
psycopg[binary]; the destination and its connection exist only with it.
One backend serves every SQLAlchemy dialect whose driver is installed. PostgreSQL works with the extra; MySQL, SQLite, Redshift, SQL Server and the rest need their own driver on top.
Setup
The connection is a single SQLAlchemy URL:
| Database | URL | Driver to install |
|---|---|---|
| PostgreSQL | postgresql+psycopg://user:password@host:5432/db |
interloper-sql[postgres] |
| MySQL / MariaDB | mysql+pymysql://user:password@host:3306/db |
pymysql |
| SQL Server | mssql+pyodbc://user:password@host/db?driver=ODBC+Driver+18+for+SQL+Server |
pyodbc (and the ODBC driver) |
| Redshift | redshift+redshift_connector://user:password@host:5439/db |
sqlalchemy-redshift, redshift_connector |
| SQLite | sqlite:///path/to/file.db |
none, in the standard library |
Install the driver next to interloper (uv pip install pymysql, or a custom
image built from the slim variant). Special characters in the password must be
URL-encoded.
The URL carries the password, so it is a secret field: encrypted at rest in a
deployed instance and masked in the UI. It also loads from the environment
(SQL_URL), so SQLConnection() works with no arguments.
The connection's check runs SELECT 1.
Usage
import interloper as il
from interloper_sql import SQLConnection, SQLDestination
warehouse = SQLDestination(
connection=SQLConnection(url="postgresql+psycopg://user:password@host:5432/analytics"),
default_dataset="marketing",
)
In a deployed instance you configure this through the UI instead: add a SQL database connection, then a SQL database destination using it.
Datasets are schemas
An asset's dataset is the SQL schema its table lives in. Without one, the
destination's default_dataset applies; without either, the table goes in
the connection's default schema (public on PostgreSQL, the database itself on
MySQL). A named schema is created on first write when the database supports
schemas and it does not exist yet.
Tables
A table is created on first write from the asset's effective schema (declared,
or inferred during conform): integers are BIGINT, floats DOUBLE, decimals
NUMERIC(38, 9), datetimes timezone-aware timestamps, strings TEXT, and
nested or repeated fields JSON. An existing table is never altered: columns
it lacks are dropped from the write with a warning, and NaN or infinite floats
are written as NULL.
Partitions
Writes replace, inside one transaction:
- unpartitioned: every row is deleted, then the data inserted;
- a time partition: the rows inside its half-open bounds
(
column >= start AND column < end) are deleted, then the data inserted; - any other partition: the rows equal to its id are deleted;
- a window: each partition's rows are deleted, then the whole batch is inserted once.
A failed insert rolls the delete back, so a partition is never left empty.
Inserts go in batches of 1000 rows. Reads return rows as list[dict]; reading
a table that was never written raises DataNotFoundError.
Metadata
Release files for interloper-sql 0.114.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| interloper_sql-0.114.0.tar.gz | 11.3 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| interloper_sql-0.114.0-py3-none-any.whl | Python 3 | none | any | Details |
Total release size: 25.3 kB
Release files / interloper_sql-0.114.0.tar.gz
| Download URL | interloper_sql-0.114.0.tar.gz |
|---|---|
| Size | 11.3 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
aa188e7cefa59e617df95c8851eab11f4021e82b51c7c88b47f1f5bdb6f0faba
|
|
BLAKE2b-256 checksum How to use checksums |
7851cebe4b081ace407aceccb3580b37936d1f9b4a6a7e1d9f053fedbb38b92a
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.13.1 {"installer":{"name":"uv","version":"0.13.1","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}
|
Release files / interloper_sql-0.114.0-py3-none-any.whl
| Download URL | interloper_sql-0.114.0-py3-none-any.whl |
|---|---|
| Size | 14.0 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
db63e7c36e865f49a7bb80d0d4a4fda13cda3043463cc7a5267b2f4c32c8973b
|
|
BLAKE2b-256 checksum How to use checksums |
a81671117d268568ddf27ddc749db4f24b7de86e616b93adbe8677fdfeeac21c
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.13.1 {"installer":{"name":"uv","version":"0.13.1","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}
|
