Skip to main content

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

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()

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()

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


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.3.tar.gz (5.1 kB view hashes)

Uploaded Source

Built Distribution

simpysql-0.1.3-py3-none-any.whl (5.8 kB view hashes)

Uploaded Python 3

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