I suggest you go straight to my github first, here: https://github.com/xiaofeipapa/python_example
It is full of code that can be tested after being written:
Download the code, click the left chicken wing and the right mouse to run. It will take 5 minutes to learn. Isn't it beautiful!
Initialization preparation
Install pymysql package
sudo pip3 install PyMysql
Then create a database named my in mysql_ Test, user name / password is also my_test and create the Product table as follows:
DROP TABLE IF EXISTS `Product`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Product` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL, /* Trade name */
`remark` varchar(1000) NULL,
`isBuy` int(1) DEFAULT 1, /* 1: On sale 2: sold */
`version` int(11) NOT null default 1000,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
test
The code of pymysql is still very simple. The following codes are fixed routines: connect to mysql, obtain connection, and obtain cursor from connection for operation:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
import pymysql
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
# ----Operating database with pymysql
def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn
def check_it():
conn = get_connection()
# Use the cursor() method to create a cursor object in dict format
cursor = conn.cursor(pymysql.cursors.DictCursor)
# Execute the SQL query using the execute() method
cursor.execute("select count(id) as total from Product")
# Use the fetchone() method to get a single piece of data
data = cursor.fetchone()
print("-- Current quantity: %d " % data['total'])
# Close database connection
cursor.close()
conn.close()
if __name__ == '__main__':
check_it()
Practical application of pymysql
Optimize operation code with
As can be seen from the above code, if you want to open the connection every time, close the connection The code is ugly and error prone The best way is to add a context manager in python with Amend as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
import pymysql
from timeit import default_timer
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
# ----Operating database with pymysql
def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn
# ----Use with to optimize your code
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='Total time'):
"""
:param commit: Commit transaction last(Set to False Facilitate unit testing when)
:param log_time: Print the total running time of the program
:param log_label: custom log Text
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# Record the time if necessary
if self._log_time is True:
self._start = default_timer()
# Automatically obtain the connection and cursor when entering
conn = get_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# Commit transaction
if self._commit:
self._conn.commit()
# Automatically close the connection and cursor when exiting
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f second' % (self._log_label, diff))
@property
def cursor(self):
return self._cursor
def check_it():
with UsingMysql(log_time=True) as um:
um.cursor.execute("select count(id) as total from Product")
data = um.cursor.fetchone()
print("-- Current quantity: %d " % data['total'])
if __name__ == '__main__':
check_it()
The running results of the program are as follows:
-- Current quantity: 0 -- Time use: 0.002345 second
After rewriting the code in this way, the business method is more streamlined And add parameters to facilitate unit testing and monitor the running time of the code
Encapsulate common code
Now add a new pymysql_comm.py class. Put the connection code and written UsingMysql into it. The code is as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
import pymysql
from timeit import default_timer
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
# ----Operating database with pymysql
def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn
# ----Use with to optimize your code
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='Total time'):
"""
:param commit: Commit transaction last(Set to False Facilitate unit testing when)
:param log_time: Print the total running time of the program
:param log_label: custom log Text
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# Record the time if necessary
if self._log_time is True:
self._start = default_timer()
# Automatically obtain the connection and cursor when entering
conn = get_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# Commit transaction
if self._commit:
self._conn.commit()
# Automatically close the connection and cursor when exiting
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f second' % (self._log_label, diff))
@property
def cursor(self):
return self._cursor
Add a test Py file, introduce this module for testing The code is as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def check_it():
with UsingMysql(log_time=True) as um:
um.cursor.execute("select count(id) as total from Product")
data = um.cursor.fetchone()
print("-- Current quantity: %d " % data['total'])
if __name__ == '__main__':
check_it()
This encapsulation class can be used for subsequent learning and development, using a similar test Py way to write their own business code, more convenient and streamlined
Add, delete, modify and query api
The following records the most commonly used methods such as adding, deleting, modifying, querying and paging
Add a single record
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def select_one(cursor):
cursor.execute("select * from Product")
data = cursor.fetchone()
print("-- single record : {0} ".format(data))
# Add a single record
def create_one():
with UsingMysql(log_time=True) as um:
sql = "insert into Product(name, remark) values(%s, %s)"
params = ('Men's backpack 1', 'This is a very good backpack')
um.cursor.execute(sql, params)
# View results
select_one(um.cursor)
if __name__ == '__main__':
create_one()
In the above code, first add a record, and then view the record. The result is similar to this:
-- single record : {'id': 1003, 'name': 'Men's backpack 1', 'isBuy': 1, 'remark': 'This is a very good backpack'}
-- Time use: 0.002600 second
By the way, it can be calculated that the concurrency number is 384.6 with 1 second / 0.0026, which means that about 384 records are inserted per second without optimization, and the performance is relatively low
Add multiple records
Insert 1000 records at one time and add the query method as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def get_count(cursor):
cursor.execute("select count(id) as total from Product")
# Use the fetchone() method to get a single piece of data
data = cursor.fetchone()
print("-- Current quantity: %d " % data['total'])
def delete_all(cursor):
cursor.execute("delete from Product")
# Insert 1000 records
def create_many():
with UsingMysql(log_time=True) as um:
# Clear previous test records
delete_all(um.cursor)
for i in range(0, 1000):
sql = "insert into Product(name, remark) values(%s, %s)"
params = ('Men's backpack%d' % i, 'This is a very good backpack%d' %i)
um.cursor.execute(sql, params)
# View results
get_count(um.cursor)
if __name__ == '__main__':
create_many()
The time used in my machine is as follows:
-- Current quantity: 1000 -- Time use: 0.097566 second
Barely acceptable Now use your mysql client to view the database. You should be able to see 1000 pieces of data:
Delete a record
In order to facilitate the test, the checking method is also written in advance The code is as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def delete_one(cursor, name):
sql = 'delete from Product where name = %s'
params = name
cursor.execute(sql, params)
print('--- Deleted name is%s Commodity. ' % name)
def select_one(cursor):
sql = 'select * from Product'
cursor.execute(sql)
data = cursor.fetchone()
print('--- Found with name%s Commodity. ' % data['name'])
return data['name']
def select_one_by_name(cursor, name):
sql = 'select * from Product where name = %s'
params = name
cursor.execute(sql, params)
data = cursor.fetchone()
if data:
print('--- Found with name%s Commodity. ' % data['name'])
else:
print('--- Name is%s Our goods are gone' % name)
# Delete a single record
def check_delete_one():
with UsingMysql(log_time=True) as um:
# Find a record
name = select_one(um.cursor)
# Delete
delete_one(um.cursor, name)
# Check if it's still there?
select_one_by_name(um.cursor, name)
if __name__ == '__main__':
check_delete_one()
The operation results are similar:
--- Found item named men's backpack 0. --- Deleted item named men's backpack 0. --- The item named men's backpack 0 is no longer available -- Time use: 0.015917 second
Modify record
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def update_by_pk(cursor, name, pk):
sql = "update Product set name = '%s' where id = %d" % (name, pk)
cursor.execute(sql)
def select_one(cursor):
sql = 'select * from Product'
cursor.execute(sql)
return cursor.fetchone()
def select_one_by_name(cursor, name):
sql = 'select * from Product where name = %s'
params = name
cursor.execute(sql, params)
data = cursor.fetchone()
if data:
print('--- Found with name%s Commodity. ' % data['name'])
else:
print('--- Name is%s Our goods are gone' % name)
# Modify record
def check_update():
with UsingMysql(log_time=True) as um:
# Find a record
data = select_one(um.cursor)
pk = data['id']
print('--- commodity{0}: '.format(data))
# Modify name
new_name = 'The single shoulder bag'
update_by_pk(um.cursor, new_name, pk)
# see
select_one_by_name(um.cursor, new_name)
if __name__ == '__main__':
check_update()
The method of modifying records according to id is recorded here. Other modification methods mainly depend on sql knowledge and will not be repeated
lookup
Find fetchone (return a single piece of data) and fetchall (return all data) related to pymysql Fetchone has been written above. Now let's look at the fetchall method:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def fetch_list_by_filter(cursor, pk):
sql = 'select * from Product where id > %d' % pk
cursor.execute(sql)
data_list = cursor.fetchall()
print('-- total: %d' % len(data_list))
return data_list
# lookup
def fetch_list():
with UsingMysql(log_time=True) as um:
# Find records with id greater than 800
data_list = fetch_list_by_filter(um.cursor, 800)
# Find records with id greater than 10000
data_list = fetch_list_by_filter(um.cursor, 10000)
if __name__ == '__main__':
fetch_list()
The results should look like this:
-- total: 999 -- total: 0 -- Time use: 0.012355 second
Paging query
Paging query mainly uses the limit feature of mysql, which has nothing to do with pymysql. The code is as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_comm import UsingMysql
def fetch_page_data(cursor, pk, page_size, skip):
sql = 'select * from Product where id > %d limit %d,%d' % (pk, skip, page_size)
cursor.execute(sql)
data_list = cursor.fetchall()
print('-- total: %d' % len(data_list))
print('-- data: {0}'.format(data_list))
return data_list
# lookup
def check_page():
with UsingMysql(log_time=True) as um:
page_size = 10
pk = 500
for page_no in range(1, 6):
print('====== Number%d Page data' % page_no)
skip = (page_no - 1) * page_size
fetch_page_data(um.cursor, pk, page_size, skip)
if __name__ == '__main__':
check_page()
Five pages of data are listed above It looks like this:
Intermediate: using connection pooling and encapsulation methods
After a series of examples, you should now be able to use pymysql to do the most basic addition, deletion, modification, query and paging Now let's look at the advanced functions: better encapsulation of code and use of database connection pool
Encapsulation code
We found that the code for calling pymysql is almost the same. In fact, we can move it to a public method and add a new pymysql_lib_1.py file to implement UsingMysql as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
import pymysql
from timeit import default_timer
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
# ----Operating database with pymysql
def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn
# ----Use with to optimize your code
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='Total time'):
"""
:param commit: Commit transaction last(Set to False Facilitate unit testing when)
:param log_time: Print the total running time of the program
:param log_label: custom log Text
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# Record the time if necessary
if self._log_time is True:
self._start = default_timer()
# Automatically obtain the connection and cursor when entering
conn = get_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# Commit transaction
if self._commit:
self._conn.commit()
# Automatically close the connection and cursor when exiting
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f second' % (self._log_label, diff))
# =========A series of encapsulated business methods
# Return count
def get_count(self, sql, params=None, count_key='count(id)'):
self.cursor.execute(sql, params)
data = self.cursor.fetchone()
if not data:
return 0
return data[count_key]
def fetch_one(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchone()
def fetch_all(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchall()
def fetch_by_pk(self, sql, pk):
self.cursor.execute(sql, (pk,))
return self.cursor.fetchall()
def update_by_pk(self, sql, params=None):
self.cursor.execute(sql, params)
@property
def cursor(self):
return self._cursor
Then add a new test2 Py file, as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from pymysql_lib_1 import UsingMysql
def check_it():
with UsingMysql(log_time=True) as um:
sql = "select count(id) as total from Product"
print("-- Current quantity: %d " % um.get_count(sql, None, 'total'))
if __name__ == '__main__':
check_it()
You can see that the business code has been simplified a lot. Just spell sql and parameters. Other calling methods are encapsulated in the context manager
Use connection pool
In the above use process, each request will open a database connection If there are too many connections, the database will soon report an error How to adjust the number of database connections and increase concurrency performance is a technical topic, which I intend to introduce in the advanced chapter Now here's what you need to know: the database can't return connections like this. You must use the connection pool
Of course, you don't have to do it yourself. The world of python is so big. First install DBUtils, as follows:
pip3 install DBUtils
Then add pymysql_lib.py, add the following code:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
import pymysql
from timeit import default_timer
from DBUtils.PooledDB import PooledDB
class DMysqlConfig:
"""
:param mincached:The initial number of free connections in the connection pool
:param maxcached:The maximum number of free connections in the connection pool
:param maxshared:Maximum number of shared connections
:param maxconnections:Maximum number of connection pools created
:param blocking:The performance when the maximum number of connections is exceeded is True Waiting for the number of connections to decrease false Direct error reporting
:param maxusage:Maximum number of reuses of a single connection
:param setsession:optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
:param reset:how connections should be reset when returned to the pool
(False or None to rollback transcations started with begin(),
True to always issue a rollback for safety's sake)
:param host:database ip address
:param port:Database port
:param db:Library name
:param user:user name
:param passwd:password
:param charset:Character encoding
"""
def __init__(self, host, db, user, password, port=3306):
self.host = host
self.port = port
self.db = db
self.user = user
self.password = password
self.charset = 'UTF8' # It cannot be utf-8
self.minCached = 10
self.maxCached = 20
self.maxShared = 10
self.maxConnection = 100
self.blocking = True
self.maxUsage = 100
self.setSession = None
self.reset = True
# ----Use connection pool to return database connection
class DMysqlPoolConn:
__pool = None
def __init__(self, config):
if not self.__pool:
self.__class__.__pool = PooledDB(creator=pymysql,
maxconnections=config.maxConnection,
mincached=config.minCached,
maxcached=config.maxCached,
maxshared=config.maxShared,
blocking=config.blocking,
maxusage=config.maxUsage,
setsession=config.setSession,
charset=config.charset,
host=config.host,
port=config.port,
database=config.db,
user=config.user,
password=config.password,
)
def get_conn(self):
return self.__pool.connection()
# ==========Initialize a connection pool at the beginning of the program
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
db_config = DMysqlConfig(host, db, user, password, port)
g_pool_connection = DMysqlPoolConn(db_config)
# ----Use with to optimize your code
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='Total time'):
"""
:param commit: Commit transaction last(Set to False Facilitate unit testing when)
:param log_time: Print the total running time of the program
:param log_label: custom log Text
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# Record the time if necessary
if self._log_time is True:
self._start = default_timer()
# Get database connection from connection pool
conn = g_pool_connection.get_conn()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# Commit transaction
if self._commit:
self._conn.commit()
# Automatically close the connection and cursor when exiting
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f second' % (self._log_label, diff))
# =========A series of encapsulated business methods
# Return count
def get_count(self, sql, params=None, count_key='count(id)'):
self.cursor.execute(sql, params)
data = self.cursor.fetchone()
if not data:
return 0
return data[count_key]
def fetch_one(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchone()
def fetch_all(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchall()
def fetch_by_pk(self, sql, pk):
self.cursor.execute(sql, (pk,))
return self.cursor.fetchall()
def update_by_pk(self, sql, params=None):
self.cursor.execute(sql, params)
@property
def cursor(self):
return self._cursor
The newly added code looks like a lot, but it only adds two configuration classes Also here:
# ==========Initialize a connection pool at the beginning of the program host = 'localhost' port = 3306 db = 'mysql_test' user = 'mysql_test' password = 'mysql_test' db_config = DMysqlConfig(host, db, user, password, port) g_pool_connection = DMysqlPoolConn(db_config)
Instantiated connection pool Subsequent context managers get connections from the connection pool, and other codes remain unchanged
Put this into mysql_ Save lib and have the opportunity to slowly add / modify various fetch/update Method, this file will become your heirloom, and you will use it to deal with mysql for many, many years
The last serious question: raw sql? Use or abandon?
From using mysql, we can see that code optimization has come to an end But those insert statements and update statements still need to spell a lot of sql fields. What should I do? There are two ways: one is to think about some code generation techniques and automatically assemble sql according to various parameters. In this way, the code will become its own unique orm (I did this when I was young) Another option (that is, my current choice) is to use Sqlalchemy instead of pymysql- D :-D :-D
I seldom use Mysql in my work. I usually take over other people's code So I usually do this: I use sqlalchemy for simple business code without using my brain Where there are performance bottlenecks, I use pymysql native sql to operate Because there are few good articles on pymysql online, I wrote such a big lump to summarize
Getting started with sqlchemy
Add a new sqlal_comm.py class, the code is as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Text
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from timeit import default_timer
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
g_mysql_url = 'mysql+pymysql://%s:%s@%s:%d/%s' % (user, password, host, port, db)
engine = create_engine(g_mysql_url)
Base = declarative_base()
class Product(Base):
__tablename__ = 'Product'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(40))
remark = Column(String(1000), nullable=True)
isBuy = Column(Integer, default=1)
Base.metadata.create_all(engine) #Create table
Session = sessionmaker(bind=engine)
# ===============The above is to initialize the database and tables
# ----Use with to optimize your code
class UsingAlchemy(object):
def __init__(self, commit=True, log_time=True, log_label='Total time'):
"""
:param commit: Commit transaction last(Set to False Facilitate unit testing when)
:param log_time: Print the total running time of the program
:param log_label: custom log Text
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
self._session = Session()
def __enter__(self):
# Record the time if necessary
if self._log_time is True:
self._start = default_timer()
return self
def __exit__(self, *exc_info):
# Commit transaction
if self._commit:
self._session.commit()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f second' % (self._log_label, diff))
@property
def session(self):
return self._session
This file is divided into two parts: the upper part is the fixed routine of SQL Alchemy: splicing connection strings, connection initialization, and then initializing the database table The next part is to continue the previous context management routine to make code writing easier
Add a new test4 Py, as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from sqlal_comm import Session, Product, UsingAlchemy
# Test get a record
def check_it():
session = Session()
result = session.query(Product).first()
if result is None:
session.commit()
return None
session.commit()
session.close()
print('-- Get recorded: {0}'.format(result))
# Test get a record
def check_it_2():
with UsingAlchemy() as ua:
result = ua.session.query(Product).first()
print('-- Get recorded: {0}'.format(result))
if __name__ == '__main__':
check_it()
check_it_2()
This file is called in two ways. Obviously, it will be easier to use context management
SQL alchemy conditional query and paging
There is an excellent blog, which summarizes the addition, deletion, modification and investigation clearly So I was lazy and released his article directly at the end Let me add two things he didn't write: conditional query and paged query
Condition query
The main business scenario is: when users pass in multiple parameters, different query conditions should be constructed according to different parameters Add a new python file as follows:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from sqlal_comm import Session, Product, UsingAlchemy
is_available = 1
# Reinsert data
def re_insert_data():
with UsingAlchemy() as ua:
# Delete all data
ua.session.query(Product).filter(Product.id > 0).delete()
data_list = []
for i in range(0, 10):
data = Product()
data.name = 'Backpack%d' % i
data.remark = 'Backpack%d' % i
data.isBuy = is_available
data_list.append(data)
# Batch add data
ua.session.add_all(data_list)
# Test condition query
def check_search(keyword):
re_insert_data()
with UsingAlchemy() as ua:
# List combination of multiple conditions
query_filter = []
if keyword:
like_value = '%{}%'.format(keyword)
# The query name and remark fields contain records of query keywords
query_filter.append(Product.name.like(like_value) | Product.remark.like(like_value))
# Add another query condition as a test
query_filter.append(Product.isBuy == is_available)
# Find results
data_list = ua.session.query(Product).filter(*query_filter).all()
print('-- Number of records: {}'.format(len(data_list)))
print('-- The record is: %s' % data_list[0].name)
if __name__ == '__main__':
check_search(3)
The presentation of this document is divided into two steps:
- Delete the data in the original database and insert 10 new test data This ensures that there must be a piece of data with keyword 3
- Demonstrates how to combine query criteria for finding, including a search condition with or Starting with this example, all queries are not difficult for you
The program should run like this:
-- Total time: 0.009106 second -- Number of records: 1 -- The record is: Backpack 3 -- Total time: 0.001323 second
Paging lookup
Add a new python file with the following code:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
author: Little fat Baba
Jian Shu: https://www.jianshu.com/u/db796a501972
mailbox: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
You can reprint it at will, Please keep me as the original author, thank you.
"""
from sqlal_comm import Session, Product, UsingAlchemy
is_available = 1
# Reinsert data
def re_insert_data():
with UsingAlchemy() as ua:
# Delete all data
ua.session.query(Product).filter(Product.id > 0).delete()
data_list = []
for i in range(0, 10):
data = Product()
data.name = 'Backpack%d' % i
data.remark = 'Backpack%d' % i
data.isBuy = is_available
data_list.append(data)
# Batch add data
ua.session.add_all(data_list)
# Test paging lookup
def check_search(page_no, page_size):
re_insert_data()
with UsingAlchemy() as ua:
# List combination of multiple conditions
query_filter = list()
# Add another query condition as a test
query_filter.append(Product.isBuy == is_available)
offset = (page_no - 1) * page_size
# Find results
data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()
print('=== Number of records: {}'.format(len(data_list)))
for data in data_list:
print('-- record: ' + data.name)
if __name__ == '__main__':
page_size = 5
for page_no in range(1, 3):
check_search(page_no, page_size)
You can see that paging lookup calls limit and offset before calling lists.
data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()
Therefore, it is also very simple
sqlalchemy addition, deletion, modification and query summary
This article is well written. Look here: https://www.cnblogs.com/pycode/p/mysql-orm.html
Finally, this is really the last
The basic knowledge of python using mysql is summarized here When I have time, I'll write an advanced article on transaction locking and optimizing concurrency performance
The complete code of this article is placed in my github, where: https://github.com/xiaofeipapa/python_example
Boy, remember to give a star~