
aiosql
Simple SQL in Python
Stars: 1290

aiosql is a Python module that allows you to organize SQL statements in .sql files and load them into your Python application as methods to call. It supports various database drivers like SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB. The project is an implementation of Kris Jenkins' yesql library to the Python ecosystem, allowing users to easily reuse SQL code in SQL GUIs or CLI tools. With aiosql, you can write, version control, comment, and run SQL code using files without losing the ability to use them as you would any other SQL file. It provides support for PEP 249 and asyncio based drivers, enabling users to execute parametric SQL queries from Python methods.
README:
SQL <https://en.wikipedia.org/wiki/SQL>
__ is code.
Write it, version control it, comment it, and run it using files.
Writing your SQL code in Python programs as strings doesn't allow you to easily
reuse them in SQL GUIs or CLI tools like psql
.
With aiosql you can organize your SQL statements in .sql files, load them
into your python application as methods to call without losing the ability to
use them as you would any other SQL file.
This project supports standard
PEP 249 <https://peps.python.org/pep-0249/>
__
and
asyncio <https://docs.python.org/3/library/asyncio.html>
__
based drivers for
SQLite <https://www.sqlite.org/>
__
(sqlite3 <https://docs.python.org/3/library/sqlite3.html>
,
aiosqlite <https://aiosqlite.omnilib.dev/en/latest/?badge=latest>
,
apsw <https://pypi.org/project/apsw/>
),
PostgreSQL <https://postgresql.org/>
(psycopg (3) <https://www.psycopg.org/psycopg3/>
,
psycopg2 <https://www.psycopg.org/docs/>
,
pg8000 <https://pypi.org/project/pg8000/>
,
pygresql <http://www.pygresql.org/>
,
asyncpg <https://magicstack.github.io/asyncpg/current/>
),
MySQL <https://www.mysql.com/>
(PyMySQL <https://github.com/PyMySQL/PyMySQL/>
,
mysqlclient <https://pypi.org/project/mysqlclient/>
,
mysql-connector <https://dev.mysql.com/doc/connector-python/en/>
,
asyncmy <https://github.com/long2ice/asyncmy>
with
this adapter <https://github.com/GoogleCloudPlatform/database-assessment/blob/main/src/dma/lib/db/adapters/asyncmy.py>
),
MariaDB <https://mariadb.org/>
(mariadb <https://pypi.org/project/mariadb/>
),
DuckDB <https://www.duckdb.org/>
(duckdb <https://duckdb.org/docs/api/python/dbapi>
) and
MS SQL Server <https//en.wikipedia.org/wiki/Microsoft_SQL_Server>
(pymssql <https://pypi.org/project/pymssql/>
__),
However, some detailed feature support may vary depending on the underlying driver
and database engine actual capabilities.
Other SQL database drivers which support the pyformat
or named
PEP 249 <https://peps.python.org/pep-0249/>
__ paramstyles should work as well
by just passing the driver as a parameter when building queries. Thus
Oracle Database <https://en.wikipedia.org/wiki/Oracle_Database>
__
(oracledb <https://oracle.github.io/python-oracledb/>
) or
Snowflake <https://en.wikipedia.org/wiki/Snowflake_Inc.>
(snowflake.connector <https://docs.snowflake.com/en/developer-guide/python-connector/python-connector>
__)
should work out of the box…
Please report with an issue if it actually works for you!
Otherwise, extensions to support other database drivers can be written by you!
See: Database Driver Adapters <./database-driver-adapters.html>
__.
Feel free to pull request!
This module is an implementation of
Kris Jenkins' yesql <https://github.com/krisajenkins/yesql>
__
Clojure <https://clojure.org/>
__ library to the
Python <https://www.python.org/>
__
ecosystem <https://pypi.org/>
__.
.. NOTE :target: is needed so that github renders badges on a line. .. image:: https://github.com/nackjicholson/aiosql/actions/workflows/aiosql-package.yml/badge.svg?branch=main&style=flat :alt: Build status :target: https://github.com/nackjicholson/aiosql/actions/ .. NOTE hardcoded, this is maintained manually. .. image:: https://img.shields.io/badge/coverage-100%25-success :alt: Code Coverage :target: https://github.com/nackjicholson/aiosql/actions/ .. NOTE all tests
loading: 15 patterns: 5
sqlite3: 17 apsw: 16 duckdb: 14 mariadb: 17 pymysql: 16 mysqldb: 15 myco: 16 pymssql: 16 pg8000: 14 psycopg2: 18 psycopg3: 19 pygresql: 15
aiosqlite: 13 asyncpg: 18 .. image:: https://img.shields.io/badge/tests-244%20✓-success :alt: Tests :target: https://github.com/nackjicholson/aiosql/actions/ .. image:: https://img.shields.io/github/issues/nackjicholson/aiosql?style=flat :alt: Issues :target: https://github.com/nackjicholson/aiosql/issues/ .. image:: https://img.shields.io/github/contributors/nackjicholson/aiosql :alt: Contributors :target: https://github.com/nackjicholson/aiosql/graphs/contributors .. image:: https://img.shields.io/pypi/dm/aiosql?style=flat :alt: Pypi Downloads :target: https://pypistats.org/packages/aiosql .. image:: https://img.shields.io/github/stars/nackjicholson/aiosql?style=flat&label=Star :alt: Stars :target: https://github.com/nackjicholson/aiosql/stargazers .. image:: https://img.shields.io/pypi/v/aiosql :alt: Version :target: https://pypi.org/project/aiosql/ .. image:: https://img.shields.io/github/languages/code-size/nackjicholson/aiosql?style=flat :alt: Code Size :target: https://github.com/nackjicholson/aiosql/ .. image:: https://img.shields.io/badge/databases-6-informational :alt: Databases :target: https://github.com/nackjicholson/aiosql/ .. image:: https://img.shields.io/badge/drivers-15-informational :alt: Drivers :target: https://github.com/nackjicholson/aiosql/ .. image:: https://img.shields.io/github/languages/count/nackjicholson/aiosql?style=flat :alt: Language Count :target: https://en.wikipedia.org/wiki/Programming_language .. image:: https://img.shields.io/github/languages/top/nackjicholson/aiosql?style=flat :alt: Top Language :target: https://en.wikipedia.org/wiki/Python_(programming_language) .. image:: https://img.shields.io/pypi/pyversions/aiosql?style=flat :alt: Python Versions :target: https://www.python.org/ .. NOTE some non-sense badge about badges:-) .. image:: https://img.shields.io/badge/badges-16-informational :alt: Badges :target: https://shields.io/ .. image:: https://img.shields.io/pypi/l/aiosql?style=flat :alt: BSD 2-Clause License :target: https://opensource.org/licenses/BSD-2-Clause
Install from pypi <https://pypi.org/project/aiosql>
__, for instance by running pip install aiosql
.
Then write parametric SQL queries in a file and execute it from Python methods, eg this greetings.sql file:
.. code:: 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^
-- Get a user from the database using a named parameter
select user_id, username, name
from users
where username = :username;
This example has an imaginary SQLite database with greetings and users. It prints greetings in various languages to the user and showcases the basic feature of being able to load queries from a SQL file and call them by name in python code.
You can use aiosql
to load the queries in this file for use in your Python
application:
.. code:: python
import aiosql
import sqlite3
queries = aiosql.from_path("greetings.sql", "sqlite3")
with sqlite3.connect("greetings.db") as conn:
user = queries.get_user_by_username(conn, username="willvaughn")
# user: (1, "willvaughn", "William")
for _, greeting in queries.get_all_greetings(conn):
# scan: (1, "Hi"), (2, "Aloha"), (3, "Hola"), …
print(f"{greeting}, {user[2]}!")
# Hi, William!
# Aloha, William!
# …
Or even in an asynchroneous way, with two SQL queries running in parallel
using aiosqlite
and asyncio
:
.. code:: python
import asyncio
import aiosql
import aiosqlite
queries = aiosql.from_path("greetings.sql", "aiosqlite")
async def main():
# Parallel queries!!!
async with aiosqlite.connect("greetings.db") as conn:
greetings, user = await asyncio.gather(
queries.get_all_greetings(conn),
queries.get_user_by_username(conn, username="willvaughn")
)
for _, greeting in greetings:
print(f"{greeting}, {user[2]}!")
asyncio.run(main())
It may seem inconvenient to provide a connection on each call.
You may have a look at the AnoDB <https://github.com/zx80/anodb>
__ DB
class which wraps both a database connection and queries in one
connection-like extended object, including managing a pool and performing
automatic reconnection if needed.
- You think SQL is pretty good, and writing SQL is an important part of your applications.
- You don't want to write your SQL in strings intermixed with your python code.
- You're not using an ORM like
SQLAlchemy <https://www.sqlalchemy.org/>
__ orDjango <https://www.djangoproject.com/>
__ , with large (100k lines) code imprints vs under 900 foraiosql
, and you don't need to. - You want to be able to reuse your SQL in other contexts.
Loading it into
psql
or other database tools.
- You're looking for an
ORM <https://en.wikipedia.org/wiki/Object-relational_mapping>
__. - You aren't comfortable writing SQL code.
- You don't have anything in your application that requires complicated SQL beyond basic CRUD operations.
- Dynamically loaded objects built at runtime really bother you.
For Tasks:
Click tags to check more tools for each tasksFor Jobs:
Alternative AI tools for aiosql
Similar Open Source Tools

aiosql
aiosql is a Python module that allows you to organize SQL statements in .sql files and load them into your Python application as methods to call. It supports various database drivers like SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB. The project is an implementation of Kris Jenkins' yesql library to the Python ecosystem, allowing users to easily reuse SQL code in SQL GUIs or CLI tools. With aiosql, you can write, version control, comment, and run SQL code using files without losing the ability to use them as you would any other SQL file. It provides support for PEP 249 and asyncio based drivers, enabling users to execute parametric SQL queries from Python methods.

aiohttp-debugtoolbar
aiohttp_debugtoolbar provides a debug toolbar for aiohttp web applications. It is a port of pyramid_debugtoolbar and offers basic functionality such as basic panels, intercepting redirects, pretty printing exceptions, an interactive python console, and showing source code. The library is still in early development stages and offers various debug panels for monitoring different aspects of the web application. It is a useful tool for developers working with aiohttp to debug and optimize their applications.

aioesphomeapi
aioesphomeapi allows you to interact with devices flashed with ESPHome. ESPHome is an open-source firmware that allows you to control your devices over Wi-Fi or Ethernet. With aioesphomeapi, you can connect to your ESPHome devices, retrieve their status, and control them from your Python code.

aiosmtpd
aiosmtpd is an asyncio-based SMTP server implementation that provides a modern and efficient way to handle SMTP and LMTP protocols in Python 3. It replaces the outdated asyncore and asynchat modules with asyncio for improved asynchronous I/O operations. The project aims to offer a more user-friendly, extendable, and maintainable solution for handling email protocols in Python applications. It is actively maintained by experienced Python developers and offers full documentation for easy integration and usage.

pentagi
PentAGI is an innovative tool for automated security testing that leverages cutting-edge artificial intelligence technologies. It is designed for information security professionals, researchers, and enthusiasts who need a powerful and flexible solution for conducting penetration tests. The tool provides secure and isolated operations in a sandboxed Docker environment, fully autonomous AI-powered agent for penetration testing steps, a suite of 20+ professional security tools, smart memory system for storing research results, web intelligence for gathering information, integration with external search systems, team delegation system, comprehensive monitoring and reporting, modern interface, API integration, persistent storage, scalable architecture, self-hosted solution, flexible authentication, and quick deployment through Docker Compose.

MCPSharp
MCPSharp is a .NET library that helps build Model Context Protocol (MCP) servers and clients for AI assistants and models. It allows creating MCP-compliant tools, connecting to existing MCP servers, exposing .NET methods as MCP endpoints, and handling MCP protocol details seamlessly. With features like attribute-based API, JSON-RPC support, parameter validation, and type conversion, MCPSharp simplifies the development of AI capabilities in applications through standardized interfaces.

lollms
LoLLMs Server is a text generation server based on large language models. It provides a Flask-based API for generating text using various pre-trained language models. This server is designed to be easy to install and use, allowing developers to integrate powerful text generation capabilities into their applications.

fetcher-mcp
Fetcher MCP is a server tool designed for fetching web page content using Playwright headless browser. It supports JavaScript execution, intelligent content extraction, flexible output formats, parallel processing, resource optimization, robust error handling, and configurable parameters. The tool provides features like fetching web page content from a specified URL, batch retrieving content from multiple URLs, and offers fine-grained control over various parameters. Fetcher MCP is ideal for users looking to scrape dynamic web content efficiently and reliably.

code2prompt
Code2Prompt is a powerful command-line tool that generates comprehensive prompts from codebases, designed to streamline interactions between developers and Large Language Models (LLMs) for code analysis, documentation, and improvement tasks. It bridges the gap between codebases and LLMs by converting projects into AI-friendly prompts, enabling users to leverage AI for various software development tasks. The tool offers features like holistic codebase representation, intelligent source tree generation, customizable prompt templates, smart token management, Gitignore integration, flexible file handling, clipboard-ready output, multiple output options, and enhanced code readability.

hugging-chat-api
Unofficial HuggingChat Python API for creating chatbots, supporting features like image generation, web search, memorizing context, and changing LLMs. Users can log in, chat with the ChatBot, perform web searches, create new conversations, manage conversations, switch models, get conversation info, use assistants, and delete conversations. The API also includes a CLI mode with various commands for interacting with the tool. Users are advised not to use the application for high-stakes decisions or advice and to avoid high-frequency requests to preserve server resources.

well-architected-iac-analyzer
Well-Architected Infrastructure as Code (IaC) Analyzer is a project demonstrating how generative AI can evaluate infrastructure code for alignment with best practices. It features a modern web application allowing users to upload IaC documents, complete IaC projects, or architecture diagrams for assessment. The tool provides insights into infrastructure code alignment with AWS best practices, offers suggestions for improving cloud architecture designs, and can generate IaC templates from architecture diagrams. Users can analyze CloudFormation, Terraform, or AWS CDK templates, architecture diagrams in PNG or JPEG format, and complete IaC projects with supporting documents. Real-time analysis against Well-Architected best practices, integration with AWS Well-Architected Tool, and export of analysis results and recommendations are included.

py-llm-core
PyLLMCore is a light-weighted interface with Large Language Models with native support for llama.cpp, OpenAI API, and Azure deployments. It offers a Pythonic API that is simple to use, with structures provided by the standard library dataclasses module. The high-level API includes the assistants module for easy swapping between models. PyLLMCore supports various models including those compatible with llama.cpp, OpenAI, and Azure APIs. It covers use cases such as parsing, summarizing, question answering, hallucinations reduction, context size management, and tokenizing. The tool allows users to interact with language models for tasks like parsing text, summarizing content, answering questions, reducing hallucinations, managing context size, and tokenizing text.

mcpdoc
The MCP LLMS-TXT Documentation Server is an open-source server that provides developers full control over tools used by applications like Cursor, Windsurf, and Claude Code/Desktop. It allows users to create a user-defined list of `llms.txt` files and use a `fetch_docs` tool to read URLs within these files, enabling auditing of tool calls and context returned. The server supports various applications and provides a way to connect to them, configure rules, and test tool calls for tasks related to documentation retrieval and processing.

mcphost
MCPHost is a CLI host application that enables Large Language Models (LLMs) to interact with external tools through the Model Context Protocol (MCP). It acts as a host in the MCP client-server architecture, allowing language models to access external tools and data sources, maintain consistent context across interactions, and execute commands safely. The tool supports interactive conversations with Claude 3.5 Sonnet and Ollama models, multiple concurrent MCP servers, dynamic tool discovery and integration, configurable server locations and arguments, and a consistent command interface across model types.

claim-ai-phone-bot
AI-powered call center solution with Azure and OpenAI GPT. The bot can answer calls, understand the customer's request, and provide relevant information or assistance. It can also create a todo list of tasks to complete the claim, and send a report after the call. The bot is customizable, and can be used in multiple languages.

laravel-ai-translator
Laravel AI Translator is a powerful tool designed to streamline the localization process in Laravel projects. It automates the task of translating strings across multiple languages using advanced AI models like GPT-4 and Claude. The tool supports custom language styles, preserves variables and nested structures, and ensures consistent tone and style across translations. It integrates seamlessly with Laravel projects, making internationalization easier and more efficient. Users can customize translation rules, handle large language files efficiently, and validate translations for accuracy. The tool offers contextual understanding, linguistic precision, variable handling, smart length adaptation, and tone consistency for intelligent translations.
For similar tasks

django-ai-assistant
Combine the power of LLMs with Django's productivity to build intelligent applications. Let AI Assistants call methods from Django's side and do anything your users need! Use AI Tool Calling and RAG with Django to easily build state of the art AI Assistants.

aiosql
aiosql is a Python module that allows you to organize SQL statements in .sql files and load them into your Python application as methods to call. It supports various database drivers like SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB. The project is an implementation of Kris Jenkins' yesql library to the Python ecosystem, allowing users to easily reuse SQL code in SQL GUIs or CLI tools. With aiosql, you can write, version control, comment, and run SQL code using files without losing the ability to use them as you would any other SQL file. It provides support for PEP 249 and asyncio based drivers, enabling users to execute parametric SQL queries from Python methods.

dataline
DataLine is an AI-driven data analysis and visualization tool designed for technical and non-technical users to explore data quickly. It offers privacy-focused data storage on the user's device, supports various data sources, generates charts, executes queries, and facilitates report building. The tool aims to speed up data analysis tasks for businesses and individuals by providing a user-friendly interface and natural language querying capabilities.
For similar jobs

resonance
Resonance is a framework designed to facilitate interoperability and messaging between services in your infrastructure and beyond. It provides AI capabilities and takes full advantage of asynchronous PHP, built on top of Swoole. With Resonance, you can: * Chat with Open-Source LLMs: Create prompt controllers to directly answer user's prompts. LLM takes care of determining user's intention, so you can focus on taking appropriate action. * Asynchronous Where it Matters: Respond asynchronously to incoming RPC or WebSocket messages (or both combined) with little overhead. You can set up all the asynchronous features using attributes. No elaborate configuration is needed. * Simple Things Remain Simple: Writing HTTP controllers is similar to how it's done in the synchronous code. Controllers have new exciting features that take advantage of the asynchronous environment. * Consistency is Key: You can keep the same approach to writing software no matter the size of your project. There are no growing central configuration files or service dependencies registries. Every relation between code modules is local to those modules. * Promises in PHP: Resonance provides a partial implementation of Promise/A+ spec to handle various asynchronous tasks. * GraphQL Out of the Box: You can build elaborate GraphQL schemas by using just the PHP attributes. Resonance takes care of reusing SQL queries and optimizing the resources' usage. All fields can be resolved asynchronously.

aiogram_bot_template
Aiogram bot template is a boilerplate for creating Telegram bots using Aiogram framework. It provides a solid foundation for building robust and scalable bots with a focus on code organization, database integration, and localization.

pluto
Pluto is a development tool dedicated to helping developers **build cloud and AI applications more conveniently** , resolving issues such as the challenging deployment of AI applications and open-source models. Developers are able to write applications in familiar programming languages like **Python and TypeScript** , **directly defining and utilizing the cloud resources necessary for the application within their code base** , such as AWS SageMaker, DynamoDB, and more. Pluto automatically deduces the infrastructure resource needs of the app through **static program analysis** and proceeds to create these resources on the specified cloud platform, **simplifying the resources creation and application deployment process**.

pinecone-ts-client
The official Node.js client for Pinecone, written in TypeScript. This client library provides a high-level interface for interacting with the Pinecone vector database service. With this client, you can create and manage indexes, upsert and query vector data, and perform other operations related to vector search and retrieval. The client is designed to be easy to use and provides a consistent and idiomatic experience for Node.js developers. It supports all the features and functionality of the Pinecone API, making it a comprehensive solution for building vector-powered applications in Node.js.

aiohttp-pydantic
Aiohttp pydantic is an aiohttp view to easily parse and validate requests. You define using function annotations what your methods for handling HTTP verbs expect, and Aiohttp pydantic parses the HTTP request for you, validates the data, and injects the parameters you want. It provides features like query string, request body, URL path, and HTTP headers validation, as well as Open API Specification generation.

gcloud-aio
This repository contains shared codebase for two projects: gcloud-aio and gcloud-rest. gcloud-aio is built for Python 3's asyncio, while gcloud-rest is a threadsafe requests-based implementation. It provides clients for Google Cloud services like Auth, BigQuery, Datastore, KMS, PubSub, Storage, and Task Queue. Users can install the library using pip and refer to the documentation for usage details. Developers can contribute to the project by following the contribution guide.

aioconsole
aioconsole is a Python package that provides asynchronous console and interfaces for asyncio. It offers asynchronous equivalents to input, print, exec, and code.interact, an interactive loop running the asynchronous Python console, customization and running of command line interfaces using argparse, stream support to serve interfaces instead of using standard streams, and the apython script to access asyncio code at runtime without modifying the sources. The package requires Python version 3.8 or higher and can be installed from PyPI or GitHub. It allows users to run Python files or modules with a modified asyncio policy, replacing the default event loop with an interactive loop. aioconsole is useful for scenarios where users need to interact with asyncio code in a console environment.

aiosqlite
aiosqlite is a Python library that provides a friendly, async interface to SQLite databases. It replicates the standard sqlite3 module but with async versions of all the standard connection and cursor methods, along with context managers for automatically closing connections and cursors. It allows interaction with SQLite databases on the main AsyncIO event loop without blocking execution of other coroutines while waiting for queries or data fetches. The library also replicates most of the advanced features of sqlite3, such as row factories and total changes tracking.