
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.

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.

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.

langserve
LangServe helps developers deploy `LangChain` runnables and chains as a REST API. This library is integrated with FastAPI and uses pydantic for data validation. In addition, it provides a client that can be used to call into runnables deployed on a server. A JavaScript client is available in LangChain.js.

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.

aiohttp-session
aiohttp_session is a Python library that provides session management for aiohttp.web applications. It allows storing user-specific data in session objects with a dict-like interface. The library offers different session storage options, including SimpleCookieStorage for testing, EncryptedCookieStorage for secure data storage, and RedisStorage for storing data in Redis. Users can easily integrate session management into their aiohttp.web applications by registering the session middleware. The library is designed to simplify session handling and enhance the security of web applications.

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.

airbadge
Airbadge is a Stripe addon for Auth.js that simplifies the process of creating a SaaS site by integrating payment, authentication, gating, self-service account management, webhook handling, trials & free plans, session data, and more. It allows users to launch a SaaS app without writing any authentication or payment code. The project is open source and free to use with optional paid features under the BSL License.

aibolit
Aibolit is a machine learning-based static analyzer for Java that helps identify patterns contributing to Cyclomatic Complexity in Java source code. It provides recommendations for fixing identified issues and allows users to suppress certain patterns. Aibolit can analyze individual Java files or entire folders of Java source code. Users can customize the output format and exclude specific files from analysis. The tool also supports training custom models for analyzing Java code. Aibolit is designed to help developers improve code quality and maintainability by identifying and addressing potential issues in Java code.

ppt2desc
ppt2desc is a command-line tool that converts PowerPoint presentations into detailed textual descriptions using vision language models. It interprets and describes visual elements, capturing the full semantic meaning of each slide in a machine-readable format. The tool supports various model providers and offers features like converting PPT/PPTX files to semantic descriptions, processing individual files or directories, visual elements interpretation, rate limiting for API calls, customizable prompts, and JSON output format for easy integration.

shortest
Shortest is an AI-powered natural language end-to-end testing framework built on Playwright. It provides a seamless testing experience by allowing users to write tests in natural language and execute them using Anthropic Claude API. The framework also offers GitHub integration with 2FA support, making it suitable for testing web applications with complex authentication flows. Shortest simplifies the testing process by enabling users to run tests locally or in CI/CD pipelines, ensuring the reliability and efficiency of web applications.
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.

dbhub
DBHub is a universal database gateway that implements the Model Context Protocol (MCP) server interface. It allows MCP-compatible clients to connect to and explore different databases. The gateway supports various database resources and tools, providing capabilities such as executing queries, listing connectors, generating SQL, and explaining database elements. Users can easily configure their database connections and choose between different transport modes like stdio and sse. DBHub also offers a demo mode with a sample employee database for testing purposes.

mcp-llm-bridge
The MCP LLM Bridge is a tool that acts as a bridge connecting Model Context Protocol (MCP) servers to OpenAI-compatible LLMs. It provides a bidirectional protocol translation layer between MCP and OpenAI's function-calling interface, enabling any OpenAI-compatible language model to leverage MCP-compliant tools through a standardized interface. The tool supports primary integration with the OpenAI API and offers additional compatibility for local endpoints that implement the OpenAI API specification. Users can configure the tool for different endpoints and models, facilitating the execution of complex queries and tasks using cloud-based or local models like Ollama and LM Studio.

mysql_mcp_server
A Model Context Protocol (MCP) server that enables secure interaction with MySQL databases. This server allows AI assistants to list tables, read data, and execute SQL queries through a controlled interface, making database exploration and analysis safer and more structured. It provides features such as listing available MySQL tables as resources, reading table contents, executing SQL queries with proper error handling, secure database access through environment variables, and comprehensive logging. The tool ensures security best practices by never committing environment variables or credentials, using a database user with minimal required permissions, implementing query whitelisting for production use, and monitoring and logging all database operations.

mcp-graphql
mcp-graphql is a Model Context Protocol server that enables Large Language Models (LLMs) to interact with GraphQL APIs. It provides schema introspection and query execution capabilities, allowing models to dynamically discover and use GraphQL APIs. The server offers tools for retrieving the GraphQL schema and executing queries against the endpoint. Mutations are disabled by default for security reasons. Users can install mcp-graphql via Smithery or manually to Claude Desktop. It is recommended to carefully consider enabling mutations in production environments to prevent unauthorized data modifications.
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.