a little orm
Project description
Crystaldb
======
crystaldb is a simple and small ORM, support native sql and orm. .
csystaldb是简单轻量级的mysql查询包,支持原生sql和orm.
* a small, expressive ORM, and no need to provide a model.
支持orm, 不需要写MODEL.
* python 3.4+
* supports mysql(developed with others)
Examples
--------
Connect to the database:
.. code-block:: python
import crystaldb
db_host = '127.0.0.1'
db_port = 3306
db_user = 'root'
db_pass = '123'
db_database = 'testdb'
db_handle = crystaldb.database(
dbn='mysql',
host=db_host,
port=db_port,
user=db_user,
passwd=db_pass,
db=db_database,
debug=True)
Create table: (Temporarily not supported)
暂时不支持创建表,需要去mysql先创建表。
.. code-block:: python
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`gender` varchar(16) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`birthday` varchar(16) DEFAULT NULL,
`age` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Query Syntax:
查询语法:
* native sql, support '$' or ':' symbol.
.. code-block:: python
# native sql, support '$' or ':' symbol.
sql = """select * from user where id=$id"""
sql = """select * from user where id>:id and gender=:gender"""
# -->> select * from user where id>5 and gender='girl'
params = {"id": 5, "gender": "girl"}
db_handle.query(sql, params)
* get or filter_by syntax
.. code-block:: python
query_result = db_handle.select("user").filter_by(id=2).all() # iterable object
query_result.list() # list object
# -->>SELECT * FROM user WHERE id = 2
query_result = db_handle.select("user").get(id=2)
# -->>SELECT * FROM user WHERE id = 2
for item in query_result:
print(item)
* lt or lte or gt or gte or eq syntax
.. code-block:: python
db_handle.select("user", ["id", "name"]).lt(id=5).gt(id=2).all().list()
#-->>SELECT id, name FROM user WHERE id < 5 AND id > 2
db_handle.select("user").lte(id=20).gte(id=2).limit(2).list()
#-->>SELECT * FROM user WHERE id <= 20 AND id >= 2 LIMIT 2
db_handle.select("user").eq(id=2).all().list()
#-->>SELECT * FROM user WHERE id = 2
* between syntax, also support count syntax
.. code-block:: python
db_handle.select("user").between(id=[2, 5]).count()
#-->>SELECT COUNT(*) AS COUNT FROM user WHERE id BETWEEN 2 AND 5
* filter syntax and order by syntax.
.. code-block:: python
db_handle.select("user").filter(gender="girl").order_by(["age", "name"], _reversed=True).all().list()
#-->>SELECT * FROM user WHERE gender = 'girl' ORDER BY age DESC , name DESC
db_handle.select("user").filter(gender="girl").order_by("age").all().list()
#-->>SELECT * FROM user WHERE gender = 'girl' ORDER BY age
db_handle.select("user").lt(id=10).filter(gender="girl").order_by("age DESC, name ASC", _reversed=False).all().list() # reversed need be False.
#-->>SELECT * FROM user WHERE id < 10 AND gender = 'girl' ORDER BY age DESC, name ASC
* The difference between filter and filter_by is that filter requires query syntax to return results.
.. code-block:: python
db_handle.select("user").gt(id=2).filter(gender="girl").query().list()
#-->>SELECT * FROM user WHERE id > 2 AND gender = 'girl'
* first syntax
.. code-block:: python
db_handle.select("user").lt(id=5, age=25).first() # length=1
#-->>SELECT * FROM user WHERE age < 25 AND id < 5
* in syntax
.. code-block:: python
db_handle.select("user").in_(id=[1, 2, 3, 4], gender=["girl", "boy"]).all().list()
#-->>SELECT * FROM user WHERE gender IN ('girl', 'boy') AND id IN (1, 2, 3, 4)
Insert Syntax: support native sql and orm.
插入语法:
* native sql.
.. code-block:: python
sql = """insert into user set age=$age, gender=$gender, birthday=$birthday, name=$name"""
values = {'gender': 'girl', 'name': 'xiaowang2', 'birthday': '1981-08-02', 'age': 35}
# -->>insert into user set age=35, gender='girl', birthday='1981-08-02', name='xiaowang2'
result = db_handle.query(sql, values) # return row count
* orm.
.. code-block:: python
values = {'gender': 'girl', 'name': 'xiaowang2', 'birthday': '1981-08-02', 'age': 35}
result = db_handle.operator("user").insert(ignore=True, **values) # return row sql
# -->>INSERT IGNORE INTO user (age, birthday, gender, name) VALUES (35, '1981-08-02', 'girl', 'xiaowang2')
result = db_handle.operator("user").insert(seqname=True, **values) # return lastest insert id.
# -->>INSERT INTO user (age, birthday, gender, name) VALUES (35, '1981-08-02', 'girl', 'xiaowang2');
# -->>SELECT last_insert_id();
# multiple insert
values_list = []
for i in range(3):
values = {'gender': 'girl', 'name': 'xiaowang2', 'birthday': '1981-08-02', 'age': 35+i}
values_list.append(values)
result = db_handle.operator("user").multiple_insert(values_list, seqname=True) # return range(lastest insert id)
# -->> INSERT INTO user (age, birthday, gender, name) VALUES (35, '1981-08-02', 'girl', 'xiaowang2'), (36, '1981-08-02', 'girl', 'xiaowang2'), (37, '1981-08-02', 'girl', 'xiaowang2')
# -->> SELECT last_insert_id();
# -->> returns: range(48, 51)
Update Syntax: support native sql and orm.
更新语法:
.. code-block:: python
where = dict(id=5)
result = db_handle.operator("user").update(where, age=19, name="xiao2"))
# -->>UPDATE user SET age = 19, name = 'xiao2' WHERE id = 5
debug_sql = db_handle.debug_queries # dict object.
# -->>{'run_time': '0.2793', 'sql': "UPDATE user SET age = 20, name = 'xiao1' WHERE id = 5"}
values = dict(age=20, name="xiao1")
result = db_handle.operator("user").update(where, **values))
# -->>UPDATE user SET age = 20, name = 'xiao1' WHERE id = 5
Insert Duplicate Update Syntax: support native sql and orm.
.. code-block:: python
where = dict(id=4, age=20, name="xiao12", birthday="1995-08-03")
values = dict(age=20, name="xiao1", birthday="1995-08-02", id=4, gender="girl")
result = db_handle.operator("user").insert_duplicate_update(where, **values)
# -->>INSERT INTO user (age, birthday, gender, id, name) VALUES (20, '1995-08-02', 'girl', 4, 'xiao1') ON DUPLICATE KEY UPDATE age = 20 , birthday = '1995-08-03' , id = 4 , name = 'xiao12'
Delete Syntax: support native sql and orm.
删除语法:
.. code-block:: python
where = dict(id=5)
result = db_handle.operator("user").delete(where)
# -->> DELETE FROM user WHERE id = 5
Get Debug Queries.
.. code-block:: python
db_handle = crystaldb.database(
dbn='mysql',
host=db_host,
port=db_port,
user=db_user,
passwd=db_pass,
db=db_database,
get_debug_queries=True)
where = dict(id=4, age=20, name="xiao12", birthday="1995-08-03")
values = dict(age=20, name="xiao1", birthday="1995-08-02", id=4, gender="girl")
result = db_handle.operator("user").insert_duplicate_update(where, **values)
debug_queries_info = db_handle.get_debug_queries_info
# -->>{'run_time': '0.2749', 'sql': "INSERT INTO user (age, birthday, gender, id, name) VALUES (20, '1995-08-02', 'girl', 4, 'xiao1') ON DUPLICATE KEY UPDATE age = 20 , birthday = '1995-08-03' , id = 4 , name = 'xiao12'"} # run_time: unit ms
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
crystaldb-1.0.1.tar.gz
(20.7 kB
view hashes)