简单的SQL生成器。
firehsql的Python项目详细描述
FireHSQL
- 网址：
Github <https://github.com/dozymoe/firehsql/> - 许可证：GPL v3
摘要
简单的SQL查询生成器，不支持创建表、字段定义和 不过，库只观察表字段名。
示例：
。代码：：python
from firehsql import SchemaBase
class UserSchema(SchemaBase):
TABLE_NAME = 'users'
FIELDS = ('id', 'username', 'email', 'password', 'is_superuser',
'is_staff', 'created_at', 'modified_at')
FILTER_BY_FIELDS = ('id', 'username', 'email', 'created_at')
ORDER_BY_FIELDS = ('username',)
# For PostgreSQL to return last inserted id
RETURNING_FIELDS = ('id',)
。代码：：python
from itertools import chain
import psycopg2
from .schema import UserSchema
def insert():
sql = UserSchema.create_insert_sql()
sql.set_values(
username='User1',
password='User1Password',
email='User1@example.com')
with psycopg2.connect('dbname=testdb') as conn:
with conn.cursor() as cur:
# INSERT INTO users (username, password, email)
# VALUES (%s, %s, %s)
#
# ('User1', 'User1Password', 'User1@example.com')
cur.execute(str(sql), sql.data)
conn.commit()
def select():
sql = UserSchema.create_select_sql()
filter_ = sql.create_or_filter()
# The word 'LIKE' will not be checked if it was valid or not,
# too bothersome, just don't put user's input in there.
filter_.add(('username', 'Ach%', 'LIKE'))
filter_.add(('username', 'Abd%', 'LIKE'))
sql.set_filters(filter_)
# Assumed DESCENDING if it was prefixed with hyphen (-), the target
# being http query string.
sql.set_sorting_order('username', '-id')
page_size = 10
page = 1
page_offset = (page - 1) * page_size
# Both page_size and page_offset tested to be of type integer.
sql.set_limit(page_size, page_offset)
with psycopg2.connect('dbname=testdb') as conn:
with conn.cursor() as cur:
# SELECT * FROM users WHERE username LIKE %s
# OR username LIKE %s ORDER BY username, id DESC
# LIMIT 10 OFFSET 0
#
# ('Ach%', 'Abd%')
cur.execute(str(sql), sql.data)
def update():
sql = UserSchema.create_update_sql()
sql.set_values(
is_superuser=True,
is_staff=True)
# '=' will not be checked if it was valid operand or not.
sql.set_filters(
('username', 'User1', '='))
with psycopg2.connect('dbname=testdb') as conn:
with conn.cursor() as cur:
# UPDATE users SET is_superuser=%s, is_staff=%s
# WHERE username = %s
#
# (True, True, 'User1')
cur.execute(str(sql), sql.data)
def filter_parser():
# This is targetted at http query string
sql = UserSchema.create_select_sql()
data = {
'filter_by': {
'username': 'User%', # starts and/or ends with '%'
'email': '!null',
'created_at': '>10-2-2017',
}
}
filters = chain(
sql.find_filters(data['filter_by'],
'username', 'email'),
sql.find_datetime_filters(data['filter_by'],
'created_at'),
)
sql.set_filters(*filters)
with psycopg2.connect('dbname=testdb') as conn:
with conn.cursor() as cur:
# SELECT * FROM users WHERE username LIKE %s
# AND email IS NOT NULL
# AND created_at > %s
# LIMIT 10 OFFSET 0
#
# ('User%', datetime.datetime(2017, 2, 10, 0, 0, 0, 0,
# tzinfo=<UTC>))
cur.execute(str(sql), sql.data)
def advance_filter_parser():
sql = UserSchema.create_select_sql()
data = {
'filter_by': [
'AND',
[
'OR',
{'name': 'User%'},
{'name': '%User'},
],
[
'OR',
{'email': '!null'},
{'email': '=admin@example.com'},
},
{'created_at': '>10-2-2017'},
{
'id': ['=', 1, 2, 3],
},
{
'id': ['!', 4, 5, 6],
},
}
}
advanced_filter = sql.parse_adv_filters(
data['filter_by'],
(
('username', 'name', 'str'),
('email', 'str'),
('created_at', 'date'),
('id', 'int'),
))
sql.set_filters(advanced_filter)
with psycopg2.connect('dbname=testdb') as conn:
with conn.cursor() as cur:
# SELECT * FROM users WHERE
# (username LIKE %s OR username LIKE %s)
# AND
# (email IS NOT NULL OR email = %s)
# AND
# created_at > %s
# AND
# id IN (%s, %s, %s)
# AND
# id NOT IN (%s, %s, %s)
# LIMIT 10 OFFSET 0
#
# (
# 'User%', '%User', 'admin@example.com',
# datetime(2017, 2, 10, 0, 0, 0, 0, tzinfo=<UTC>),
# 1, 2, 3, 4, 5, 6,
# )
cur.execute(str(sql), sql.data)
欢迎加入QQ群-->： 979659372
