Skip to main content

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 called name.

Documentation

Read the docs!

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


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqeleton-0.1.6.tar.gz (57.6 kB view details)

Uploaded Source

Built Distribution

sqeleton-0.1.6-py3-none-any.whl (75.7 kB view details)

Uploaded Python 3

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

Hashes for sqeleton-0.1.6.tar.gz
Algorithm Hash digest
SHA256 2b0fd6330129a4280eff3b8f2f6dee73619a6dc13d95045ce72a549c2a04219c
MD5 fe8b076261edcf093d154026471dc21c
BLAKE2b-256 6c3fc3d3e33025447d50cbaad517c0c7ce2c9ec4f7f0daeb6d4d6a78a5d9a12c

See more details on using hashes here.

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

Hashes for sqeleton-0.1.6-py3-none-any.whl
Algorithm Hash digest
SHA256 8a794803ea57efa8d3f8536a3f4f5efdb45f32b976bf4894c3fa14e078a6c45f
MD5 2d3f75e0e7f67fa06f844424c41cf852
BLAKE2b-256 2ae1dbff30e671ed06100e2a3bd8ca2552eea29f38de44650b430574c4f75941

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page