OptionMysql方法使用
在python中,常用的web框架有flask和django,后端和前端进行交互时,使用django框架时,django自带的ORM可以有效率的进行,但是flask相当于一个内核,要实现该功能需要借助SQLAlchemy第三方拓展包
除了借助SQLAlchemy第三方拓展包之外,我们还可以使用OptionMysql方法来实现前后端数据交互`
我们先新建一个叫option_mysql的py文件,创建OptionMysql类
class OptionMysql(object):
def __init__(self, options):
host = options['HOST']
user = options['USERNAME']
password = options['PASSWORD']
database = options['DATABASE']
port = options['PORT']
charset = 'utf8'
# 连接数据库
self.conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
# 创建游标
self.cur = self.conn.cursor()
self.dict_cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
def __del__(self):
self.cur.close()
self.dict_cur.close()
self.conn.close()
def insert_data(self, sql, params=[]):
"""插入行"""
try:
if not params:
self.cur.execute(sql)
else:
self.cur.execute(sql, params)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e
return True
def multi_insert_data(self, sql, params=[]):
"""批量插入行"""
if not params:
return False
try:
self.cur.executemany(sql, params)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise Exception(e)
return True
def get_data_dict(self, sql, params=[]):
"""查询,返回字典类型"""
try:
if params:
self.dict_cur.execute(sql, params)
else:
self.dict_cur.execute(sql)
data = self.dict_cur.fetchall()
return data
except Exception as e:
self.conn.rollback()
raise e
def get_data(self, sql, params=[]):
"""查询"""
self.cur.execute(sql, params)
data = self.cur.fetchall()
return data
def update_data(self, sql, params=[]):
"""更新"""
try:
if not params:
self.cur.execute(sql)
else:
self.cur.execute(sql, params)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e
return True
def delete_data(self, sql, params=[]):
"""删除"""
try:
self.cur.execute(sql, params)
self.conn.commit()
return True
except Exception as e:
self.conn.rollback()
raise e
def findOne(self, table, col=[], params=[]):
"""
验证记录是否存在
Args:
table: string, 数据表
col: list, 查询的列名
params:list,参数列表
"""
sql = "SELECT " + ",".join(col) + " FROM " + table + " WHERE deleted_at = '0000-01-01 00:00:00'"
# WHERE条件
for i in range(len(col)):
col[i] = col[i] + " = %s"
sql += " AND " + ",".join(col)
self.cur.execute(sql, params)
data = self.cur.fetchall()
return data
def update(self, table, where, args: object):
"""
更新数据
Args:
table: string, 表名
where: string, 待更新的row 的where条件
args: object, 更新的数据
"""
column = []
values = []
# 提取更新的列及值
for key in args.keys():
column.append("`" + key + "`" + " = %s")
if isinstance(args[key], dict):
args[key] = str(json.dumps(args[key]))
values.append(args[key])
# update sql
sql = "UPDATE " + table + " SET " + ",".join(column) + " WHERE " + where
try:
self.cur.execute(sql, values)
self.conn.commit()
return True
except Exception as e:
self.conn.rollback()
raise e
该类提供了多个子方法:常用的有:
1,insert_data方法用于插入数据时使用 (注意:返回是True)
2,get_data_dict方法用于查询,返回字典类型,
3,update_data方法用于修改数据 (注意:返回是True)
4,delete_data方法用于删除数据 (注意:返回是True)
同时我们需要在setting.py文件中添加mysql_option:
# mysql config
db_user = "xxx
db_passwd = "123
db_host = "192.168.80.256"
db_name = "user"
db_port = 3306
mysql_option = {
'HOST': db_host,
'USERNAME': db_user,
'PASSWORD': db_passwd,
'DATABASE': db_name,
'PORT': db_port
}
数据库内容根据个人电脑实际情况填写
使用
查询
mysql = OptionMysql(mysql_option)
sql = "SELECT * FROM store_order WHERE store_order_id=%s" % store_order_id
result= mysql.get_data_dict(sql)
修改
mysql = OptionMysql(mysql_option)
sql = " UPDATE truck SET is_delete =1,update_time=now() WHERE truck_id in (%s) " % ",".join("'"+str(x) + "'" for x in truck_list)
mysql.update_data(sql)
插入
mysql = OptionMysql(mysql_option)
insert_sql = "INSERT INTO resource_file SET field='%s', f_value='%s', path='%s'" % (field, value, json.dumps(path_list))
mysql.insert_data(insert_sql)