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
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.
Filename, size | File type | Python version | Upload date | Hashes |
---|---|---|---|---|
Filename, size pysqler-1.0.1-py2.py3-none-any.whl (11.0 kB) | File type Wheel | Python version py2.py3 | Upload date | Hashes View |
Filename, size pysqler-1.0.1.tar.gz (11.2 kB) | File type Source | Python version None | Upload date | Hashes View |
Close
Hashes for pysqler-1.0.1-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 0c875fc6968e3945818d726dca1af178e2d8278663a6ed513acba529eade7619 |
|
MD5 | 96bbfd3ac9bfaf7ab0771d6c2ec2953b |
|
BLAKE2-256 | ab5f217518eed654cfc0df724c2ab4ccc99fc0864a5c6d1ec0fd0ff1588740ac |