Skip to main content

pysqler

Easy to write sql to avoid using string slice

更方便的拼写SQL, 免除各种容易出错的拼接字符串操作

eg:

age = some_function()
sql = "select * from people where name=\'barry\' and age = {0}" 
if age:
    sql = sql.format(sql, age)
else:
    sql = sql.format(sql, "null")

above is boring, so try this:

from pysqler import *

age = some_function()

query = Select()
query.select("*")
query.from1("people")
query.where("age", "=", age)
query.and_where("name", "=", "barry")
query_str = str(query)
print(query_str)

you don't need take care of that if if the param is string, number or none ...

Usage

see more samples in tests/test_sqler.py

Build Select SQL

from pysqler import *

query = Select()
query.select("city", "education", "AVG(age) as avg_age")
query.from1("people")
query.where("age", ">", 10)
query.join("orders", "orders.account = people.id",
           "orders.time = people.birthday")
query.and_where("job", "like", "%it%")
query.and_where("birthday", ">", "1988-09-12 12:12:12")
query.and_where("address", "!=", None)

query.left_join("vip", "vip.account = people.id")

query.groupby("city", "education")
query.orderby("avg_age", "DESC")
query.limit(10, 8)

output

SELECT city,education,AVG(age) as avg_age
FROM people
INNER JOIN orders
ON orders.account = people.id and orders.time = people.birthday
LEFT JOIN vip ON vip.account = people.id
WHERE age > 10 AND job like "%it%" AND birthday > "1988-09-12 12:12:12"
AND address IS NOT null
GROUP BY city,education ORDER BY avg_age DESC
LIMIT 8,10;

Build Insert SQl

insert one row

from pysqler import *

query = Insert("people")
query.put("name", "barry")

query.put("age", 10, value_on_duplicated=20)

express = Expression()
express.field("salary")
express.operator("+")
express.value(200)
express.operator("*")
express.value(3.5)

query.put("salary", 1000, value_on_duplicated=express)
query.put("address", "shanghai", value_on_duplicated="china")
query.put("education", "bachelor")
query.put("job", "engineer")
query.put("birthday", "2000-01-01")
query_str = str(query)
print(query_str)

output:

INSERT INTO people ( name,age,salary,address,education,jobs,birthday)
VALUES("barry",10,1000,"shanghai","bachelor","engineer","2000-01-01")
ON DUPLICATE KEY UPDATE age = 20,salary = salary + 200 * 3.5,
address = "china";

insert multiple rows

from pysqler import *

query = Insert("people")
query.add_columns("name", "age", "salary", "address", "education", "job", "birthday")
query.add_row("barry", 19, 3100, "shanghai", "bachelor", None,"2010-01-01")
query.add_row("jack", 24, 3600, "shanghai", "bachelor", "engineer","2010-01-09")
query.add_row("bob", 27, 8600, None, "bachelor", "engineer","1990-01-09")
query.add_row("edwin", 30, 10600, "beijing", "bachelor", "engineer","1987-01-09")
query_str = str(query)
print(query_str)

output:

INSERT INTO people ( name,age,salary,address,education,job,birthday )
 VALUES( "barry",19,3100,"shanghai","bachelor",null,"2010-01-01" ),
 ( "jack",24,3600,"shanghai","bachelor","engineer","2010-01-09" ),
 ( "bob",27,8600,null,"bachelor","engineer","1990-01-09" ),
 ( "edwin",30,10600,"beijing","bachelor","engineer","1987-01-09" )

Build update SQl

from pysqler import *

query = Update("people")
query.put("name", "barry")
query.put("age", 10)

query.where("age", ">", 15)
query.or_where("age", "<", 5)
query_str = str(query)
print(query_str)

output:

UPDATE people SET name = "barry",age = 10
WHERE age > 15 OR age < 5;

Build delete SQl

from pysqler import *

query = Delete("people")

query.where("age", ">", 15)
query.or_where("name", "in", [9527, "barry", "jack"])
query_str = str(query)
print(query_str)

output:

DELETE FROM people  WHERE age > 15 OR name in (9527,"barry","jack");

Metadata

Release files for pysqler 1.0.1

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pysqler 1.0.1
File Size Uploaded Provenance Actions
pysqler-1.0.1.tar.gz 11.2 kB Without provenance Details for pysqler-1.0.1.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for pysqler 1.0.1
File Interpreter ABI Platform Actions
pysqler-1.0.1-py2.py3-none-any.whl Python 2, Python 3 none any Details for pysqler-1.0.1-py2.py3-none-any.whl

Total release size: 22.1 kB

Release files / pysqler-1.0.1.tar.gz

Download URL pysqler-1.0.1.tar.gz
Size 11.2 kB
Tags Source
SHA-256 checksum
How to use checksums
62518b096fac94287bcd11f2f9a37b98dafbaac1bf4b5ed09e206fbac929c7f8
BLAKE2b-256 checksum
How to use checksums
bbe50c0d5ba364b5c67bf6adabe2f6f65b626f1f77f02d2c6c78cceb162e14bb
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/50.3.0 requests-toolbelt/0.9.1 tqdm/4.50.1 CPython/3.8.2

Release files / pysqler-1.0.1-py2.py3-none-any.whl

Download URL pysqler-1.0.1-py2.py3-none-any.whl
Size 11.0 kB
Tags Python 2 Python 3
SHA-256 checksum
How to use checksums
0c875fc6968e3945818d726dca1af178e2d8278663a6ed513acba529eade7619
BLAKE2b-256 checksum
How to use checksums
ab5f217518eed654cfc0df724c2ab4ccc99fc0864a5c6d1ec0fd0ff1588740ac
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/50.3.0 requests-toolbelt/0.9.1 tqdm/4.50.1 CPython/3.8.2

Security for pysqler 1.0.1

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page