A lightweight Python library to easily generate SQL queries
Project description
chocolatine
Chocolatine is a lightweight python library designed to easily generate SQL queries.
Why Chocolatine ?
If you know Python programming langage but you are not at your ease with SQL (or you don't want to manage SQL requests by yourself), you can use Chocolatine to generate some SQL queries for you. Of course, there are many other open source projects to do that, but honestly, they are more complex than most people expects from them (SQLAlchemy, Django ORM, etc...).
Examples
Concatenation & filtering :
from chocolatine import Query, Col as _
query = Query().table("customer")\
.select(
"customer_id",
(_("first_name") & ' ' & _("last_name")).upper().alias("name")
)\
.filter(_("first_name") >> "%E")
print(query)
Output :
SELECT customer_id, UPPER(CONCAT(first_name, ' ', last_name)) AS name
FROM customer
WHERE first_name LIKE '%E'
Group by, aggregation & filtering :
from chocolatine import Query, sum, Col as _
query = Query().table("payment")\
.select(
"customer_id",
count().alias("payment_count"),
sum("amount").alias(">:total_amount")
)\
.group_by("customer_id")\
.filter(count() > 1 & sum("amount") > 5.00)\
.filter(_("customer_id") != 3)
print(query)
Output :
SELECT customer_id, COUNT(*) AS payment_count, SUM(amount) AS total_amount
FROM payment
WHERE customer_id != 3
GROUP BY customer_id
HAVING COUNT(*) > 1 AND SUM(amount) > 5.00
ORDER BY total_amount
Join :
from chocolatine import Query, Col as _
query = Query().table("film")\
.select(
"title",
"film_id",
(_("first_name") & " " & _("last_name")).alias("name")
)\
.join("film_actor", "film_id")\
.join("actor", "actor_id")\
.build()
print(query)
Output :
SELECT title, film_id, CONCAT(first_name, ' ', last_name) AS name
FROM film
INNER JOIN film_actor
USING film_id
INNER JOIN actor
USING actor_id
Note : Random aliases have been used to remove ambiguity on joins clauses. You can always define your own aliases instead.
SQL dialect
For now, Chocolatine is only designed to generate MySQL queries. It is not excluded that in the future it will be compatible with Sqlite3, SqlServer or postgreSQL
Basic functionnalities
- Select requests
- Distinct
- Aliases (for columns & tables)
- Ordering
- Group by
- Aggregation functions
- Joins
- SQL functions
- Concatenations
Advanced functionnalities
- Dynamic type checking
- Use of : or @ in col or table name directly for alias
- Protection against SQL injection attacks
- Calls orders doesn't matter (except for join clauses)
- Compact or extended SQL expressions
- Whole system to deal with conditions (logical operators, boolean operators, priority order)
- Automatic handling of filter conditions to fill the having or where clause depending on the given columns
- Shortcut functions : Asc, Desc, Sum, Count, Upper, Lower, Concat, Second, Minute, Hour, Day, Month, Year
- >: or <: at first position in column name (or alias) in select to set the ordering
- >> operator to perform a "like" condition on a column
- << operator to perform a "in" condition on a column
- Limit clause
- Nested requests
- "ChocExpr" mini langage for SQL requests templating :
- Conditions
- Basic loops (with unpacking lists)
- Case When
- Update requests
- Delete requests
To-do
- Tables union
- Option to disable dynamic type checking (for performance concerns)
- Create requests
- Pypi package (to install with pip install)
- SQLServer compatibility
- PostGreSQL compatibility
- SQLite3 compatibility
Tests
python pytest
Install Sakila database (mySQL) with Docker
docker run -p 3306:3306 -d sakiladb/mysql:latest
Contributors
- peb-8 (main code)
- ryry-shi (testing)
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for chocolatine-0.85-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | ab249ded377200e24802694118597cf5a73f1f43a9364ed887c7245e6a2212c8 |
|
MD5 | 2b4e627bff95f6eb4745465e77d265ae |
|
BLAKE2b-256 | c420038f536ca542130b54c28f9c7982acb548310e2fc760533f97a79b944c7a |