python中mysql的封装

103 阅读 0 评论 68 点赞

我是靠谱客的博主无聊鸡，这篇文章主要介绍python中mysql的封装，现在分享给大家，希望可以做个参考。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
import pymysql
# 1、建立连接
conn = pymysql.connect(host=host,
user=user,
password=password,
db=db,
port=port,
charset="utf8",
cursorclass=pymysql.cursors.DictCursor)
# 2、创建游标
cursor = conn.cursor()
# 3、执行sql语句
# sql = "SELECT * from member LIMIT 10;"
sql = "SELECT * from member WHERE LeaveAmount > %s LIMIT 0,3;"
cursor.execute(sql, args=(400,))
# 4、需要手动提交
conn.commit()
# 5、获取sql执行结果
result1 = cursor.fetchone()
# 返回所有记录中的第一条记录组成的字典
result2 = cursor.fetchall()
# 返回所有记录中的所有记录组成嵌套字典的列表
print(result1)
print("=========")
print(result2)
# 关闭连接
cursor.close()
# 先关闭游标
conn.close()
# 再关闭链接

复制代码

import pymysql
from py2020.fz.handle_config import do_config
class HandleMySql:
"""
处理数据库
"""
def __init__(self):
self.conn = pymysql.connect(host=do_config("mysql", "host"),
user=do_config("mysql", "user"),
password=do_config("mysql", "password"),
db=do_config("mysql", "db"),
port=do_config("mysql", "port"),
charset=do_config("mysql", "charset"),
cursorclass=pymysql.cursors.DictCursor)
# 定义游标
self.cursor = self.conn.cursor()
# def get_value(self,sql,arg=None):
#
"""
#
获取单个值
#
:param sql:
#
:param arg:
#
:return:
#
"""
#
self.cursor.execute(sql, arg)
#
self.conn.commit()
#
#
return self.cursor.fetchone()
#
#
# def get_values(self,sql,arg=None):
#
"""
#
获取多个值
#
:param sql:
#
:param arg:
#
:return:
#
"""
#
self.cursor.execute(sql,arg)
#
self.conn.commit()
#
#
return self.cursor.fetchall()
def __call__(self, sql, arg=None, is_more=False):
"""
:param sql: sql语句，字符类型
:param arg: sql语句的参数，为序列类型
:param is_more: False or True
:return: 字典类型或者嵌套字典的列表
"""
self.cursor.execute(sql, arg)
self.conn.commit()
if is_more:
result = self.cursor.fetchall()
else:
result = self.cursor.fetchone()
return result
def close(self):
"""
关闭连接
:return:
"""
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
sql_1 = "SELECT * from member LIMIT 10;"
sql_2 = "SELECT * from member WHERE LeaveAmount > %s LIMIT 0,3;"
do_mysql = HandleMySql()
print(do_mysql(sql=sql_1, is_more=True))
print(do_mysql(sql=sql_2, arg=(600,), is_more=True))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
import pymysql
from py2020.fz.handle_config import do_config
class HandleMySql:
"""
处理数据库
"""
def __init__(self):
self.conn = pymysql.connect(host=do_config("mysql", "host"),
user=do_config("mysql", "user"),
password=do_config("mysql", "password"),
db=do_config("mysql", "db"),
port=do_config("mysql", "port"),
charset=do_config("mysql", "charset"),
cursorclass=pymysql.cursors.DictCursor)
# 定义游标
self.cursor = self.conn.cursor()
# def get_value(self,sql,arg=None):
#
"""
#
获取单个值
#
:param sql:
#
:param arg:
#
:return:
#
"""
#
self.cursor.execute(sql, arg)
#
self.conn.commit()
#
#
return self.cursor.fetchone()
#
#
# def get_values(self,sql,arg=None):
#
"""
#
获取多个值
#
:param sql:
#
:param arg:
#
:return:
#
"""
#
self.cursor.execute(sql,arg)
#
self.conn.commit()
#
#
return self.cursor.fetchall()
def __call__(self, sql, arg=None, is_more=False):
"""
:param sql: sql语句，字符类型
:param arg: sql语句的参数，为序列类型
:param is_more: False or True
:return: 字典类型或者嵌套字典的列表
"""
self.cursor.execute(sql, arg)
self.conn.commit()
if is_more:
result = self.cursor.fetchall()
else:
result = self.cursor.fetchone()
return result
def close(self):
"""
关闭连接
:return:
"""
self.cursor.close()
self.conn.close()
if __name__ == '__main__':
sql_1 = "SELECT * from member LIMIT 10;"
sql_2 = "SELECT * from member WHERE LeaveAmount > %s LIMIT 0,3;"
do_mysql = HandleMySql()
print(do_mysql(sql=sql_1, is_more=True))
print(do_mysql(sql=sql_2, arg=(600,), is_more=True))