一 数据插入

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   -> cmd

1.4.2 登录MySQL

mysql -h127.0.0.1 -uroot -proot

1.4.3 选择数据库

use test_data

1.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 = 3306db_user = 'root'db_password = 'root'db_database = 'test_data'

2.2 配置文件二

import MySQLdb
class MySQLClientUtils(object):def __init__(self, config):self.host = config.db_hostself.port = config.db_portself.user = config.db_userself.pwd = config.db_passwordself.db = config.db_databaseself.conn = Noneself.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 resultexcept 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()# 获取cursorself.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 MySQLClientUtilssql2 = '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('谢谢使用 ！')breakelse: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 resultdef 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 result

3.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 result

sql = '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 result

3.5 清空购物车

sql = 'delete from cart'

def clear_cart():"""清空购物车"""sql = 'delete from cart'result = MySQLClientUtils(config=DBConfig).dml_operation(sql)return result

3.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)