Python library for querying SQL databases
Project description
Sqeleton
Sqeleton is a Python library for querying SQL databases.
It consists of -
-
A fast and concise query builder, designed from scratch, but inspired by PyPika and SQLAlchemy
-
A modular database interface, with drivers for a long list of SQL databases.
It is comparable to other libraries such as SQLAlchemy or PyPika, in terms of API and intended audience. However, there are several notable ways in which it is different.
Features:
🏃♂️High-performance: Sqeleton's API is designed to maximize performance using batch operations
- No ORM! While ORMs are easy and familiar, their granular operations are far too slow.
- Compiles queries 4 times faster than SQLAlchemy
🙌Parallel: Seamless multi-threading and multi-processing support
💖Well-tested: In addition to having an extensive test-suite, sqeleton is used as the core of data-diff.
✅Type-aware: The schema is used for validation when building expressions, making sure the names are correct, and that the data-types align. (WIP)
- The schema can be queried at run-time, if the tables already exist in the database
✨Multi-database access: Sqeleton is designed to work with several databases at the same time. Its API abstracts away as many implementation details as possible.
Databases we fully support:
- PostgreSQL >=10
- MySQL
- Snowflake
- BigQuery
- Redshift
- Oracle
- Presto
- Databricks
- Trino
- Clickhouse
- Vertica
- DuckDB >=0.6
- SQLite (coming soon)
💻Built-in SQL client: Connect to any of the supported databases with just one line.
Example usage: sqeleton repl snowflake://...
- Has syntax-highlighting, and autocomplete
- Use
*text
to find all tables like%text%
(or just*
to see all tables) - Use
?name
to see the schema of the table calledname
.
Documentation
Or jump straight to the introduction.
Install
Install using pip:
pip install sqeleton
It is recommended to install the driver dependencies using pip's []
syntax:
pip install 'sqeleton[mysql, postgresql]'
Read more in install / getting started.
Example: Basic usage
We will create a table with the numbers 0..100, and then sum them up.
from sqeleton import connect, table, this
# Create a new database connection
ddb = connect("duckdb://:memory:")
# Define a table with one int column
tbl = table('my_list', schema={'item': int})
# Make a bunch of queries
queries = [
# Create table 'my_list'
tbl.create(),
# Insert 100 numbers
tbl.insert_rows([x] for x in range(100)),
# Get the sum of the numbers
tbl.select(this.item.sum())
]
# Query in order, and return the last result as an int
result = ddb.query(queries, int)
# Prints: Total sum of 0..100 = 4950
print(f"Total sum of 0..100 = {result}")
Example: Advanced usage
We will define a function that performs outer-join on any database, and adds two extra fields: only_a
and only_b
.
from sqeleton.databases import Database
from sqeleton.queries import ITable, leftjoin, rightjoin, outerjoin, and_, Expr
def my_outerjoin(
db: Database,
a: ITable, b: ITable,
keys1: List[str], keys2: List[str],
select_fields: Dict[str, Expr]
) -> ITable:
"""This function accepts two table expressions, and returns an outer-join query.
The resulting rows will include two extra boolean fields:
"only_a", and "only_b", describing whether there was a match for that row
only in the first table, or only in the second table.
Parameters:
db - the database connection to use
a, b - the tables to outer-join
keys1, keys2 - the names of the columns to join on, for each table respectively
select_fields - A dictionary of {column_name: expression} to select as a result of the outer-join
"""
# Predicates to join on
on = [a[k1] == b[k2] for k1, k2 in zip(keys1, keys2)]
# Define the new boolean fields
# If all keys are None, it means there was no match
# Compiles to "<k1> IS NULL AND <k2> IS NULL AND <k3> IS NULL..." etc.
only_a = and_(b[k] == None for k in keys2)
only_b = and_(a[k] == None for k in keys1)
if isinstance(db, MySQL):
# MySQL doesn't support "outer join"
# Instead, we union "left join" and "right join"
l = leftjoin(a, b).on(*on).select(
only_a=only_a,
only_b=False,
**select_fields
)
r = rightjoin(a, b).on(*on).select(
only_a=False,
only_b=only_b,
**select_fields
)
return l.union(r)
# Other databases
return outerjoin(a, b).on(*on).select(
only_a=only_a,
only_b=only_b,
**select_fields
)
TODO
-
Transactions
-
Indexes
-
Date/time expressions
-
Window functions
Possible plans for the future (not determined yet)
-
Cache the compilation of repetitive queries for even faster query-building
-
Compile control flow, functions
-
Define tables using type-annotated classes (SQLModel style)
Alternatives
Thanks
Thanks to Datafold for having sponsored Sqeleton in its initial stages. For reference, the original repo.
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
File details
Details for the file sqeleton-0.1.6.tar.gz
.
File metadata
- Download URL: sqeleton-0.1.6.tar.gz
- Upload date:
- Size: 57.6 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: poetry/1.8.3 CPython/3.12.4 Darwin/23.6.0
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 2b0fd6330129a4280eff3b8f2f6dee73619a6dc13d95045ce72a549c2a04219c |
|
MD5 | fe8b076261edcf093d154026471dc21c |
|
BLAKE2b-256 | 6c3fc3d3e33025447d50cbaad517c0c7ce2c9ec4f7f0daeb6d4d6a78a5d9a12c |
File details
Details for the file sqeleton-0.1.6-py3-none-any.whl
.
File metadata
- Download URL: sqeleton-0.1.6-py3-none-any.whl
- Upload date:
- Size: 75.7 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: poetry/1.8.3 CPython/3.12.4 Darwin/23.6.0
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 8a794803ea57efa8d3f8536a3f4f5efdb45f32b976bf4894c3fa14e078a6c45f |
|
MD5 | 2d3f75e0e7f67fa06f844424c41cf852 |
|
BLAKE2b-256 | 2ae1dbff30e671ed06100e2a3bd8ca2552eea29f38de44650b430574c4f75941 |