python 集成mysql

初始化准备
安装pymysql 包

sudo pip3 install PyMysql
然后在mysql里创建数据库名称为 my_test, 用户名/密码也是 my_test , 并创建 Product 表如下:

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,    /* 商品名称 */
  `remark` varchar(1000) NULL,
  `isBuy` int(1) DEFAULT 1,      /* 1: 在售 2:卖出 */
  `version` int(11) NOT null default 1000, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

测试
pymysql 的代码还是很简单的, 以下代码分别为连接mysql 获得connection, 从connection 获得cursor 进行操作, 都是固定套路:

#! /usr/bin/python

-- coding: UTF-8 --

import pymysql

host = ‘localhost’
port = 3306
db = ‘mysql_test’
user = ‘mysql_test’
password = ‘mysql_test’

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

# 使用 cursor() 方法创建一个 dict 格式的游标对象 cursor
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 使用 execute()  方法执行 SQL 查询
cursor.execute("select count(id) as total from Product")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print("-- 当前数量: %d " % data['total'])

# 关闭数据库连接
cursor.close()
conn.close()

if name == ‘main’:
check_it()
pymysql 实战应用
使用with 优化操作代码
从以上代码可以看到, 如果每次都要打开连接, 关闭连接 … 代码难看且容易出错. 最好的办法是用 python with 的方式来增加一个上下文管理器. 修改如下:

#! /usr/bin/python

-- coding: UTF-8 --

import pymysql
from timeit import default_timer

host = ‘localhost’
port = 3306
db = ‘mysql_test’
user = ‘mysql_test’
password = ‘mysql_test’

---- 用pymysql 操作数据库

def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn

---- 使用 with 的方式来优化代码

class UsingMysql(object):

def __init__(self, commit=True, log_time=True, log_label='总用时'):
    """

    :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
    :param log_time:  是否打印程序运行总时间
    :param log_label:  自定义log的文字
    """
    self._log_time = log_time
    self._commit = commit
    self._log_label = log_label

def __enter__(self):

    # 如果需要记录时间
    if self._log_time is True:
        self._start = default_timer()

    # 在进入的时候自动获取连接和cursor
    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):
    # 提交事务
    if self._commit:
        self._conn.commit()
    # 在退出的时候自动关闭连接和cursor
    self._cursor.close()
    self._conn.close()

    if self._log_time is True:
        diff = default_timer() - self._start
        print('-- %s: %.6f 秒' % (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("-- 当前数量: %d " % data['total'])

if name == ‘main’:
check_it()
程序运行结果如下:

– 当前数量: 0
– 用时: 0.002345 秒
用这种方式改写代码之后, 业务方法更精简. 并且加了参数方便进行单元测试和监控代码的运行时间, 不亦美哉.

封装公用代码
现在新增一个pymysql_comm.py 类, 将连接代码和写好的UsingMysql 放进去, 代码如下:

#! /usr/bin/python

-- coding: UTF-8 --

import pymysql
from timeit import default_timer

host = ‘localhost’
port = 3306
db = ‘mysql_test’
user = ‘mysql_test’
password = ‘mysql_test’

---- 用pymysql 操作数据库

def get_connection():
conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
return conn

---- 使用 with 的方式来优化代码

class UsingMysql(object):

def __init__(self, commit=True, log_time=True, log_label='总用时'):
    """

    :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
    :param log_time:  是否打印程序运行总时间
    :param log_label:  自定义log的文字
    """
    self._log_time = log_time
    self._commit = commit
    self._log_label = log_label

def __enter__(self):

    # 如果需要记录时间
    if self._log_time is True:
        self._start = default_timer()

    # 在进入的时候自动获取连接和cursor
    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):
    # 提交事务
    if self._commit:
        self._conn.commit()
    # 在退出的时候自动关闭连接和cursor
    self._cursor.close()
    self._conn.close()

    if self._log_time is True:
        diff = default_timer() - self._start
        print('-- %s: %.6f 秒' % (self._log_label, diff))

@property
def cursor(self):
    return self._cursor

新增一个 test.py 文件, 引入这个模块进行测试使用. 代码如下:

#! /u