dapper_sqls

Python module for working with SQL Server and SQLite, providing synchronous and asynchronous access, typed models, query builders, and integrated HTTP clients.

Features

• Dapper (sync) and AsyncDapper — Execute queries and stored procedures with pyodbc (SQL Server) and configurable retry.
• Base models — TableBaseModel, ViewBaseModel, StpBaseModel with Pydantic, sensitive fields, and diff/comparison utilities.
• QueryField — Typed construction of SELECT, JOIN, ORDER BY, HAVING, and query options.
• Builders — QueryBuilder, StoredBuilder, StpBuilder, ViewBuilder (sync/async) and schema-based model generator.
• Migration — DataMigrator to migrate and compare data from SQL Server to SQLite (all tables or selected tables).
• HTTP — Synchronous (Request) and asynchronous (AioHttp) clients with models and decorators.
• Local SQLite — DataBaseInstall, BaseLocalDatabase, BaseAsyncLocalDatabase, safety decorators, and utilities.
• Decorators — func_validation and async_func_validation for input/output validation.
• Result — Result wrappers (Count, Fetchone, Fetchall, Insert, Send) and join/SQL helpers.

Requirements

• Python 3.8+
• For SQL Server: pyodbc (sync) and aioodbc (async)
• For SQLite: aiosqlite (async)
• Pydantic, aiohttp, requests, and other dependencies listed in pyproject.toml

Installation

pip install dapper_sqls

Quick start

Dapper (SQL Server, synchronous)

from dapper_sqls import Dapper

dapper = Dapper(
    server="localhost",
    database="MyDatabase",
    username="user",
    password="password",
)

# Direct query
result = dapper.query().execute("SELECT 1 AS num")
rows = result.fetchall()

# Stored procedure
stored = dapper.stored().execute("usp_MyProcedure", param1="value")

AsyncDapper (SQL Server, asynchronous)

from dapper_sqls import AsyncDapper

dapper = AsyncDapper(server="...", database="...", username="...", password="...")

async def main():
    result = await dapper.query().execute("SELECT * FROM Table")
    rows = result.fetchall()

Models and builders

from dapper_sqls import TableBaseModel, ViewBaseModel, StpBaseModel, QueryField

class MyTable(TableBaseModel):
    id: int
    name: str

# Builders for queries, views, and stored procedures
from dapper_sqls import ModelBuilder, StpBuilder, ViewBuilder

Local SQLite

from dapper_sqls.sqlite import BaseLocalDatabase, DataBaseInstall, BaseTables

# Database setup/creation
installer = DataBaseInstall(
    app_name="my_app",
    tables=BaseTables,
    path_local_database=".",
    database_name="my_local",
    database_folder_name="data",
)

# Using the synchronous base
db = installer.instance(BaseLocalDatabase)

Data migration (SQL Server -> SQLite)

from dapper_sqls import Dapper
from dapper_sqls.builders.generator.migration import DataMigrator

dapper = Dapper(server="localhost", database="MyDatabase", username="user", password="password")

migrator = DataMigrator(
    dapper.config.connectionStringDataQuery,
    sqlite_db_path="data/my_local.db",
    sql_version=dapper.config.sql_version,
)

# Migrate all tables
result = migrator.migrate_all(clear_existing=False)

# Compare row counts between SQL Server and SQLite
stats = migrator.get_table_statistics()

Validation with decorators

from dapper_sqls import func_validation, async_func_validation

@func_validation
def my_function(x: int) -> str:
    return str(x)

Project structure

dapper_sqls/
├── dapper/           # Synchronous Dapper and executors (Query, Stored)
├── async_dapper/     # AsyncDapper and async executors
├── models/           # Base (table, view, stp), query_field, result, connection, converter
├── builders/         # Query, Stored, Stp, View and model generator
├── http/             # Request, AioHttp, models and HTTP decorators
├── sqlite/           # Installer, BaseLocalDatabase, BaseAsyncLocalDatabase, decorators
├── config.py         # Connection and retry configuration
├── decorators.py     # func_validation, async_func_validation
└── utils.py          # General utilities

Configuration

• SQL Server connection: server, database, username, password; optionally sql_version, api_environment, default_attempts, default_wait_timeout.

License

MIT

Author

Samuel Semedo