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.
aiocache
Aiocache is an asyncio cache library that supports multiple backends such as memory, redis, and memcached. It provides a simple interface for functions like add, get, set, multi_get, multi_set, exists, increment, delete, clear, and raw. Users can easily install and use the library for caching data in Python applications. Aiocache allows for easy instantiation of caches and setup of cache aliases for reusing configurations. It also provides support for backends, serializers, and plugins to customize cache operations. The library offers detailed documentation and examples for different use cases and configurations.
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.
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.
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.
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.
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.
julep
Julep is an advanced platform for creating stateful and functional AI apps powered by large language models. It offers features like statefulness by design, automatic function calling, production-ready deployment, cron-like asynchronous functions, 90+ built-in tools, and the ability to switch between different LLMs easily. Users can build AI applications without the need to write code for embedding, saving, and retrieving conversation history, and can connect to third-party applications using Composio. Julep simplifies the process of getting started with AI apps, whether they are conversational, functional, or agentic.
vim-ai
vim-ai is a plugin that adds Artificial Intelligence (AI) capabilities to Vim and Neovim. It allows users to generate code, edit text, and have interactive conversations with GPT models powered by OpenAI's API. The plugin uses OpenAI's API to generate responses, requiring users to set up an account and obtain an API key. It supports various commands for text generation, editing, and chat interactions, providing a seamless integration of AI features into the Vim text editor environment.
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.
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.
LLM.swift
LLM.swift is a simple and readable library that allows you to interact with large language models locally with ease for macOS, iOS, watchOS, tvOS, and visionOS. It's a lightweight abstraction layer over `llama.cpp` package, so that it stays as performant as possible while is always up to date. Theoretically, any model that works on `llama.cpp` should work with this library as well. It's only a single file library, so you can copy, study and modify the code however you want.
evalplus
EvalPlus is a rigorous evaluation framework for LLM4Code, providing HumanEval+ and MBPP+ tests to evaluate large language models on code generation tasks. It offers precise evaluation and ranking, coding rigorousness analysis, and pre-generated code samples. Users can use EvalPlus to generate code solutions, post-process code, and evaluate code quality. The tool includes tools for code generation and test input generation using various backends.
client-python
The Mistral Python Client is a tool inspired by cohere-python that allows users to interact with the Mistral AI API. It provides functionalities to access and utilize the AI capabilities offered by Mistral. Users can easily install the client using pip and manage dependencies using poetry. The client includes examples demonstrating how to use the API for various tasks, such as chat interactions. To get started, users need to obtain a Mistral API Key and set it as an environment variable. Overall, the Mistral Python Client simplifies the integration of Mistral AI services into Python 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.
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.