A simple mysql orm base on pymysql
Project description
SimpySql
A lightweight mysql orm based on pymysql
Sample Code
ModelDemo().where('id', 4).select('id', 'name').take(5).get()
中文文档
Content
- Installation
- Initialization
- Create Model
- Create
- Update
- Delete
- Select
- Transaction
- Database
- Log
- Authors
Installation
pip install simpysql
Initialization
you need to create a .env file at your project root path, and content as follows:
[default]
DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=test_db1
DB_USER=root
DB_PASSWORD=123456
DB_CHARSET=utf8mb4
LOG_DIR=/home/logs/python/ #open sql log
[test_db2]
DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=test_db2
DB_USER=root
DB_PASSWORD=123456
DB_CHARSET=utf8mb4
#LOG_DIR=/home/logs/python/ #close sql log
Create Model
Create your Model extend DBModel as follows:
#!/usr/bin/python
# -*- coding: UTF-8 -*-
from simpysql.DBModel import DBModel
class ModelDemo(DBModel):
__basepath__ = '/home/project/' # .env file path
#__database__ = 'default' # database
__tablename__ = 'lh_test' # table name
__create_time__ = 'create_time' # it will be ignore if set None(default value: int(time.time()))
__update_time__ = 'update_time' # it will be ignore if set None(default value: int(time.time()))
columns = [ # table columns
'id',
'name',
'token_name',
'status',
'create_time',
'update_time',
]
# set time format of create_time and update_time
# def fresh_timestamp(self):
# return datetime.datetime.now().strftime("%Y%m%d")
Create
One Data
ModelDemo().create({'name': "haha1", 'token_name': 'haha124'})
Multi Data
ModelDemo().create([{'name': "haha1", 'token_name': 'haha124'}, {'name':"haha2", 'token_name': 'haha125'}])
Get Lastid
id = ModelDemo().create({'name': "haha1", 'token_name': 'haha124'}).lastid()
Update
Update Data
ModelDemo().where('id', 1).update({'name':"hehe", 'token_name': 'hehe123'})
Increment
ModelDemo().where('id', 1).increment('status') #status increment by 1
ModelDemo().where('id', 1).increment('status', 5) #status increment by 5
Decreament
ModelDemo().where('id', 1).decrement('status') #status decrement by 1
ModelDemo().where('id', 1).decrement('status', 5) #status decrement by 5
Delete
ModelDemo().where('id', 4).delete()
Select
Select One Data
# sql: select * from lh_test where id = 4 limit 1
data = ModelDemo().where('id', 4).first()
data = ModelDemo().where('id', '=', 4).first()
return data: {'id':4, 'name':...}
Select Multi Data
# sql: select * from lh_test where id > 4 limit 5
data = ModelDemo().where('id', '>', 4).take(5).get()
return data: [{'id':5, 'name':...},{}...]
Select Condition
# sql: select * from lh_test where id >= 4
data = ModelDemo().where('id', '>=', 4).get()
# sql: select * from lh_test where id > 4
data = ModelDemo().where('id', '>', 4).get()
# sql: select * from lh_test where id < 4
data = ModelDemo().where('id', '<', 4).get()
# sql: select * from lh_test where id <= 4
data = ModelDemo().where('id', '<=', 4).get()
# sql: select * from lh_test where id != 4
data = ModelDemo().where('id', '!=', 4).get()
# sql: select * from lh_test where id in (1,2)
data = ModelDemo().where('id', 'in', [1, 2]).get()
# sql: select * from lh_test where id not in (1,2)
data = ModelDemo().where('id', 'not in', [1, 2]).get()
# sql: select * from lh_test where id between (1,2)
data = ModelDemo().where('id', 'between', [1, 2]).get()
# sql: select * from lh_test where id not between (1,2)
data = ModelDemo().where('id', 'not between', [1, 2]).get()
# sql: select * from lh_test where name like '%Tether%'
data = ModelDemo().where('name', 'like', '%Tether%').get()
# sql: select * from lh_test where name not like '%Tether%'
data = ModelDemo().where('name', 'not like', '%Tether%').get()
# sql: select * from lh_test where `id`=62 or `name`='haha'
data = ModelDemo().where('id', 62).orwhere('name', 'haha').get()
# sql: select * from lh_test where `id`=62 or `name` like 'haha%'
data = ModelDemo().where('id', 62).orwhere('name', 'like', 'haha%').get()
# sql: select * from lh_test where `id`=62 or (`name` like 'haha%' and `create_time` < 1555123210)
data = ModelDemo().where('id', 62).orwhere([['name', 'like', 'haha%'], ['create_time', '<', 1555123210]]).get()
# sql: data = ModelDemo('a').where('FROM_UNIXTIME(create_time, "%Y%m%d%H")', 2019042912).first()
data = ModelDemo('a').where('FROM_UNIXTIME(create_time, "%Y%m%d%H")', 2019042912).first()
Select Multi Condition
# sql:select * from lh_test where id=1 and name='hehe'
data = ModelDemo().where({'id': 1, 'name': 'hehe'}).get()
# sql:select * from lh_test where id=1 and name like 'hehe%'
data = ModelDemo().where('id', 1).where('name', 'like', 'hehe%').get()
Select Order
# sql: select * from lh_test where `id` > 0 order by `id` desc,`status`
data = ModelDemo().where('id', '>', 0).orderby('id', 'desc').orderby('status').get()
Select Offset
# sql: select * from lh_test where id > 100 limit 5 offset 10
data = ModelDemo().where('id', '>', 100).offset(10).take(5).get()
Search Colums
# sql: select `id`,`name` from lh_test limit 5
data = ModelDemo().select('id', 'name').take(5).get()
# 对应sql: select min(id) as minid from lh_test limit 1
data = ModelDemo().select('min(id) as minid').first()
Select Groupby
# sql: select count(*) as num,name from lh_test group by name
data = ModelDemo().select('count(*) as num', 'name').groupby('name').get()
Select Having
# sql: select count(*) as num,name from lh_test group by name having num > 2
data = ModelDemo().select('count(*) as num', 'name').groupby('name').having('num', '>', 2).get()
# sql:select * from lh_test having FROM_UNIXTIME(create_time, "%Y%m%d%H") = 2019042912 limit 1 offset 1
ModelDemo().having('FROM_UNIXTIME(create_time, "%Y%m%d%H")', 2019042912).offset(1).first()
Select Subquery
# sql: select * from lh_test where id=(select max(id) from lh_test) limit 1
data = ModelDemo().where('id', ModelDemo().select('max(id)')).first()
# sql:select * from lh_test where id in (select max(id) from lh_test where id <= 50)
data = ModelDemo().where('id', 'in', ModelDemo().where('id', '<=', 50).select('id')).get()
# sql:select * from (select * from lh_test as a where a.id >= 58) as a
data = ModelDemo().subquery(ModelDemo('a').where('a.id', '>=', 58), 'a').get()
Tablename Alias
# sql: select a.id,a.name from lh_test as a limit 1
data = ModelDemo('a').select('a.id', 'a.name').first()
Select Joins
# 【left join】sql: select a.id,b.name from lh_test as a left join lh_test as b on a.id = b.id where a.id=42
data = ModelDemo('a').where('a.id', 42).leftjoin(ModelDemo('b').on('a.id', '=', 'b.id')).select('a.id', 'b.name').get()
# 【right join】sql: select a.id,b.name from lh_test as a right join lh_test as b on a.id = b.id where a.id=42
data = ModelDemo('a').where('a.id', 42).rightjoin(ModelDemo('b').on('a.id', '=', 'b.id')).select('a.id', 'b.name').get()
# 【inner join】sql: select a.id,b.name from lh_test as a inner join lh_test as b on a.id = b.id where a.id=42
data = ModelDemo('a').where('a.id', 42).innerjoin(ModelDemo('b').on('a.id', '=', 'b.id')).select('a.id', 'b.name').get()
Select Unions
# 【union all】sql: (select * from lh_test where id=42) union all (select * from lh_test where id=58)
data = ModelDemo().where('id', 42).unionall(ModelDemo().where('id', '=', 58)).get()
# 【union】sql: (select * from lh_test where id=42) union (select * from lh_test where id=58)
data = ModelDemo().where('id', 42).union(ModelDemo().where('id', '=', 58)).get()
Original SQL
sql = 'select count(*) as num,name from lh_test group by name'
data = ModelDemo().execute(sql)
Response
data = ModelDemo().where('id', '=', 1).select('id').first() # {'id':1}
data = ModelDemo().where('id', '=', 1).select('id').get() # [{'id':1}]
data = ModelDemo().where('id', 'in', [1,2,3]).select('id', 'name').lists('id') # [1,2,3]
data = ModelDemo().where('id', 'in', [1,2]).select('id', 'name').lists(['id', 'name']) # [[1,'name1'],[2,'name2']]
data = ModelDemo().select('id', 'name', 'status').data() # return pandas DataFrame
Transaction
method1:
def demo():
ModelDemo().where('id', 42).update({'name': "44", 'token_name': '444'})
ModelDemo().where('id', 43).update({'name': "44", 'token_name': '444'})
return True
data = ModelDemo().transaction(demo)
method2:
@ModelDemo.transaction
def demo(id):
ModelDemo().where('id', id).update({'name': "44", 'token_name': '111'})
ModelDemo().where('id', 43).update({'name': "44", 'token_name': '111'})
# raise Exception('haha')
return True
demo(42)
Database
set ModelDemo attribute as follow:
__database__ = 'test_db2'
set database in your code:
ModelDemo().database('test_db2').where('id', '>', 40).first()
Log
set LOG_DIR in your .env file:
LOG_DIR=/home/logs/python/
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.
Source Distribution
simpysql-0.1.6.tar.gz
(14.6 kB
view hashes)
Built Distribution
simpysql-0.1.6-py3-none-any.whl
(19.2 kB
view hashes)