一 数据插入
1.1 创建数据列
1.1.1 选择数据库
USE test_data;1.1.2 插入数据列
ALTER TABLE test_data_info
ADD COLUMN stuno INT,
ADD COLUMN stuname VARCHAR(255),
ADD COLUMN gender VARCHAR(10);
1.2 插入具体数据
import MySQLdb
# 创建数据库连接 # 主机IP：192.168.146.1，本机可以使用local_host
conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', password='root', db='test_data')
# print('conn:', conn)
# 获取游标对象
cursor = conn.cursor() # cursor 游标
sql = 'insert into test_data_info(stuno, stuname, gender) values(%s,%s,%s)'
try:
result = cursor.execute(sql, args=(1042, 'rose', 'girl')) # 增，删，改，需要提交 # execute，执行
print('result:', result)
conn.commit() # 提交 # commit
except Exception as ex:
print(ex)
conn.rollback() # 记得回滚1.3 查询数据
1.3.1 查询多条数据
sql = 'select * from test_data_info'
cursor.execute(sql)
result = cursor.fetchall()
print('result:', result)1.3.2 查询单条数据
sql = 'select count(*) from test_data_info'
cursor.execute(sql)
result = cursor.fetchone()
print('result:', result)1.4 命令行操作
1.4.1 命令行操作
Windows + r -> cmd1.4.2 登录MySQL
mysql -h127.0.0.1 -uroot -proot1.4.3 选择数据库
use test_data1.4.4 查看存在的表格
show tables;1.4.5 查看具体表格
select * from test_data_info;1.4.6 插入数据
insert into test_data_info(stuno, stuname, gender) values (1043, 'mark', 'boy');二 数据库内容修改
2.1 配置文件一
class DBConfig:
db_host = '127.0.0.1'
db_port = 3306
db_user = 'root'
db_password = 'root'
db_database = 'test_data'2.2 配置文件二
import MySQLdb
class MySQLClientUtils(object):
def __init__(self, config):
self.host = config.db_host
self.port = config.db_port
self.user = config.db_user
self.pwd = config.db_password
self.db = config.db_database
self.conn = None
self.cursor = None
# 创建链接
def create_connection(self):
self.conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, password=self.pwd, db=self.db)
# 获取游标对象
def create_cursor(self):
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
# 关闭
def close(self):
if self.cursor:
self.cursor.close()
if self.conn:
self.conn.close()
# 关于数据库的操作, 增删改查, insert, delete, update,
def dml_operation(self, sql, args=None):
try:
# 获取连接对象
self.create_connection()
# 获取游标
self.create_cursor()
# 执行sql语句
result = self.cursor.execute(sql, args=args) # 执行sql语句并传参
# 提交
self.conn.commit()
return result
except Exception as ex:
print('ex: ', ex)
self.conn.rollback()
finally:
self.close() # 正常结束，调用关闭操作
# 查询操作
def search_operation(self, sql, args=None, is_one=True):
try:
# 创建链接
self.create_connection()
# 获取cursor
self.create_cursor()
# 执行sql语句
self.cursor.execute(sql, args=args)
if is_one:
result = self.cursor.fetchone() # 查询一个结果
else:
result = self.cursor.fetchall() #
return result # 返回查询结果
except Exception as ex:
print('ex: ', ex)
finally:
self.close() # 关闭2.3 插入数据
2.3.1 插入数据
sql1 = 'insert into test_data_info(stuno, stuname, gender) values(%s,%s,%s)'
# 创建对象
client_utils = MySQLClientUtils(DBConfig)
result = client_utils.dml_operation(sql1, args=(1048, '张三', 'boy'))
print(result)2.3.2 修改数据
from settings import DBConfig
from mysql_utils import MySQLClientUtils
sql2 = 'update test_data_info set stuname=%s where stuno=%s'
# 创建对象
client_utils = MySQLClientUtils(DBConfig)
result = client_utils.dml_operation(sql2, args=('张小亮', 1048))
print(result)2.3.3 修改数据
sql3 = 'delete from test_data_info where stuno=%s'
# 创建对象
client_utils = MySQLClientUtils(DBConfig)
result = client_utils.dml_operation(sql3, args=(1048, ))
print(result)2.3.4 查询数据
sql4 = 'select count(*) from test_data_info'
# 创建对象
client_utils = MySQLClientUtils(DBConfig)
result = client_utils.search_operation(sql4)
print(result)2.3.5 模糊查询
sql5 = 'select * from test_data_info where stuname like %s'
# 创建对象
client_utils = MySQLClientUtils(DBConfig)
result = client_utils.search_operation(sql5, args=('%ma%', ), is_one=False)
print(result)三 实际项目数据库操作
3.1 进入主界面
from settings import DBConfig
from mysql_utils import MySQLClientUtils
import prettytable as pt
# 面向过程
def menu():
while True:
print('-' * 50, '欢迎来到购物商城', '-' * 50)
print('1.查询全部商品 2.根据商品名称查询 3.根据编号查询 4.新增商品进购物车 5.清空购物车 6.删除购物车中指定商品 '
'7.查询购物车 0.退出系统')
print('-'*120)
choice = eval(input('请选择：'))
if choice == 1:
# 调用查询方法
result = search_all()
show(result)
elif choice == 2:
goods_name = input('请输入要查询的商品名称【支持模糊查询】：')
# 调用本模块中根据商品名称查询的方法
result = search_by_name(goods_name)
show(result)
elif choice == 3:
goods_id = eval(input('请输入要查询的商品编号：'))
# 调用本模块中根据商品名称查询的方法
result = search_by_id(goods_id)
show(result)
elif choice == 4:
goods_id = eval(input('请输入想要购买的商品编号: '))
result = search_by_id(goods_id)[0]
print('result:', result)
# result: ({'id': '51.0', 'name': '项目驱动零起点学Java 百万程序员Java学习经验总结', 'price': '49.0'},)
goods_name = result.get('name')
price = result.get('price')
num = eval(input('请输入要购买的数量：'))
# 调用本模块的添加进购物车的方法
insert_result = add_cart(goods_id, goods_name, price, num)
if insert_result > 0:
print('商品已经添加到购物车！')
else:
print('添加失败')
elif choice == 5:
# 清空购物车
result = clear_cart()
if result > 0:
print('成功清空购物车')
else:
print('购物车中无商品！')
elif choice == 6:
"""6.删除购物车中指定商品"""
goods_id = eval(input('请输入要删除的商品编号： '))
result = delete_goods_in_cart(goods_id)
print('result:', result)
if result > 0:
print('删除购物车商品成功')
else:
print('购物车中无商品')
elif choice == 7:
"""7.查询购物车"""
result = search_cart()
show_cart(result)
elif choice == 0:
print('谢谢使用 ！')
break
else:
print('输入错误，重新选择')
if __name__ == '__main__':
menu()3.2 查询全部商品
sql = 'select * from yanxuan'
def search_all():
"""查询全部商品"""
sql = 'select * from yanxuan'
# 调用数据库查询
result = MySQLClientUtils(config=DBConfig).search_operation(sql, is_one=False)
return result
def show(result):
"""数据显示"""
tb = pt.PrettyTable()
tb.field_names = ['编号', '商品名称', '售价']
for item in result:
id = item.get('id')
name = item.get('name')
price = item.get('price')
lst = [id, name, price]
tb.add_row(lst) # 添加到表格中
print('tb:', tb)
3.3 根据商品名称查询
sql = 'select * from yanxuan where name like %s'
def search_by_name(goods_name):
sql = 'select * from yanxuan where name like %s'
# 调用数据库查询
result = MySQLClientUtils(config=DBConfig).search_operation(sql, args=(f'%{goods_name}%',), is_one=False)
return result3.4 根据编号查询
sql = 'select * from yanxuan where id = %s'
def search_by_id(goods_id):
sql = 'select * from yanxuan where id = %s'
# 调用数据库查询
result = MySQLClientUtils(config=DBConfig).search_operation(sql, args=(goods_id,), is_one=False)
return resultsql = 'insert into cart (goods_id, name, price, num) value (%s, %s, %s, %s)'
def add_cart(id, goods_name, price, num):
sql = 'insert into cart (goods_id, name, price, num) value (%s, %s, %s, %s)'
result = MySQLClientUtils(config=DBConfig).dml_operation(sql, args=(id, goods_name, price, num))
return result3.5 清空购物车
sql = 'delete from cart'
def clear_cart():
"""清空购物车"""
sql = 'delete from cart'
result = MySQLClientUtils(config=DBConfig).dml_operation(sql)
return result3.6 删除购物车指定商品
sql = 'delete from cart where goods_id=%s'
def delete_goods_in_cart(goods_id):
"""删除购物车指定商品"""
sql = 'delete from cart where goods_id=%s'
result = MySQLClientUtils(config=DBConfig).dml_operation(sql, args=(goods_id, ))
return result
3.7 查询购物车所有商品
sql = 'select * from cart'
def search_cart():
"""查询购物车所有商品"""
sql = 'select * from cart'
result = MySQLClientUtils(config=DBConfig).search_operation(sql, is_one=False)
return result显示购物车商品：
def show_cart(result):
tb = pt.PrettyTable()
tb.field_names = ['序号', '商品编号', '商品名称', '售价', '数量']
for item in result:
id = item.get('id')
goods_id = item.get('goods_id')
name = item.get('name')
price = item.get('price')
num = item.get('num')
lst = [id, goods_id, name, price, num]
tb.add_row(lst)
print(tb)