一、简单封装
import pymysql
class MysqlHelper(object):
conn = None
def __init__(self, host, username, password, db, charset='utf8', port=3306):
self.host = host
self.username = username
self.password = password
self.db = db
self.charset = charset
self.port = port
def connect(self):
self.conn = pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password, db=self.db,
charset=self.charset)
self.cursor = self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
def get_one(self, sql, params=()):
result = None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchone()
self.close()
except Exception as e:
print(e)
return result
def get_all(self, sql, params=()):
list_data = ()
try:
self.connect()
self.cursor.execute(sql, params)
list_data = self.cursor.fetchall()
self.close()
except Exception as e:
print(e)
return list_data
def insert(self, sql, params=()):
return self.__edit(sql, params)
def update(self, sql, params=()):
return self.__edit(sql, params)
def delete(self, sql, params=()):
return self.__edit(sql, params)
def __edit(self, sql, params):
count = 0
try:
self.connect()
count = self.cursor.execute(sql, params)
self.conn.commit()
self.close()
except Exception as e:
print(e)
return count
二、mysql封装
import pymysql
class MysqlHelper(object):
conn = None
def __init__(self,host, username, password, db, charset='utf8', port=3306) -> None:
self.host = host
self.username = username
self.password = password
self.db = db
self.charset = charset
self.port = port
def connect(self):
try:
self.conn = pymysql.connect(host=self.host, port=self.port, user=self.username, password=self.password, db=self.db, charset=self.charset)
self.cursor = self.conn.cursor()
return True
except:
return False
# 关闭数据库
def close(self):
if self.conn and self.cursor:
self.cursor.close()
self.conn.close()
return True
def execute(self, sql, params=None, exe_many=False):
res = self.connect()
if not res:
return False
cnt = 0
try:
if self.conn and self.cursor:
if exe_many:
cnt = self.cursor.executemany(sql, params)
else:
cnt = self.cursor.execute(sql, params)
self.conn.commit()
except Exception as e:
return False
self.close()
return cnt
def insert(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
data ：必填，更新数据，字典类型，如：data={"aaa": "666", "bbb": "888"}
"""
table = kwargs["table"]
data = kwargs["data"]
sql = "insert into %s (" % table
fields = ""
values = []
flag = ""
for k, v in data.items():
fields += "%s," % k
values.append(str(v))
flag += "%s,"
fields = fields.rstrip(",")
values = tuple(values)
flag = flag.rstrip(",")
sql += fields + ") values (" + flag + ");"
try:
self.execute(sql, values)
# 获取自增id
res = self.cursor.lastrowid
return res
except:
self.conn.rollback()
return 0
def delete(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
where：必填，删除条件，字典类型用于=，如：where={"aaa": 333, "bbb": 2}；字符串类型用于非等于判断，如：where="aaa>=333"
"""
table = kwargs["table"]
where = kwargs["where"]
sql = "delete from %s where 1=1" % (table)
values = []
if type(where) == dict:
for k, v in where.items():
sql += " and {} in (%s)".format(k)
values.append(str(v))
elif type(where) == str:
sql += " and %s" % where
sql += ";"
values = tuple(values)
try:
self.execute(sql, values)
rowcount = self.cursor.rowcount
return rowcount
except:
self.conn.rollback()
return 0
def update(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
data ：必填，更新数据，字典类型，如：data={"aaa": "666'6", "bbb": "888"}
where：必填，更新条件，字典类型用于=，如：where={"aaa": 333, "bbb": 2}；字符串类型用于非等于判断，如：where="aaa>=333"
"""
table = kwargs["table"]
data = kwargs["data"]
where = kwargs["where"]
sql = "update %s set " % table
values = []
for k, v in data.items():
sql += "{}=%s,".format(k)
values.append(str(v))
sql = sql.rstrip(",")
sql += " where 1=1 "
if type(where) == dict:
for k, v in where.items():
sql += " and {} in (%s)".format(k)
values.append(str(v))
elif type(where) == str:
sql += " and %s" % where
sql += ";"
values = tuple(values)
try:
self.execute(sql, values)
rowcount = self.cursor.rowcount
return rowcount
except:
self.conn.rollback()
return 0
def get_one(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
where：必填，查询条件，字典类型用于=，如：where={"aaa": 333, "bbb": 2}；字符串类型用于非等于判断，如：where="aaa>=333"
field： 非必填，查询列名，字符串类型，如：field="aaa, bbb"，不填默认*
order： 非必填，排序字段，字符串类型，如：order="ccc"
sort： 非必填，排序方式，字符串类型，如：sort="asc"或者"desc"，不填默认asc
"""
table = kwargs["table"]
field = "field" in kwargs and kwargs["field"] or "*"
where = kwargs["where"]
order = "order" in kwargs and "order by " + kwargs["order"] or ""
sort = kwargs.get("sort", "asc")
if order == "":
sort = ""
sql = "select %s from %s where 1=1 " % (field, table)
values = []
if type(where) == dict:
for k, v in where.items():
sql += " and {} in (%s)".format(k)
values.append(str(v))
elif type(where) == str:
sql += " and %s" % where
sql += " %s %s limit 1;" % (order, sort)
values = tuple(values)
try:
self.execute(sql, values)
data = self.cursor.fetchone()
return data
except Exception as e:
self.conn.rollback()
return ()
def get_all(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
where：必填，查询条件，字典类型用于=，如：where={"aaa": 333, "bbb": 2}；字符串类型用于非等于判断，如：where="aaa>=333"
field： 非必填，查询列名，字符串类型，如：field="aaa, bbb"，不填默认*
order： 非必填，排序字段，字符串类型，如：order="ccc"
sort： 非必填，排序方式，字符串类型，如：sort="asc"或者"desc"，不填默认asc
offset：非必填，偏移量，如翻页，不填默认0
limit： 非必填，条数，不填默认100
"""
table = kwargs["table"]
field = "field" in kwargs and kwargs["field"] or "*"
order = "order" in kwargs and "order by " + kwargs["order"] or ""
sort = kwargs.get("sort", "asc")
if order == "":
sort = ""
where = kwargs["where"]
offset = kwargs.get("offset", 0)
limit = kwargs.get("limit", 100)
sql = "select %s from %s where 1=1 " % (field, table)
values = []
if type(where) == dict:
for k, v in where.items():
sql += " and {} in (%s)".format(k)
values.append(str(v))
elif type(where) == str:
sql += " and %s" % where
values = tuple(values)
sql += " %s %s limit %s, %s;" % (order, sort, offset, limit)
try:
self.execute(sql, values)
data = self.cursor.fetchall()
return data
except:
self.conn.rollback()
return ()
def get_count(self, **kwargs):
"""
table：必填，表名，如：table="test_table"
where：必填，查询条件，字典类型用于=，如：where={"aaa": 333, "bbb": 2}；字符串类型用于非等于判断，如：where="aaa>=333"
"""
table = kwargs["table"]
where = kwargs["where"]
sql = "select count(1) as count from %s where 1=1 " % (table)
values = []
if type(where) == dict:
for k, v in where.items():
sql += " and {} in (%s)".format(k)
values.append(str(v))
elif type(where) == str:
sql += " and %s;" % where
values = tuple(values)
try:
self.execute(sql, values)
data = self.cursor.fetchone()
return data[0]
except:
self.conn.rollback()
return 0
