def execute(self, sql, params):
if type(params) == list and len(params) == 0:
return
# 使用cursor()方法获取操作游标
connection = self.get_connection()
# cursor = connection.cursor()
try:
# 执行sql语句
# cursor.execute(sql)
# cursor.executemany("insert into tb7(user,pass,licnese)values(%s,%s,%s)",
# [("u1","u1pass","11111"),("u2","u2pass","22222")])
with connection.cursor() as cursor:
# 执行sql语句
if type(params) == list:
cursor.executemany(sql, params)
else:
cursor.execute(sql, params)
# 获取自增id
pk = cursor.lastrowid
connection.commit()
if pk:
cy_logger.log("executes successfully! last_row_id==> " +
str(pk))
else:
cy_logger.log("executes successfully")
return pk
except Exception as e:
cy_logger.error(str(e))
# 发生错误时回滚
connection.rollback()
finally:
# 关闭数据库连接
connection.close()
def upload(url, filename, gen_folde):
"""
上传网络文件
:param url: 网络文件地址
:param filename: 文件名
:param gen_folde: 目录类别 image、media、authent、voice、file
:return: 上传七牛返回的地址、文件大小
"""
try:
if url is None or filename is None:
return
if "image".__eq__(gen_folde):
gen_folde = "image"
elif "media".__eq__(gen_folde):
gen_folde = "yvd"
elif "authent".__eq__(gen_folde):
gen_folde = "important"
elif "voice".__eq__(gen_folde):
gen_folde = "voice"
else:
gen_folde = "file"
# 生成上传 Token
token = q.upload_token(bucket)
data = http.request("GET", url).data
ret, info = put_data(
token,
gen_folde + "/" + time.strftime("%Y%m%d", time.localtime()) + "/" + filename,
data,
mime_type=None
)
return qiniu_url + ret['key'], round(len(data)/1024, 2)
except Exception as e:
logger.error("exception===> " + str(e))
def delete(url):
"""
删除指定文件
:param url: 牛文件地址
:return:
"""
try:
if url is not None:
url = url.replace(qiniu_url, "")
ret, info = bm.delete(bucket, url)
print(info)
print(ret)
except Exception as e:
logger.error("exception===> " + str(e))
def fetchone(self, sql):
# 使用cursor()方法获取操作游标
connection = self.get_connection()
cursor = connection.cursor()
try:
# 执行SQL语句
cursor.execute(sql)
# 获取单条数据
return cursor.fetchone()
except Exception as e:
cy_logger.error(e)
finally:
# 关闭数据库连接
connection.close()
def select_rows_paper(self, sql, param=None, page_no=1, page_size=20):
"""
分页查询
"""
connection = self.get_connection()
try:
with connection.cursor() as cursor:
if param:
sql = sql + '%s' % param
cursor.execute("SELECT COUNT(1) FROM (%s) tmp" % sql)
total = cursor.fetchall()[0][0]
pages = 0
rows = []
if total > 0:
# 总页数
pages = int(total /
page_size) if total % page_size == 0 else int(
total / page_size) + 1
if page_no > pages:
page_no = pages
offset = (page_no - 1) * page_size # 偏移量
sql = sql + ' LIMIT %s, %s' % (offset, page_size)
cy_logger.log(sql)
cursor.execute(sql)
rows = cursor.fetchall()
return {
'total': total,
'page_no': page_no,
'pages': pages,
'rows': rows
}
except Exception as e:
cy_logger.error(e)
finally:
connection.close()
