.. image:: https://img.shields.io/pypi/v/sqls
:alt: version
.. image:: https://img.shields.io/pypi/l/sqls
:alt: license
.. image:: https://img.shields.io/pypi/pyversions/sqls
:alt: python versions
.. image:: https://gitlab.com/durko/sqls/badges/master/pipeline.svg
:target: https://gitlab.com/durko/sqls/-/commits/master
:alt: pipeline status
.. image:: https://gitlab.com/durko/sqls/badges/master/coverage.svg
:target: https://gitlab.com/durko/sqls/-/commits/master
:alt: coverage report
====
SQLs
====
SQLs is a collection of libraries to interact with SQL databases. SQLs is not an object-relational mapper (ORM), but offers useful low-level primitives for handling transactions, defining a data model, and formulating queries in Python. Its main design goal is helping developers write SQL in idiomatic, type-checked Python, while being fast and efficient on runtime.
Getting started
===============
For more, see the `documentation <https://durko.gitlab.io/sqls/>`_.
SQLs is published on PyPI and does not have any special dependencies. Simply install with pip::
pip install sqls
Dependencies on database interface libraries are strictly optional. If you want to speak to a specific SQL implementation use any of::
pip install sqls[mysql]
pip install sqls[postgresql]
pip install sqls[sqlite]
pip install sqls[mysql,postgre,sqlite]
Connect to a database
=====================
Asynchronous transaction managers from ``sqls.transactions`` handle SQL database connections:
.. code-block:: python
from sqls.transactions import get_manager
async def application() -> list[tuple[int]]:
# Create a transaction manager.
manager = get_manager('file:///path/to/sqlite.db')
# Initialize database connections.
await manager.init()
# Open transaction.
async with manager.txn() as txn:
# Execute query.
return await txn.execute('SELECT 1')
# Close database connections.
await manager.close()
All SQL statements inside the asynchronous context manager are executed in one single transaction. Uncaught exceptions in the context will cause the transaction to be automatically rolled back, on regular exit the transaction will automatically be commited.
Define a data model
===================
The data model is defined through annotated Python dataclasses and the ``Model`` base class from ``sqls.models``.
Basic usage
-----------
The syntax uses builtin Python primitives to express the rich details of SQL types:
.. code-block:: python
from dataclasses import dataclass
from typing import Annotated
from sqls.models import CharField, Fieldmeta, IntegerField, Model
@dataclass
class User(Model):
"""A user model."""
# Names are unique.
name: Annotated[CharField, Fieldmeta(max_length=32, unique=True)]
# Passwords are nullable.
password: Annotated[CharField, Fieldmeta(max_length=128)] | None
# Use just a plain integer.
time_created: IntegerField
The Model base class automatically adds an integer primary key ``id`` field.
Relationships
-------------
Relationships are expressed through annotations on fields that store the actual information:
.. code-block:: python
@dataclass
class User(Model):
"""Same as above, add some relationships."""
# Table name and field are inferred from the attribute name.
company_id: ForeignKeyField
# Table name and field are explicitly set though Fieldmeta.
team_id: Annotated[ForeignKeyField, Fieldmeta(foreign_key=('department', 'id'))
Many-to-many relationships cannot be expressed on the related models themselves, the table needs to be defined explicitly:
.. code-block:: python
@dataclass
class UserGroup(Model):
"""User group relationship."""
# Disable automatic injection of id field.
id: None
user_id: ForeignKeyField
group_id: ForeignKeyField
Create database tables
======================
The ``sqls.models`` package can generate ``CREATE TABLE`` queries from model definitions:
.. code-block:: python
from sqls.models import get_create_queries
# Inside a transaction context (txn) create tables for User and Group.
for query in get_create_queries([User, UserGroup, Group]):
# Execute generated query with txn.exq.
await txn.exq(query)
Build queries
=============
The ``sqls.queries`` package helps writing queries in idiomatic python:
.. code-block:: python
from sqls.queries import Query, Table
# Create Table object from sql table name.
user_t = Table('user')
# Create query for id and password of one specific user.
query = (
Query
.select(
user_t.id.typed(int),
user_t.password.typed(str | None),
)
.from_(user_t)
.where(user_t.name == 'Ringo')
)
As SQLs is not an ORM, ``Query`` knows nothing about the data model. By expressing the expected return type of the id field with ``.typed(int)`` static typed checkers like `mypy <https://mypy-lang.org/>`_ are able to infer the return types when the query is executed.
Development
===========
Clone the repository and setup your local checkout::
git clone https://gitlab.com/durko/sqls.git
cd sqls
python -m venv venv
. venv/bin/activate
pip install -r requirements-dev.txt
pip install -e .
This creates a new virtual environment with the necessary python dependencies and installs SQLs in editable mode. The SQLs code base uses pytest as its test runner, run the test suite by simply invoking::
pytest
To build the documentation from its source run sphinx-build::
sphinx-build -a docs build/public
The entry point to the local documentation build should be available under ``build/public/index.html``.
Raw data
{
"_id": null,
"home_page": null,
"name": "sqls",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "aio, async, database, mariadb, model, mysql, query, postgresql, sql, sqlite3, transaction",
"author": null,
"author_email": "Marko Durkovic <marko@miding.de>",
"download_url": "https://files.pythonhosted.org/packages/70/83/7a416b788b8954fcee5c7d6e0a65401c6cde145b5a42a7ac54cf53336195/sqls-0.0.101.tar.gz",
"platform": null,
"description": ".. image:: https://img.shields.io/pypi/v/sqls\n :alt: version\n\n.. image:: https://img.shields.io/pypi/l/sqls\n :alt: license\n\n.. image:: https://img.shields.io/pypi/pyversions/sqls\n :alt: python versions\n\n.. image:: https://gitlab.com/durko/sqls/badges/master/pipeline.svg\n :target: https://gitlab.com/durko/sqls/-/commits/master\n :alt: pipeline status\n\n.. image:: https://gitlab.com/durko/sqls/badges/master/coverage.svg\n :target: https://gitlab.com/durko/sqls/-/commits/master\n :alt: coverage report\n\n\n====\nSQLs\n====\n\nSQLs is a collection of libraries to interact with SQL databases. SQLs is not an object-relational mapper (ORM), but offers useful low-level primitives for handling transactions, defining a data model, and formulating queries in Python. Its main design goal is helping developers write SQL in idiomatic, type-checked Python, while being fast and efficient on runtime.\n\n\nGetting started\n===============\n\nFor more, see the `documentation <https://durko.gitlab.io/sqls/>`_.\n\nSQLs is published on PyPI and does not have any special dependencies. Simply install with pip::\n\n pip install sqls\n\nDependencies on database interface libraries are strictly optional. If you want to speak to a specific SQL implementation use any of::\n\n pip install sqls[mysql]\n pip install sqls[postgresql]\n pip install sqls[sqlite]\n pip install sqls[mysql,postgre,sqlite]\n\n\nConnect to a database\n=====================\n\nAsynchronous transaction managers from ``sqls.transactions`` handle SQL database connections:\n\n.. code-block:: python\n\n from sqls.transactions import get_manager\n\n async def application() -> list[tuple[int]]:\n # Create a transaction manager.\n manager = get_manager('file:///path/to/sqlite.db')\n\n # Initialize database connections.\n await manager.init()\n\n # Open transaction.\n async with manager.txn() as txn:\n\n # Execute query.\n return await txn.execute('SELECT 1')\n\n # Close database connections.\n await manager.close()\n\n\nAll SQL statements inside the asynchronous context manager are executed in one single transaction. Uncaught exceptions in the context will cause the transaction to be automatically rolled back, on regular exit the transaction will automatically be commited.\n\n\nDefine a data model\n===================\n\nThe data model is defined through annotated Python dataclasses and the ``Model`` base class from ``sqls.models``.\n\nBasic usage\n-----------\n\nThe syntax uses builtin Python primitives to express the rich details of SQL types:\n\n.. code-block:: python\n\n from dataclasses import dataclass\n from typing import Annotated\n\n from sqls.models import CharField, Fieldmeta, IntegerField, Model\n\n @dataclass\n class User(Model):\n \"\"\"A user model.\"\"\"\n\n # Names are unique.\n name: Annotated[CharField, Fieldmeta(max_length=32, unique=True)]\n\n # Passwords are nullable.\n password: Annotated[CharField, Fieldmeta(max_length=128)] | None\n\n # Use just a plain integer.\n time_created: IntegerField\n\nThe Model base class automatically adds an integer primary key ``id`` field.\n\nRelationships\n-------------\n\nRelationships are expressed through annotations on fields that store the actual information:\n\n.. code-block:: python\n\n @dataclass\n class User(Model):\n \"\"\"Same as above, add some relationships.\"\"\"\n\n # Table name and field are inferred from the attribute name.\n company_id: ForeignKeyField\n\n # Table name and field are explicitly set though Fieldmeta.\n team_id: Annotated[ForeignKeyField, Fieldmeta(foreign_key=('department', 'id'))\n\nMany-to-many relationships cannot be expressed on the related models themselves, the table needs to be defined explicitly:\n\n.. code-block:: python\n\n @dataclass\n class UserGroup(Model):\n \"\"\"User group relationship.\"\"\"\n\n # Disable automatic injection of id field.\n id: None\n user_id: ForeignKeyField\n group_id: ForeignKeyField\n\n\nCreate database tables\n======================\n\nThe ``sqls.models`` package can generate ``CREATE TABLE`` queries from model definitions:\n\n.. code-block:: python\n\n from sqls.models import get_create_queries\n\n # Inside a transaction context (txn) create tables for User and Group.\n for query in get_create_queries([User, UserGroup, Group]):\n # Execute generated query with txn.exq.\n await txn.exq(query)\n\n\nBuild queries\n=============\n\nThe ``sqls.queries`` package helps writing queries in idiomatic python:\n\n.. code-block:: python\n\n from sqls.queries import Query, Table\n\n # Create Table object from sql table name.\n user_t = Table('user')\n\n # Create query for id and password of one specific user.\n query = (\n Query\n .select(\n user_t.id.typed(int),\n user_t.password.typed(str | None),\n )\n .from_(user_t)\n .where(user_t.name == 'Ringo')\n )\n\nAs SQLs is not an ORM, ``Query`` knows nothing about the data model. By expressing the expected return type of the id field with ``.typed(int)`` static typed checkers like `mypy <https://mypy-lang.org/>`_ are able to infer the return types when the query is executed.\n\nDevelopment\n===========\n\nClone the repository and setup your local checkout::\n\n git clone https://gitlab.com/durko/sqls.git\n\n cd sqls\n python -m venv venv\n . venv/bin/activate\n\n pip install -r requirements-dev.txt\n pip install -e .\n\n\nThis creates a new virtual environment with the necessary python dependencies and installs SQLs in editable mode. The SQLs code base uses pytest as its test runner, run the test suite by simply invoking::\n\n pytest\n\n\nTo build the documentation from its source run sphinx-build::\n\n sphinx-build -a docs build/public\n\n\nThe entry point to the local documentation build should be available under ``build/public/index.html``.\n",
"bugtrack_url": null,
"license": "Apache-2.0",
"summary": "SQL Toolkit for Python.",
"version": "0.0.101",
"project_urls": {
"Changelog": "https://gitlab.com/durko/sqls/-/blob/master/CHANGES.rst",
"Documentation": "https://durko.gitlab.io/sqls",
"Homepage": "https://gitlab.com/durko/sqls",
"Issues": "https://gitlab.com/durko/sqls/issues",
"Source": "https://gitlab.com/durko/sqls"
},
"split_keywords": [
"aio",
" async",
" database",
" mariadb",
" model",
" mysql",
" query",
" postgresql",
" sql",
" sqlite3",
" transaction"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fc8304d5029e53418ace508389c2c6c76d7bfe0183281d6386ff2be9f35822b2",
"md5": "318b9927a29ea18f37b2a07332f6ade6",
"sha256": "a08568e8adfeceabf2121da543d79b476eb16b5bacd746c4d451811fcdab06f3"
},
"downloads": -1,
"filename": "sqls-0.0.101-py3-none-any.whl",
"has_sig": false,
"md5_digest": "318b9927a29ea18f37b2a07332f6ade6",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 48072,
"upload_time": "2024-11-18T17:22:00",
"upload_time_iso_8601": "2024-11-18T17:22:00.919654Z",
"url": "https://files.pythonhosted.org/packages/fc/83/04d5029e53418ace508389c2c6c76d7bfe0183281d6386ff2be9f35822b2/sqls-0.0.101-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "70837a416b788b8954fcee5c7d6e0a65401c6cde145b5a42a7ac54cf53336195",
"md5": "69a69349211e98bf1c6d26e79d03911a",
"sha256": "d5c671e9c62bc95c0d9ba13b54e530bea121c73ebe0abfc695b8ae0313db9202"
},
"downloads": -1,
"filename": "sqls-0.0.101.tar.gz",
"has_sig": false,
"md5_digest": "69a69349211e98bf1c6d26e79d03911a",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 77695,
"upload_time": "2024-11-18T17:22:02",
"upload_time_iso_8601": "2024-11-18T17:22:02.846748Z",
"url": "https://files.pythonhosted.org/packages/70/83/7a416b788b8954fcee5c7d6e0a65401c6cde145b5a42a7ac54cf53336195/sqls-0.0.101.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-11-18 17:22:02",
"github": false,
"gitlab": true,
"bitbucket": false,
"codeberg": false,
"gitlab_user": "durko",
"gitlab_project": "sqls",
"lcname": "sqls"
}
JSON