class StudyPyMySql:
__db_server = ''
__db_user = ''
__db_password = ''
__db_name = ''
__test_table = 'chat_vcode '
# construct
def __init__(self, spm_server, spm_user, spm_password, spm_name):
self.__db_server = spm_server
self.__db_user = spm_user
self.__db_password = spm_password
self.__db_name = spm_name
# connect db
self.__db = pymysql.connect(self.__db_server, self.__db_user, self.__db_password, self.__db_name)
# 单条数据插入测试
def test_insert(self):
spm_cursor = self.__db.cursor()
sql = """INSERT INTO `%s` \
(`phone`, `vcode`, `code_time`, `inuse`) \
VALUES ('%s ', '%s ', '%s ', '%s ')""" % (self.__test_table, 'testphone ', 'testvcode ', 0, 1)
try:
spm_cursor.execute(sql)
self.__db.commit()
print('test_insert成功')
except Exception as e:
# 如果发生错误则回滚
self.__db.rollback()
print('test_insert失败: %s ' % e)
# 多条数据插入测试
def test_insert_many(self):
spm_cursor = self.__db.cursor()
sql = """INSERT INTO `chat_vcode` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
t = (("testphone1 ", "testvcode1 ", 0, 1),
("testphone2 ", "testvcode2 ", 0, 1),
("testphone3 ", "testvcode3 ", 0, 1))
try:
spm_cursor.executemany(sql, t)
self.__db.commit()
print('test_insert_many成功')
except Exception as e:
# 如果发生错误则回滚
self.__db.rollback()
print('test_insert_many失败: %s ' % e)
# 数据更新操作
def test_update(self):
spm_cursor = self.__db.cursor()
sql = """UPDATE `%s` SET `%s`='%s ' WHERE `%s`='%s '""" % \
(self.__test_table, 'phone ', 'testphone11 ', 'phone ', 'testphone1 ')
try:
spm_cursor.execute(sql)
if spm_cursor.rowcount 0 """ % self.__test_table
try:
spm_cursor.execute(sql)
spm_res = spm_cursor.fetchall()
for row in spm_res:
list_id = row[0]
list_phone = row[1]
list_verify_code = row[2]
list_create_time = row[3]
list_inuse = row[4]
# 打印结果
print("id=%s,phone=%s,verify_code=%s,create_time=%s,inuse=%s " % \
(list_id, list_phone, list_verify_code, list_create_time, list_inuse))
except Exception as e:
print('test_select 失败: %s ' % e)
# 数据库删除操作
def test_delete(self):
spm_cursor = self.__db.cursor()
sql = """DELETE FROM `%s` WHERE `phone`='testphone2 '""" % self.__test_table
try:
spm_cursor.execute(sql)
if spm_cursor.rowcount在执行数据批量插入时,注意sql模板语句,待传参数只能是字段名对应的数值,不能将表名或其他(如字段名)也作为待传参数:
sql = """INSERT INTO `chat_vcode` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
t = (("testphone1 ", "testvcode1 ", 0, 1),
("testphone2 ", "testvcode2 ", 0, 1),
("testphone3 ", "testvcode3 ", 0, 1))
上面这种写法是正确的,看下面的写法:
sql = """INSERT INTO `%s` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
t = (("chat_vcode ", "testphone1 ", "testvcode1 ", 0, 1),
("chat_vcode ", "testphone2 ", "testvcode2 ", 0, 1),
("chat_vcode ", "testphone3 ", "testvcode3 ", 0, 1))
这里我们将表名也作为了待传参数,这样执行会直接报错,无法提交事务。
代码优化,在方法执行完毕后调用 self.__db.close() 或者在析构函数中执行此条语句。
调试
if __name__ == "__main__ ":
spm = StudyPyMySql(
'127.0.0.1 ',
'root ',
'root ',
'studypy '
)
# spm.test_select()
# spm.test_insert()
# spm.test_insert_many()
# spm.test_update()
# spm.test_delete()
代码已经过测试,全部运行正常,python --version = 3.7.4rcl