本篇文章重点介绍通过 PyMySQL 帮助程序在 Python 中处理 SQL 的最佳实践。此外，我们将使用该configparser模块来存储我们数据库的配置设置。
本文有 5个部分。
1、安装PyMySQL模块跟configparser模块
2、使用配置文件管理数据库连接
3、基本 API 调用
4、我常用的PyMySQL类文件Tips
5、总结
安装PyMySQL跟ConfigParse模块
pip install pymysql
pip install configparser使用配置文件管理数据库连接
创建一个名为mysql_db文件夹然后在文件夹内创建一个名为的新文件mysql_config.ini。打开文件并填写数据库的详细信息。
[db_dev]
host=192.168.1.1
port=5000
user=admin
password=admin
db_name=development
[db_online]
host=192.168.1.2
port=5000
user=admin
password=admin
db_name=production你可以通过修改此配置文件轻松更改与特定数据库的连接。它为你省去了从开发服务器迁移到生产服务器时可能出现的任何潜在问题的麻烦和风险。
基本API调用
在同一文件夹 ( mysql_db) 中，创建一个名为db_connect.py
- 初始化数据库连接
__init__函数将在初始化期间被调用。首先，我们将创建一个ConfigParser用于读取mysql_config.ini文件的对象。然后，我们将解析必要的设置并连接到数据库。
try:
import configparser
except:
from six.moves import configparser
import pymysql
class MySqlConnector(object):
def __init__(self, env=None):
cf = configparser.ConfigParser()
cf.read('mysql_db/mysql_config.ini')
if not env:
db_env = ''
else:
db_env = env
sec = 'db_{0}'.format(db_env)
self.db_host = cf.get(sec, 'host')
self.db_port = cf.getint(sec, 'port')
self.db_user = cf.get(sec, 'user')
self.db_pwd = cf.get(sec, 'password')
self.db_name = cf.get(sec,'db_name')
self._connect = pymysql.connect(host=self.db_host,
port=int(self.db_port),
user=self.db_user,
password=self.db_pwd,
charset='utf8',
db=self.db_name,
init_command='set names utf8')- 查询所有数据(queryall)
此函数用作SELECT从数据库中读取数据。它接受两个参数：
- sql – 读取操作的 SQL 字符串。
- params – 用户定义变量的附加参数。
注意 ：必须使用close函数来关闭cursor
def queryall(self, sql, params=None):
cursor = self._connect.cursor()
try:
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
result = cursor.fetchall()
finally:
cursor.close()
return result除此之外，你可以使用上下文管理器来避免必须关闭cursor. 上面的函数可以写成如下
def queryall(self, sql, params=None):
with connection.cursor() as cursor:
try:
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
result = cursor.fetchall()
return result请注意，我们正在使用cursor.fetchall函数调用。如果只想查询第一项，可以调用该cursor.fetchone函数，但强烈建议设置LIMIT在 SQL 字符串中。
更新
该函数负责其余的 CRUD 操作。它接受与函数相同的参数queryall。唯一的区别是它将调用该commit函数，因为默认情况下不会自动提交连接。你必须承诺保存所做的任何更改。
def update(self, sql, params=None):
cursor = self._connect.cursor()
try:
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
self._connect.commit()
finally:
cursor.close()- 执行更新数据
update_string = '''update ai_model set status = 1 where model_id = %s;'''
mysql_con.update(update_string, (model_id))- 执行插入数据
insert_string = '''insert into ai_model(model_id,port,language) values(%s,%s,%s);'''
mysql_con.update(insert_string, (model_id, port, lang))- 执行删除数据
delete_string = '''delete from ai_model where model_id = %s;'''
mysql_con.update(delete_string, (model_id))关闭连接
最后一个函数有助于关闭数据库。每次完成查询时都必须关闭它。强烈建议尽量减少与 SQL 数据库的连接。想办法查询所有内容并手动处理它，而不是在一个for循环中进行多个 CRUD 操作。
def close(self):
if self._connect:
self._connect.close()我常用的PyMySQL类文件Tips
这是我自己用的连接数据库的类文件，如果你开始使用PyMYSQL想找相关的代码tips，请复制粘贴。
import sys
import pymysql
import logging
try:
import configparser
except:
from six.moves import configparser
class Database:
"""Database connection class."""
def __init__(self, env=None):
cf = configparser.ConfigParser()
cf.read('mysql_db/mysql_config.ini')
if not env:
db_env = ''
else:
db_env = env
sec = 'db_{0}'.format(db_env)
self.db_host = cf.get(sec, 'host')
self.db_port = cf.getint(sec, 'port')
self.db_user = cf.get(sec, 'user')
self.db_pwd = cf.get(sec, 'password')
self.db_name = cf.get(sec,'db_name')
self._connect = pymysql.connect(host=self.db_host,
port=int(self.db_port),
user=self.db_user,
password=self.db_pwd,
charset='utf8',
db=self.db_name,
init_command='set names utf8')
def open_connection(self):
"""Connect to MySQL Database."""
try:
if self.conn is None:
self.conn = pymysql.connect(self.host,
user=self.username,
passwd=self.password,
db=self.dbname,
connect_timeout=5)
except pymysql.MySQLError as e:
logging.error(e)
sys.exit()
finally:
logging.info('Connection opened successfully.')
def run_query(self, query):
"""Execute SQL query."""
try:
self.open_connection()
with self.conn.cursor() as cur:
if 'SELECT' in query:
records = []
cur.execute(query)
result = cur.fetchall()
for row in result:
records.append(row)
cur.close()
return records
else:
result = cur.execute(query)
self.conn.commit()
affected = f"{cur.rowcount} rows affected."
cur.close()
return affected
except pymysql.MySQLError as e:
print(e)
finally:
if self.conn:
self.conn.close()
self.conn = None
logging.info('Database connection closed.')总结
让我们回顾一下今天所学的内容。我们从一个简单的设置开始，安装了两个 python 模块，configparser以及pymysql.
然后，我们继续为数据库创建配置文件。有了配置文件，我们就可以轻松地切换数据库，而不会遇到太多麻烦。如果你想了解更多信息，请查看我之前关于处理配置文件的文章：Python ConfigParser：配置文件变得简单
接下来，我们创建了一个 PyMySQL 帮助文件，用于使用配置文件中的设置初始化连接。我们在帮助文件中添加了一些函数来查询、更新和关闭数据库连接。
我们还详细探讨了如何使用帮助文件进行 CRUD 操作。我们尝试了几个示例来从ai_model表中选择、更新、插入和删除项目。
