Skip to main content

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

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

Project details


Download files

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

Source Distribution

pysqler-1.0.1.tar.gz (11.2 kB view hashes)

Uploaded source

Built Distribution

pysqler-1.0.1-py2.py3-none-any.whl (11.0 kB view hashes)

Uploaded py2 py3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page