def selectALLProByName(pro_name):
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select pro_id,pro_name,pro_unit_price,pro_num from products where pro_name='%s'" % (
pro_name)
row_num = cur.execute(sql)
#print("数据量："+str(row_num))
if row_num > 0:
row = cur.fetchone()
pro = Product(row[0], row[1], row[2], row[3])
return (pro)
def selectALLUserByID(user_id):
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select user_id,user_name,password,is_admin,address from users where user_id=%d" % (
user_id)
row_num = cur.execute(sql)
#print("数据量："+str(row_num))
if row_num > 0:
row = cur.fetchone()
#print(row)
use = Users(row[0], row[1], row[2], row[3], row[4])
return use
def removeUser(user_id):
#print("删除商品!")
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
#创建sql语句
sql = "delete from users where user_id=%d" % (user_id)
#异常检测
try:
#用游标函数执行sql语句
row_num = cur.execute(sql)
#print("SQL删除的影响行数"+str(row_num))
conn.commit() #提交事务，真正写数据到数据库
except:
#traceback.format_exc()格式化异常消息
print(traceback.format_exc())
conn.roolback()
def updateUseradmin(user_id, is_admin):
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
#创建sql语句
sql = "update users set is_admin='%s' where user_id=%d" % (is_admin,
user_id)
#异常检测
try:
#用游标函数执行sql语句
row_num = cur.execute(sql)
print("SQL删除的影响行数" + str(row_num))
conn.commit() #提交事务，真正写数据到数据库
except:
#traceback.format_exc()格式化异常消息
print(traceback.format_exc())
conn.roolback()
def selectALLUserByName(user_name):
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select user_id,user_name,password,is_admin,address from users where user_name='%s'" % (
user_name)
row_num = cur.execute(sql)
isSuccess = False
#print("数据量："+str(row_num))
if row_num > 0:
isSuccess = True
row = cur.fetchone()
use = Users(row[0], row[1], row[2], row[3], row[4])
return use, isSuccess
else:
return None, isSuccess
def selectALLProByID(pro_id):
isFound = False
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select pro_id,pro_name,pro_unit_price,pro_num from products where pro_id=%d" % (
pro_id)
row_num = cur.execute(sql)
#print("数据量："+str(row_num))
if row_num > 0:
row = cur.fetchone()
isFound = True
#print(row)
pro = Product(row[0], row[1], row[2], row[3])
return pro, isFound
else:
return None, isFound
def updateProNum(pro_id, new_pro_num):
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
#创建sql语句
sql = "update products set pro_num='%s' where pro_id=%d" % (new_pro_num,
pro_id)
#异常检测
try:
#用游标函数执行sql语句
row_num = cur.execute(sql)
print("SQL删除的影响行数" + str(row_num))
conn.commit() #提交事务，真正写数据到数据库
except:
#traceback.format_exc()格式化异常消息
print(traceback.format_exc())
conn.roolback()
def selectALLUsers():
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select user_id,user_name,password,is_admin,address from users"
row_num = cur.execute(sql)
#print("数据量："+str(row_num))
rows = cur.fetchall()
#print(type(rows))#此处为method元素
#print(rows)#fetchall() 获取所有记录
for row in rows:
#print(str(row[0])+":"+row[1]+":"+str(row[2])+":"+str(row[3]))
use = Users(row[0], row[1], row[2], row[3], row[4])
#print(pro.pro_id)
users.append(use)
return users
def insertUser(use):
print("插入用户到数据库...")
#print("\t商品单价:"+str(pro.pro_unit_price)+"\n\t商品库存:"+str(pro.pro_num))
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
#创建sql语句
sql="insert into users(user_name,password,is_admin,address)values('%s','%s','%s','%s')"\
%(use.user_name,use.password,use.is_admin,use.address)
#异常检测
try:
#用游标函数执行sql语句
row_num = cur.execute(sql)
print("SQL语句影响行数" + str(row_num))
conn.commit() #提交事务，真正写数据到数据库
except:
#traceback.format_exc()格式化异常消息
print(traceback.format_exc())
def selectALLPros():
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
sql = "select pro_id,pro_name,pro_unit_price,pro_num from products"
row_num = cur.execute(sql)
#print("数据量："+str(row_num))
rows = cur.fetchall()
#print(type(rows))#此处为method元素
#print(rows)#fetchall() 获取所有记录
for row in rows:
#print(str(row[0])+":"+row[1]+":"+str(row[2])+":"+str(row[3]))
pro = Product(row[0], row[1], row[2], row[3])
#print(pro.pro_id)
pros.append(pro)
return pros
def insertPro(pro):
print("插入商品到数据库...")
print("\t商品单价:" + str(pro.pro_unit_price) + "\n\t商品库存:" + str(pro.pro_num))
conn = getMySQLConn()
#print(conn)
#创建游标对象
cur = conn.cursor()
#创建sql语句
sql="insert into products(pro_id,pro_name,pro_unit_price,pro_num)values('%s','%s',%d,%d)"\
%(pro.pro_id,pro.pro_name,pro.pro_unit_price,pro.pro_num)
#异常检测
try:
#用游标函数执行sql语句
row_num = cur.execute(sql)
print("SQL语句影响行数" + str(row_num))
conn.commit() #提交事务，真正写数据到数据库
except:
#traceback.format_exc()格式化异常消息
print(traceback.format_exc())
conn.roolback()