def userInfo_login(name, pwd):
"""
前台用户登录功能
:param name: 用户姓名
:param pwd: 用户登录密码
:return: 查询到的用户信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM userInfo WHERE LOGINNAME = %s AND LOGINPASSWORD = %s'
cur.execute(sql, (name, pwd))
# user = cur.fetchone()
users = []
for u in cur.fetchall():
s2 = re.sub(r'<.*?>', '', u[2])
# 再用str.replace()函数去掉'\n'
s2 = s2.replace('\n', '')
user = {
'id': u[0],
'name': u[1],
'subTitle': s2,
'creationTime': str(u[3]),
'lastModificationTime': str(u[4]),
'loginName': u[5],
'loginPassword': u[6]
}
users.append(user)
return users
finally:
if conn:
conn.close()
def select_all():
"""
查询所有用户信息
:return: 返回所有用户信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM userinfo'
cur.execute(sql)
# 将Tuple的list，转换成User对象的list
users = []
for u in cur.fetchall():
user = {
'id': u[0],
'name': u[1],
'subTitle': u[2],
'creationTime': str(u[3]),
'lastModificationTime': str(u[4]),
'loginName': u[5],
'loginPassword': u[6]
}
users.append(user)
return users
finally:
if conn:
conn.close()
def select_all():
"""
查询全部用户信息
:return: 返回全部信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM user'
cur.execute(sql)
# 将Tuple的list，转换成User对象的list
users = []
for u in cur.fetchall():
user = {
'id': u[0],
'name': u[1],
'province': u[2],
'city': u[3],
'address': u[4],
'zip': u[5],
'date': str(u[6])
}
users.append(user)
return users
finally:
if conn:
conn.close()
def select_all():
"""
查询全部文章信息
:return: 返回全部信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM article'
cur.execute(sql)
# 将Tuple的list，转换成User对象的list
users = []
for u in cur.fetchall():
s2 = re.sub(r'<.*?>', '', u[2])
# 再用str.replace()函数去掉'\n'
s2 = s2.replace('\n', '')
user = {
'id': u[0],
'title': u[1],
'content': s2,
'readingVolume': int(u[3]),
'releaseTime': str(u[4]),
'modificationTime': str(u[5]),
}
users.append(user)
return users
finally:
if conn:
conn.close()
def select_condition_data(stu):
"""
动态查询用户信息
:param stu: 字典对象
加入可能又以下key
id,
name,
province,
city,
zip
startDay,
endDay,
:return: 返回查询到的信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM user WHERE 1 = 1'
params = [] # 用来存放具体的条件参数
if 'id' in stu:
sql += ' AND ID = %s'
params.append(stu['id'])
if 'name' in stu:
if stu['name']:
sql += ' AND NAME LIKE concat("%", %s, "%")'
params.append(stu['name'])
if 'province' in stu:
sql += ' AND PROVINCE = %s'
params.append(stu['province'])
if 'city' in stu:
sql += ' AND CITY = %s'
params.append(stu['city'])
if 'zip' in stu:
sql += ' AND ZIP = %s'
params.append(stu['zip'])
if 'startDay' in stu:
sql += ' AND DATE >= %s'
params.append(stu['startDay'])
if 'endDay' in stu:
sql += ' AND DATE <= %s'
params.append(stu['endDay'])
print(sql)
cur.execute(sql, tuple(params))
# 将tuple的list 转成Student对象的list
users = []
for u in cur.fetchall():
user = modal.User(u[0], u[1], u[2], u[3], u[4], u[5], u[6])
users.append(user)
return users
finally:
if conn:
conn.close()
def update_readingVolume(user):
"""
修改文章信息
:param user: 阅读量和id
:return: 返回已修改条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'UPDATE article SET READINGVOLUME=%s WHERE ID=%s'
cur.execute(sql, user)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def update(user):
"""
修改文章信息
:param user: 文章对象Tuple(全部列，除了阅读数和文章发布时间)
:return: 返回已修改条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'UPDATE article SET TITLE=%s, CONTENT=%s, MODIFICATIONTIME=%s WHERE ID=%s'
cur.execute(sql, user)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def delete(id):
"""
删除一条文章信息
:param id: 文章 id
:return: 返回已删除条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'DELETE FROM article WHERE ID=%s'
cur.execute(sql, (id, ))
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def insert(stu):
"""
新增用户
:param stu: 用户对象Tuple(全部列)
:return: 返回新增条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'INSERT INTO login (USERNAME, USERPWD, USERTEL, USERSEX) values (%s, %s, %s, %s)'
cur.execute(sql, stu)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def update(list):
"""
修改用户信息
:param user: 用户对象Tuple(全部列，除了创建时间)
:return: 返回已修改条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'UPDATE userinfo SET NAME=%s, SUBTITLE=%s, LASTMODIFICATIONTIME=%s, LOGINNAME=%s, LOGINPASSWORD=%s WHERE ID=%s'
cur.execute(sql, list)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def update(user):
"""
修改后台用户信息
:param user: 用户对象Tuple(全部列)
:return: 返回已修改条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'UPDATE backstage_login SET USERNAME=%s, USERPWD=%s WHERE ID=%s'
cur.execute(sql, user)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def insert(con):
"""
插入一条新闻
:param con: 数据对象 Tuple
:return: 返回行数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'INSERT INTO thematicdaily (MAINTITLE, SUBTITLE, CONTENT, RELEASETIME, MODIFICATIONTIME) VALUES (%s, %s, %s, %s, %s)'
cur.execute(sql, con)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def insert(data):
"""
新增一条饼图数据
:param stu: 数据对象 Tuple(全部列)
:return: 返回新增条数
"""
con = DB_util.getConn()
try:
cur = con.cursor()
sql = 'INSERT INTO doughnutChart (VALUE, NAME) VALUES (%s, %s)'
cur.execute(sql, data)
con.commit()
return cur.rowcount
finally:
if con:
con.close()
def select_data(stu):
"""
查询用户信息
:param stu: 用户对象Tuple(用户名列和用户信息列)
:return: 返回查询到的信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM login WHERE USERNAME=%s AND USERPWD=%s'
print(sql)
cur.execute(sql, stu)
return cur.fetchall()
finally:
if conn:
conn.close()
def inster(stu):
"""
增加一条用户信息
:param stu: 用户对象Tuple(全部列)
:return: 返回新增条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'INSERT INTO user (NAME, PROVINCE, CITY, ADDRESS, ZIP, DATE) VALUES (%s, %s, %s, %s, %s, %s)'
cur.execute(sql, stu)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def insert(files):
"""
上传文件
:param files: 数据对象 Tuple(全部列)
:return: 返回新增条数
"""
con = DB_util.getConn()
try:
cur = con.cursor()
sql = 'INSERT INTO uploadfiles (FILEPATH, UPLOADTIME) VALUES (%s, %s)'
cur.execute(sql, files)
con.commit()
return cur.rowcount
finally:
if con:
con.close()
def delete(id):
"""
删除一条后台用户信息
:param id: 用户id
:return: 返回已删除条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'DELETE FROM backstage_login WHERE ID=%s'
cur.execute(sql, (id, ))
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def update(user):
"""
修改用户信息
:param user: 用户对象Tuple(全部列)
:return: 返回已修改条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'UPDATE user SET NAME=%s, PROVINCE=%s, CITY=%s, ADDRESS=%s, ZIP=%s, DATE=%s WHERE ID=%s'
cur.execute(sql, user)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def insert(user):
"""
添加一条用户信息
:param stu: 用户对象Tuple(全部列，除了上次修改时间列)
:return: 返回新增条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'INSERT INTO userinfo (NAME, SUBTITLE, CREATIONTIME, LOGINNAME, LOGINPASSWORD) VALUES' \
'(%s, %s, %s, %s, %s)'
cur.execute(sql, user)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def inster(stu):
"""
增加一条文章 信息
:param stu: 文章对象Tuple(全部列，除了阅读数和修改时间)
:return: 返回新增条数
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'INSERT INTO article (TITLE, CONTENT, RELEASETIME)' \
'VALUES (%s, %s, %s)'
cur.execute(sql, stu)
conn.commit()
return cur.rowcount
finally:
if conn:
conn.close()
def login(name, pwd):
"""
用户登录功能
:param name: 用户姓名
:param pwd: 用户登录密码
:return: 查询到的用户信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM login WHERE USERNAME=%s AND USERPWD=%s'
cur.execute(sql, (name, pwd))
user = cur.fetchone()
return user
finally:
if conn:
conn.close()
def select_all():
"""
查询全部数据信息
:return: 全部信息
"""
con = DB_util.getConn()
try:
cur = con.cursor()
sql = 'SELECT * FROM doughnutChart'
cur.execute(sql)
datas = []
for d in cur.fetchall():
d = {'id': d[0], 'value': d[1], 'name': d[2]}
datas.append(d)
return datas
finally:
if con:
con.close()
def select_all():
"""
查询全部用户信息
:return: 返回全部信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM user'
cur.execute(sql)
# 将Tuple的list，转换成User对象的list
users = []
for u in cur.fetchall():
user = modal.User(u[0], u[1], u[2], u[3], u[4], u[5], u[6])
users.append(user)
return users
finally:
if conn:
conn.close()
def select_all():
"""
查询全部后台用户信息
:return: 返回全部信息
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sql = 'SELECT * FROM backstage_login'
cur.execute(sql)
# 将Tuple的list，转换成User对象的list
users = []
for u in cur.fetchall():
user = {
'id': u[0],
'name': u[1],
'pwd': u[2],
}
users.append(user)
return users
finally:
if conn:
conn.close()
def select_all_page(list, pageNum):
"""
分页查询
:param list: 字典对象
加入可能又以下key
id,
title,
readingVolume # 阅读数量
startDete,
endDate,
isConvertFormat # 是否转换成字符串(默认内容带有标签) true/false
:param pageNum: 页数
:return: 查询到的数据对象
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sqlData = 'SELECT * FROM article'
sqlCount = 'SELECT count(*) FROM article'
sql = ' WHERE 1 = 1'
params = [] # 用来存放具体的条件参数
if 'id' in list:
if list['id']:
sql += ' AND ID = %s'
params.append(list['id'])
if 'title' in list:
if list['title']:
sql += ' AND TITLE = %s'
params.append(list['title'])
if 'readingVolume' in list:
if list['readingVolume']:
sql += ' AND READINGVOLUME = %s'
params.append(list['readingVolume'])
if 'startDay' in list:
if list['startDay']:
sql += ' AND RELEASETIME >= %s'
params.append(list['startDay'])
if 'endDay' in list:
if list['endDay']:
sql += ' AND RELEASETIME <= %s'
params.append(list['endDay'])
page = Page.Page()
# 查询总条数
sqlCount += sql
cur.execute(sqlCount, tuple(params))
countTotal = cur.fetchone()[0]
page.countTotal = countTotal
# 计算总页数
if countTotal % page.pageSize == 0:
pageTotal = countTotal // page.pageSize
else:
pageTotal = countTotal // page.pageSize + 1
page.pageTotal = pageTotal
page.pageNum = pageNum
# 查询记录
sqlData += sql
sqlData += ' LIMIT %s, %s'
params.append((pageNum - 1) * page.pageSize)
params.append(page.pageSize)
cur.execute(sqlData, tuple(params))
# 判断是否转换为字符串
if 'isConvertFormat' in list:
if list['isConvertFormat']:
isTrue = True
else:
isTrue = False
else:
return 'isConvertFormat'
lists = []
for u in cur.fetchall():
if isTrue == True:
s2 = re.sub(r'<.*?>', '', u[2])
s2 = s2.replace('\n', '')
else:
s2 = u[2]
list = {
'id': u[0],
'title': u[1],
'content': s2,
'readingVolume': int(u[3]),
'releaseTime': str(u[4]),
'modificationTime': str(u[5]),
}
lists.append(list)
page.data = lists
return page
finally:
if (conn):
conn.close()
from com.aowin.connect_library import DB_util
# 打开客户端
conn = DB_util.getConn()
# 打开一个游标
cursor = conn.cursor()
# 新增用户登录表
# sql = """
# CREATE TABLE login(
# ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
# USERNAME VARCHAR(32) NOT NULL,
# USERPWD VARCHAR(32) NOT NULL,
# USERTEL VARCHAR(11) NOT NULL,
# USERSEX VARCHAR(1) NOT NULL
# )
# """
# 新增用户表
sql = """
CREATE TABLE user(
ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
PROVINCE VARCHAR(32),
CITY VARCHAR(32),
ADDRESS VARCHAR(128),
ZIP INT(6),
DATE DATE
)
"""
def select_data(stu, pageNum):
"""
分页查询
:param stu: 字典对象
加入可能又以下key
id,
name,
province,
city,
zip
startDay,
endDay,
:return: page 对象
"""
conn = DB_util.getConn()
try:
cur = conn.cursor()
sqlData = 'SELECT * FROM user'
sqlCount = 'SELECT count(*) FROM user'
sql = ' WHERE 1 = 1'
params = [] # 用来存放具体的条件参数
if 'id' in stu:
if stu['id']:
sql += ' AND ID = %s'
params.append(stu['id'])
if 'name' in stu:
if stu['name']:
sql += ' AND NAME LIKE concat("%", %s, "%")'
params.append(stu['name'])
if 'province' in stu:
if stu['province']:
sql += ' AND PROVINCE = %s'
params.append(stu['province'])
if 'city' in stu:
if stu['city']:
sql += ' AND CITY = %s'
params.append(stu['city'])
if 'zip' in stu:
if stu['zip']:
sql += ' AND ZIP = %s'
params.append(stu['zip'])
if 'startDay' in stu:
if stu['startDay']:
sql += ' AND DATE >= %s'
params.append(stu['startDay'])
if 'endDay' in stu:
if stu['endDay']:
sql += ' AND DATE <= %s'
params.append(stu['endDay'])
print(sql)
page = Page.Page()
# 查询总条数
sqlCount += sql
cur.execute(sqlCount, tuple(params))
countTotal = cur.fetchone()[0]
page.countTotal = countTotal
# 计算总页数
if countTotal % page.pageSize == 0:
pageTotal = countTotal // page.pageSize
else:
pageTotal = countTotal // page.pageSize + 1
page.pageTotal = pageTotal
page.pageNum = pageNum
# 查询记录
sqlData += sql
sqlData += ' LIMIT %s, %s'
params.append((pageNum - 1) * page.pageSize)
params.append(page.pageSize)
cur.execute(sqlData, tuple(params))
users = []
for u in cur.fetchall():
user = {
'id': u[0],
'name': u[1],
'province': u[2],
'city': u[3],
'address': u[4],
'zip': u[5],
'date': str(u[6])
}
users.append(user)
page.data = users
return page
finally:
if conn:
conn.close()