DOCSEARCHbyAlgolia
Submit Docs

aiosql

repository·main·Indexed 23 days ago

https://github.com/nackjicholson/aiosql

A library for asynchronous SQL execution in Python (version 15.0). It allows loading SQL queries from files or strings using from_path() and from_str(), generating dynamic methods via the Queries class. It supports multiple database engines including Postgres, MySQL, MariaDB, and MS SQL Server through built-in and custom driver adapters. The library uses a specific comment syntax (-- name:) to define query names, parameters, and operation types such as SELECT_ONE, SELECT_VALUE, and mutations.

Tokens
8.4K
Snippets
16
Records
65
Agent score
80%

What's inside aiosql

  2. Implement a custom database driver adapter

    main

    If your driver is asynchronous or does not work with the default PEP 249 implementation, you must implement a custom adapter. An adapter is a duck-typed class that must follow one of two protocols defined in aiosql/types.py: the PEP 249 Synchronous Adapter or the Asynchronous Adapter.

    Key Implementation Details

    • Async Adapters: Must include the static class field is_aio_driver = True so aiosql knows to await method returns.
    • Internal Methods: _cursor is used to generate a cursor (allowing for driver-specific parameters during this phase).
    • SQL Preprocessing: process_sql handles preprocessing SQL queries and must manage named parameters as required by the target driver.
    • Core Operations: You must implement select, select_one, insert_update_delete, insert_update_delete_many, insert_returning, and execute_script.
    • Cursor Access: select_cursor returns the raw cursor from a select operation.

    Implementation Tips

    • For PEP 249 (synchronous) drivers, consider inheriting from aiosql.adapters.Generic.
    • For inspiration, examine the source code in aiosql/adapters/.

  3. Understand aiosql dependency management

    main

    For standard users, aiosql has no mandatory dependencies other than the specific database driver you choose to use.

    For developers, additional dependencies are required to test against various databases and drivers. The project also provides Dockerfiles in the docker/ subdirectory for testing against Postgres, MySQL, MariaDB, and MS SQL Server.

  4. Use named parameters and avoid positional arguments

    main

    In version 13.0 and later, AioSQL introduced stricter parameter handling to improve reliability:

    1. Named Parameter Declarations: You can add optional parameter declarations directly to your SQL queries.
    2. Positional Parameter Restriction: When named parameters are declared in a query, AioSQL forbids the use of positional parameters for that query. You must use the named arguments.
    3. kwargs_only: You can use the kwargs_only option to force the library to fail if simple positional arguments are passed instead of keyword arguments.

  5. How aiosql works: Organizing SQL in files

    main

    aiosql allows you to write parametric SQL queries in .sql files and load them into your Python application as callable methods. This keeps SQL code separate from Python logic, allowing you to use the same .sql files in other tools like psql or GUI clients.

    To define a method, use the -- name: method_name(optional_params) comment syntax above your SQL statement.

    Example greetings.sql:

    -- name: get_all_greetings()
-- Get all the greetings in the database
select greeting_id, greeting
  from greetings
 order by 1;

-- name: get_user_by_username(username)^ 
-- Get a user from the database using a named parameter
select user_id, username, name
  from users
  where username = :username;

    Note: Adding parameters in parentheses like (username) is optional but enforces their presence.

    -- name: get_all_greetings()
-- Get all the greetings in the database
select greeting_id, greeting
  from greetings
 order by 1;

-- name: get_user_by_username(username)^ 
-- Get a user from the database using a named parameter
select user_id, username, name
  from users
  where username = :username;

  6. Install development dependencies for aiosql

    main

    To support development and testing across different database drivers, install the [dev] extra along with specific database extras.

    Install the base development tools:

    pip install .[dev]

    Then, install the specific extras for the databases you intend to test against:

    • SQLite: .[dev-sqlite]
    • Postgres: .[dev-postgres]
    • DuckDB: .[dev-duckdb]
    • MySQL: .[dev-mysql]
    • MariaDB: .[dev-mariadb]
    # development tools
pip install .[dev]
# per-database stuff
pip install .[dev-sqlite]
pip install .[dev-postgres]
pip install .[dev-duckdb]
pip install .[dev-mysql]
pip install .[dev-mariadb]

  8. Run Docker clients against manually started servers

    main

    If you have database servers running manually (not via Docker Compose), you can run AioSQL client containers and point them to your host machine using --add-host=host.docker.internal:host-gateway and setting the appropriate host environment variable.

    Commands for different databases:

    • MariaDB/SQLite/DuckDB: Use python-aiosql-dbs with MA_HOST=host.docker.internal.
    • MySQL: Use python-aiosql-mysql with MY_HOST=host.docker.internal.
    • MS SQL Server: Use python-aiosql-dbs with MS_HOST=host.docker.internal.
    # Run against MariaDB
docker run -it -v .:/code --add-host=host.docker.internal:host-gateway \
  python-aiosql-dbs \
  make VENV=/venv MA_HOST=host.docker.internal check.pytest.mariadb.detached

# Run against MySQL
docker run -it -v .:/code --add-host=host.docker.internal:host-gateway \
  python-aiosql-mysql \
  make VENV=/venv MY_HOST=host.docker.internal check.pytest.mysql.detached

# Run against MS SQL Server
docker run -it -v .:/code --add-host=host.docker.internal:host-gateway \
  python-aiosql-dbs \
  make VENV=/venv MS_HOST=host.docker.internal check.pytest.mssql.detached

  9. Run tests and checks in aiosql

    main

    You can run the test suite using pytest directly, or use the provided Makefile to automate the process.

    Using pytest:

    pytest

    Using Makefile:

    1. Install the development virtual environment: make venv.dev
    2. Activate the environment: source venv/bin/activate
    3. Run all checks (pytest, flake8, coverage, etc.): make check
    pytest

# Alternatively using Makefile
make venv.dev
source venv/bin/activate
make check

  11. Set up a development environment for aiosql

    main

    To develop on aiosql, you should first clone the repository and create a manual virtual environment to isolate dependencies.

    Follow these steps:

    1. Clone the repository.
    2. Create and activate a Python virtual environment.
    3. Upgrade pip.

    All subsequent development steps assume you are working within this activated virtual environment.

    # get the project sources
git clone git@github.com:nackjicholson/aiosql.git
cd aiosql
# create a venv manually
python -m venv venv
source venv/bin/activate
pip install --upgrade pip

  12. Set up MS SQL Server via Docker

    main

    To run an MS SQL Server instance for testing, pull the Microsoft SQL Server image and run it with the required environment variables (ACCEPT_EULA, MSSQL_SA_PASSWORD, and MSSQL_PID).

    After starting the container, you can access the server using sqlcmd via docker exec.

    docker pull mcr.microsoft.com/mssql/server:2022-latest

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Abc123.." -e "MSSQL_PID=Developer" \
  -p 1433:1433 --name mssqltest --hostname mssqltest -d mcr.microsoft.com/mssql/server:2022-latest

# Access the server via sqlcmd
docker exec -it mssqltest /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P "Abc123.."