preface

Python mysql chain operation, ABuilder goodbye tedious model definition, save development time, almost no requirements, import direct use. Different from the conventional model ABuilder does not need to pre-define table fields, field types, field lengths and other tedious Settings, of course, that does have its advantages here will not say ~, each has its own strengths to choose from the individual. ABuilder supports direct use, simple, fast and convenient

Making the address

Github.com/lizhenggan/…

Quick start

Install a – sqlbuilder

pip install a-sqlbuilder
Copy the code

Set up the data profile (refer to the database profile description)
Begin to use

from ABuilder.ABuilder import ABuilder

model = ABuilder()
data = model.table('tar_user').field("username,id").where({"username": ["like"."% M - '%"]}).limit(0.1).query()
Copy the code

Using document

Third-party Library Requirements

pymysql
logging

Database configuration file

We need developers to add database.py database configuration files to the project directory

Make sure from Database import DATABASE retrieves database configuration items

Configuration file example:

class Config(object):
    pass

class Proconfig(Config):
    pass


class Devconfig(Config):
    debug = True
    DATABASE_URI = 'mysql + pymysql: / / root: [email protected]:3306 / target'
    data_host = '127.0.0.1' 
    data_pass = 'root'
    data_user = 'root'
    database = 'target'
    data_port = 3306
    charset = 'utf8mb4'


database = Devconfig
Copy the code

Support functions

Currently the project only supports some simple usages as follows

The table lookup table
Where the where condition
Where_or sets the or condition
Field query field
Limit Indicates the number of queries
Group, a group
The order sorting
Join Join table query
First Query single entry
Query Multiple queries
Pluck queries for a single field
Insert to insert
The update changes
Delete delete
Select performs native queries
Commit
Rollback Rollback
Get_last_sql Obtains the execution SQL
Get_insert_id Gets the insert ID

table

Set up the query table

from ABuilder.ABuilder import ABuilder
data = ABuilder().table('user').limit(0.1).query()
print(data)
Copy the code

where

Setting the WHERE condition

from Amo.ABuilder import ABuilder
ABuilder().table('user').where({"id": ["=".3]}).first()
Copy the code

Multiple WHERE conditions

ABuilder().table('user').where({"id": ['< ='.10]."sex": ["="."Male"]}).query()
# or multiple WHERE concatenations
ABuilder().table('user').where({"id": ['< ='.10]}).where({"sex": ["="."Male"]}).query()
Copy the code

Comparison symbols support =,<>,<,>,<=,>=,in,like, etc

where_or

Set the or condition where_or is used in much the same way as where

from Amo.ABuilder import ABuilder
ABuilder().table('user').where_or({"id": ["=".3]}).query()
Copy the code

Comparison symbols support =,<>,<,>,<=,>=,in,like, etc

field

Setting query fields

ABuilder().table("user").field("user_id,sex,user_name").query()
# example count
ABuilder().table("user").field("count(*) as count").query()
Copy the code

By default, all fields * are queried. The query fields are separated and count() and sum() are supported

Note: functions with % such as DATE_FORMAT(NOW(),’%m-%d-%Y’) are not supported

limit

Query the number

ABuilder().table("user").limit(0.10).query()
Copy the code

Limit The first parameter is the start position and the second parameter is the number of entries to obtain

group

grouping

# Group by gender
ABuilder().table("user").field("count(*) as count").group('sex').query()

# Use multiple groups
ABuilder().table("user").field("count(*) as count").group('sex').group('age').query()
Select count(*) as count from user group by sex,age
Copy the code

order

The sorting

ABuilder().table("user").order("user_id"."desc").query()

# multiple sort values
ABuilder().table("user").order("user_id"."desc").order("sex".'asc').query()
Select * from tar_user order by user_id desc,sex asc
Copy the code

Order by: Follow from left to right

join

Check the table

ABuilder().table('user as u').field('u.id,b.name').join('book b'.'u.id=b.user_id'.'INNER').where({"u.id": ['='.1]}).query()

Select u.id,b.name from user as u INNER JOIN book b on U.id = b.usser_id where U.id = 1
Copy the code

Join Description of the three parameters

The name of the table
List conditional supportand,or
The join type:INNER,LEFT,RIGHT,FULLThe default INNER

Multiple join tables can be joined together. For example: the table (‘ table as t ‘). The join (table1 t1, t1. User_id = ‘t.i d’). The join (‘ table2 t2 ‘, ‘t1. Id = t2. Book_id’)

first

Query a single record

find = ABuilder().table('user').where({"id": ["=".3]}).first()
print(find)
Copy the code

query

Querying Multiple Records

data = ABuilder().table('user').where({"id": ["in", (1.2.3.4)]}).query()
print(data)
Copy the code

pluck

Querying a single field

user_id = ABuilder().table('user').where({"username": ["=".'Joe']}).pluck('id')
print(user_id)
Copy the code

insert

Insert data

model = ABuilder()
state = model.table("user").insert({"username":"Zhang"."sex":'male'."age":18})
if state:
    print("Added successfully! Increment ID: %" % model.get_insert_id)
else:
    print("Add failed")
Copy the code

update

Modify the record

state = ABuilder().table("user").where({"username": ["="."Zhang"]}).update({"age":25})
if state:
    print('Modified successfully')
else:
    print('Modification failed')

Copy the code

delete

Delete records

state = ABuilder().table("user").where({"username": ["="."Zhang"]}).delete()
if state:
    print('Deleted successfully')
else:
    print('Delete failed')
Copy the code

select

Execute native SQL

model = ABuilder()
# First way
model.select("SELECT username,id FROM user where id=%s"[1])
# Second way
model.select("SELECT username,id FROM user where id=1")
Copy the code

Commit and rollback

What operation

model = ABuilder()
state = model.table("user").insert({"username":"Zhang"."sex":'male'."age":18})
if state:
    state = model.table("book").insert({"book_name":'Book nickname'."user_id":model.get_insert_id})
if state:
    # Success is submission
    model.commit()
else:
    # Failure rolls things back
    model.rollback()
Copy the code

Do not instance multiple Abuilders. Otherwise, some of the abuilders fail to be rolled back during the rollback operation

def fun1(a):
    model = ABuilder()
    state = model.table("user").insert({"username":"Zhang"."sex":'male'."age":18})
    if state:
        state = fun2()
   
    if state:
        model.commit()
    else:
        model.rollback()

def fun2(a):
    model = ABuilder()
    return  model.table("book").insert({"book_name":'Book nickname'."user_id":model.get_insert_id})

fun1()
Copy the code

If fun2() returns a failed state and rolls back only the SQL executed by fun1() but not the SQL executed by fun2(), the correct approach is as follows:

def fun1(a):
    model = ABuilder()
    state = model.table("user").insert({"username":"Zhang"."sex":'male'."age":18})
    if state:
        state = fun2(model)
   
    if state:
        model.commit()
    else:
        model.rollback()

def fun2(model):
    return  model.table("book").insert({"book_name":'Book nickname'."user_id":model.get_insert_id})

fun1()
Copy the code

get_last_sql

Get the last execution SQL

 model = ABuilder()
 find = model.table('user').where({"id": ["=".3]}).first()
 print(model.get_last_sql)
Copy the code

Note: The output SQL is not quoted for the string, so that the database management tool cannot execute.

get_insert_id

Gets the insert auto-increment ID

model = ABuilder()
model.table("user").insert({"username":"Zhang"."sex":'male'."age":18})
print(model.get_insert_id)
Copy the code

Example of class inheritance

from ABuilder.ABuilder import ABuilder
class UserModel(ABuilder):

    def __init__(self):
        self.table_name = 'user'
        
    def user_info(self,user_id):
        info = self.table(self.table_name).field("user_id,user_name").where({"user_id":user_id}).first()
        print(self.get_last_sql)
        return info
        
    def login(self):
        pass
        
userInfo = UserModel().user_info(user_id=1)
print(userInfo)
Copy the code

Case project

Target bookkeeping +

+ charge to an account. The combination of bookkeeping and target, record every transaction anytime and anywhere, human contact, multi-person bookkeeping, daily bookkeeping real-time statistics and target distance, instant understanding of the fund overview of capital flow

Thank you

At the beginning of the project, it is the very first simple version. If there is a problem, if the writing method is not standard, welcome feedback. Thousands of words, you know, I will not say

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Python’s mysql chain operation, SQL builder, says goodbye to native writing

preface

Making the address

Quick start

Using document

Third-party Library Requirements

Database configuration file

Support functions

table

where

where_or

field

limit

group

order

join

first

query

pluck

insert

update

delete

select

Commit and rollback

get_last_sql

get_insert_id

Example of class inheritance

Case project

Target bookkeeping +

Thank you

Python’s mysql chain operation, SQL builder, says goodbye to native writing

preface

Making the address

Quick start

Using document

Third-party Library Requirements

Database configuration file

Support functions

table

where

where_or

field

limit

group

order

join

first

query

pluck

insert

update

delete

select

Commit and rollback

get_last_sql

get_insert_id

Example of class inheritance

Case project

Target bookkeeping +

Thank you

Related Posts

Alipay payment fast integration implementation

203. Remove linked list elements

What is the print directly output by a JAVA object?