Skip to main content
Join the official 2019 Python Developers SurveyStart the survey!

Easy to write sql

Project description

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

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");

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for pysqler, version 0.8.9
Filename, size File type Python version Upload date Hashes
Filename, size pysqler-0.8.9-py2.py3-none-any.whl (10.3 kB) File type Wheel Python version py2.py3 Upload date Hashes View hashes
Filename, size pysqler-0.8.9.tar.gz (9.9 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page