（五）实训练习案例
五、sqlite3 实训练习案例
1、创建数据库主数据表
import sqlite3
# 连接到SQLite数据库
# 数据库文件是mrsoft.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
cursor.execute('create table user (
id int(10) primary key, name varchar(20))')
# 关闭游标
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
2、插入数据到数据库表
# 连接到SQLite数据库
# 数据库文件是mrsoft.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor = conn.cursor()
# 继续执行一条SQL语句，插入一条记录:
cursor.execute('insert into user (id, name) values ("1", "MRSOFT")')
cursor.execute('insert into user (id, name) values ("2", "Andy")')
cursor.execute('insert into user (id, name) values ("3", "明日科技小助手")')
cursor.execute('insert into user (id, name) values ("4", "林小明")')
cursor.execute('insert into user (id, name) values ("5", "谢军红")')
cursor.execute('insert into user (id, name) values ("6", "李明助")')
# 关闭游标
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
3、查询数据库数据
import sqlite3
# 连接到SQLite数据库,数据库文件是mrsoft.db
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor = conn.cursor()
# 执行查询语句:
cursor.execute('select * from user')
# 获取查询结果:
result1 = cursor.fetchall()
print(result1)
# 关闭游标
cursor.close()
# 关闭Connection:
conn.close()
4、修改数据库数据
import sqlite3
# 连接到SQLite数据库,数据库文件是mrsoft.db
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor = conn.cursor()
cursor.execute('update user set name = ? where id = ?',('谢红明',1))
cursor.execute('update user set name = ? where id = ?',('李小明',3))
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭Connection:
conn.close()
5、删除数据库中的数据
import sqlite3
# 连接到SQLite数据库,数据库文件是mrsoft.db
conn = sqlite3.connect('mrsoft.db')
# 创建一个Cursor:
cursor = conn.cursor()
cursor.execute('delete from user where id = ?',(1,))
cursor.execute('select * from user')
result = cursor.fetchall()
print(result)
# 关闭游标
cursor.close()
# 提交事务
conn.commit()
# 关闭Connection:
conn.close()
综合案例：商品信息管理(sqlite)
'''
要导入的文件应是utf-8编码，不能是utf-8-BOM的
'''
import os
import sqlite3
class GoodsDB:
def __init__(self):
self.con = ''
self.cur = ''
def connect(self,db):
self.con = sqlite3.connect(db)
self.cur = self.con.cursor()
try:
sql = '''
create table goods (
no text,
name text,
first integer,
second integer,
third integer )
'''
self.cur.execute(sql)
self.con.commit()
except:
pass
def close(self):
self.cur.close()
self.con.close()
def show(self):
format_head = '{:8}\t{:8}\t{:8}\t{:8}\t{:8}'
print(format_head.format('编号','名称','一季度','二季度','三季度'))
sql = 'select * from goods'
self.cur.execute(sql)
rows = self.cur.fetchall()
format_con = '{:8}\t{:8}\t{:<8}\t{:<8}\t{:<8}'
for row in rows:
print(format_con.format(row[0],row[1],row[2],row[3],row[4]))
def __enterSale(self,message):
while True:
try:
sale = input(message)
if 0 <= int(sale) <= 10000:
break
else:
print('输入错误，应在0到10000之间')
except:
print('输入错误，应在0到10000之间')
return int(sale)
def __exists(self,no):
sql = 'select * from goods where no = ?'
result = self.cur.execute(sql,(no,))
rows = result.fetchall()
if len(rows) > 0:
return True
else:
return False
def __insert(self,no,name,first,second,third):
if self.__exists(no):
print('该编号已经存在')
else:
sql='insert into goods(no,name,first,second,third) values(?,?,?,?,?)'
self.cur.execute(sql,(no,name,first,second,third))
self.con.commit()
if self.cur.rowcount > 0:
print('插入成功')
else:
print('插入失败')
def __update(self,no,name,first,second,third):
if not self.__exists(no):
print('该编号不存在')
else:
sql='update goods set name = ?,first = ?,second = ? ,third = ? where no = ?'
self.cur.execute(sql,(name,first,second,third,no))
self.con.commit()
if self.cur.rowcount > 0:
print('修改成功')
else:
print('修改失败')
def __delete(self,no):
if not self.__exists(no):
print('该编号不存在')
else:
sql = 'delete from goods where no = ?'
self.cur.execute(sql,(no,))
self.con.commit()
if self.cur.rowcount > 0:
print('删除成功')
else:
print('删除失败')
def insert(self):
while True:
no = input('编号：')
if self.__exists(no):
print('该编号已经存在')
else:
name = input('名称：')
first = self.__enterSale('一季度:')
second = self.__enterSale('二季度:')
third = self.__enterSale('三季度:')
if no != '' and name != '':
self.__insert(no,name,first,second,third)
else:
print('请将信息输入完整')
choice = input('继续添加(y/n)?').lower()
if choice == 'n':
break
def delete(self):
while True:
no = input('请输入要删除的编号：')
if no != '':
self.__delete(no)
choice = input('继续删除(y/n)?').lower()
if choice == 'n':
break
def update(self):
while True:
no = input('请输入要修改的编号:')
if not self.__exists(no):
print('该编不存在')
else:
name = input('名称：')
first = self.__enterSale('一季度:')
second = self.__enterSale('二季度:')
third = self.__enterSale('三季度:')
if no != '' and name != '' :
self.__update(no,name,first,second,third)
else:
print('请将信息输入完整')
choice = input('继续修改(y/n)?').lower()
if choice == 'n':
break
def save(self):
fn = input('请输入要导出的文件名:')
with open(fn,'w',encoding = 'utf-8') as fp:
self.cur.execute('select * from goods')
rows = self.cur.fetchall()
for row in rows:
fp.write(row[0] + ',' )
fp.write(row[1] + ',' )
fp.write(str(row[2]) + ',' )
fp.write(str(row[3]) + ',' )
fp.write(str(row[4])+ '\n')
print('导出完毕')
def load(self):
fn = input('请输入要导入的文件名:')
if os.path.exists(fn):
try:
with open(fn,'r',encoding = 'utf-8') as fp:
while True:
line = fp.readline().strip('\n')
if line == '':
break
gds = line.split(',')
self.__insert(gds[0],gds[1],int(gds[2]),int(gds[3]),int(gds[4]))
print('导入完毕')
except:
print('error...')
else:
print('要导入的文件不存在')
def sale_avg(self):
sql = 'select avg(first),avg(second),avg(third) from goods'
self.cur.execute(sql)
result = self.cur.fetchone()
first_avg,second_avg,third_avg = result[0],result[1],result[2]
if first_avg == None:
print('尚没有商品销量...')
else:
print('一季度平均销量是：%.2f'%first_avg)
print('二季度平均销量是：%.2f'%second_avg)
print('三季度平均销量是：%.2f'%third_avg)
def sale_max(self):
sql = 'select max(first),max(second),max(third) from goods'
self.cur.execute(sql)
result = self.cur.fetchone()
first_max,second_max,third_max = result[0],result[1],result[2]
if first_max == None:
print('尚没有商品销量...')
else:
print('一季度最高销量是：%d'%first_max)
print('二季度最高销量是：%d'%second_max)
print('三季度最高销量是：%d'%third_max)
def sale_min(self):
sql = 'select min(first),min(second),min(third) from goods'
self.cur.execute(sql)
result = self.cur.fetchone()
first_min,second_min,third_min = result[0],result[1],result[2]
if first_min == None:
print('尚没有商品销量...')
else:
print('一季度最低销量是：%d'%first_min)
print('二季度最低销量是：%d'%second_min)
print('三季度最低销量是：%d'%third_min)
def main(self,db):
while True:
print('商品信息管理系统(数据库版)'.center(20,'='))
print('info --------商品基本信息管理')
print('sale --------商品销量统计')
print('exit --------退出系统')
print(''.center(32,'='))
s = input('main>').strip().lower()
if s == 'info':
self.infoprocess(db)
elif s == 'sale':
self.saleprocess(db)
elif s == 'exit':
break
else:
print('输入错误')
def infoprocess(self,db):
self.connect(db)
print('商品基本信息管理'.center(24,'='))
print('load -----------导入商品数据')
print('insert -----------插入商品信息')
print('delete -----------删除商品信息')
print('update -----------修改商品信息')
print('show -----------显示商品信息')
print('save -----------导出商品数据')
print('return -----------返回')
print(''.center(32,'='))
while True:
s = input('info>').strip().lower()
if s == 'load':
self.load()
elif s == 'insert':
self.insert()
elif s == 'delete':
self.delete()
elif s == 'update':
self.update()
elif s == 'show':
self.show()
elif s == 'save':
self.save()
elif s =='return':
break
else:
print('输入错误')
self.close()
def saleprocess(self,db):
self.connect(db)
print('商品销量统计'.center(24,'='))
print('avg --------平均销量')
print('max --------最高销量')
print('min --------最低销量')
print('return --------返回')
print(''.center(30,'='))
while True:
s = input('sale>').strip().lower()
if s == 'avg':
self.sale_avg()
elif s == 'max':
self.sale_max()
elif s == 'min':
self.sale_min()
elif s == 'return':
break
else:
print('输入错误')
self.close()
if __name__ == '__main__':
gds = GoodsDB()
gds.main('d:/db/GoodsDB.db')
