• Python 笔记07（Mysql数据库基本操作）

    一 数据插入

    1.1 创建数据列

    1.1.1 选择数据库

    USE test_data;

    1.1.2 插入数据列

    1. ALTER TABLE test_data_info
    2. ADD COLUMN stuno INT,
    3. ADD COLUMN stuname VARCHAR(255),
    4. ADD COLUMN gender VARCHAR(10);

    1.2 插入具体数据

    1. import MySQLdb
    2.  
    3. # 创建数据库连接  # 主机IP：192.168.146.1，本机可以使用local_host
    4. conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root', password='root', db='test_data')
    5. # print('conn:', conn)
    6.  
    7. # 获取游标对象
    8. cursor = conn.cursor()   # cursor 游标
    9.  
    10. sql = 'insert into test_data_info(stuno, stuname, gender) values(%s,%s,%s)'
    11.  
    12. try:
    13.     result = cursor.execute(sql, args=(1042, 'rose', 'girl'))   # 增，删，改，需要提交  # execute，执行
    14.     print('result:', result)
    15.     conn.commit()    # 提交  # commit
    16. except Exception as ex:
    17.     print(ex)
    18.     conn.rollback()  # 记得回滚

    1.3 查询数据

    1.3.1 查询多条数据

    1. sql = 'select * from test_data_info'
    2.  
    3. cursor.execute(sql)
    4. result = cursor.fetchall()
    5. print('result:', result)

    1.3.2 查询单条数据

    1. sql = 'select count(*) from test_data_info'
    2.  
    3. cursor.execute(sql)
    4. result = cursor.fetchone()
    5. 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 配置文件一

    1. class DBConfig:
    2.     db_host = '127.0.0.1'
    3.     db_port = 3306
    4.     db_user = 'root'
    5.     db_password = 'root'
    6.     db_database = 'test_data'

    2.2 配置文件二

    1. import MySQLdb
    2. class MySQLClientUtils(object):
    3.     def __init__(self, config):
    4.         self.host = config.db_host
    5.         self.port = config.db_port
    6.         self.user = config.db_user
    7.         self.pwd = config.db_password
    8.         self.db = config.db_database
    9.         self.conn = None
    10.         self.cursor = None
    11.  
    12.     # 创建链接
    13.     def create_connection(self):
    14.         self.conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, password=self.pwd, db=self.db)
    15.  
    16.     # 获取游标对象
    17.     def create_cursor(self):
    18.         self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
    19.  
    20.     # 关闭
    21.     def close(self):
    22.         if self.cursor:
    23.             self.cursor.close()
    24.         if self.conn:
    25.             self.conn.close()
    26.  
    27.     # 关于数据库的操作, 增删改查, insert, delete, update,
    28.     def dml_operation(self, sql, args=None):
    29.         try:
    30.             # 获取连接对象
    31.             self.create_connection()
    32.             # 获取游标
    33.             self.create_cursor()
    34.             # 执行sql语句
    35.             result = self.cursor.execute(sql, args=args)   # 执行sql语句并传参
    36.             # 提交
    37.             self.conn.commit()
    38.             return result
    39.  
    40.         except Exception as ex:
    41.             print('ex: ', ex)
    42.             self.conn.rollback()
    43.         finally:
    44.             self.close()   # 正常结束，调用关闭操作
    45.  
    46.     # 查询操作
    47.     def search_operation(self, sql, args=None, is_one=True):
    48.         try:
    49.             # 创建链接
    50.             self.create_connection()
    51.             # 获取cursor
    52.             self.create_cursor()
    53.             # 执行sql语句
    54.             self.cursor.execute(sql, args=args)
    55.             if is_one:
    56.                 result = self.cursor.fetchone()  # 查询一个结果
    57.             else:
    58.                 result = self.cursor.fetchall()  #
    59.             return result  # 返回查询结果
    60.  
    61.         except Exception as ex:
    62.             print('ex: ', ex)
    63.         finally:
    64.             self.close()  # 关闭

    2.3 插入数据

    2.3.1 插入数据

    1. sql1 = 'insert into test_data_info(stuno, stuname, gender) values(%s,%s,%s)'
    2.  
    3. # 创建对象
    4. client_utils = MySQLClientUtils(DBConfig)
    5. result = client_utils.dml_operation(sql1, args=(1048, '张三', 'boy'))
    6. print(result)

    2.3.2 修改数据

    1. from settings import DBConfig
    2. from mysql_utils import MySQLClientUtils
    3.  
    4. sql2 = 'update test_data_info set stuname=%s where stuno=%s'
    5. # 创建对象
    6. client_utils = MySQLClientUtils(DBConfig)
    7. result = client_utils.dml_operation(sql2, args=('张小亮', 1048))
    8. print(result)

    2.3.3 修改数据

    1. sql3 = 'delete from test_data_info where stuno=%s'
    2.  
    3. # 创建对象
    4. client_utils = MySQLClientUtils(DBConfig)
    5. result = client_utils.dml_operation(sql3, args=(1048, ))
    6. print(result)

    2.3.4 查询数据

    1. sql4 = 'select count(*) from test_data_info'
    2.  
    3. # 创建对象
    4. client_utils = MySQLClientUtils(DBConfig)
    5. result = client_utils.search_operation(sql4)
    6. print(result)

    2.3.5 模糊查询

    1. sql5 = 'select * from test_data_info where stuname like %s'
    2.  
    3. # 创建对象
    4. client_utils = MySQLClientUtils(DBConfig)
    5. result = client_utils.search_operation(sql5, args=('%ma%', ), is_one=False)
    6. print(result)

    三 实际项目数据库操作

    3.1 进入主界面

    1. from settings import DBConfig
    2. from mysql_utils import MySQLClientUtils
    3. import prettytable as pt
    4.  
    5. # 面向过程
    6. def menu():
    7.     while True:
    8.         print('-' * 50, '欢迎来到购物商城', '-' * 50)
    9.         print('1.查询全部商品 2.根据商品名称查询 3.根据编号查询 4.新增商品进购物车 5.清空购物车 6.删除购物车中指定商品 '
    10.               '7.查询购物车 0.退出系统')
    11.         print('-'*120)
    12.         choice = eval(input('请选择：'))
    13.         if choice == 1:
    14.             # 调用查询方法
    15.             result = search_all()
    16.             show(result)
    17.  
    18.         elif choice == 2:
    19.             goods_name = input('请输入要查询的商品名称【支持模糊查询】：')
    20.             # 调用本模块中根据商品名称查询的方法
    21.             result = search_by_name(goods_name)
    22.             show(result)
    23.  
    24.         elif choice == 3:
    25.             goods_id = eval(input('请输入要查询的商品编号：'))
    26.             # 调用本模块中根据商品名称查询的方法
    27.             result = search_by_id(goods_id)
    28.             show(result)
    29.  
    30.         elif choice == 4:
    31.             goods_id = eval(input('请输入想要购买的商品编号: '))
    32.             result = search_by_id(goods_id)[0]
    33.             print('result:', result)
    34.             # result: ({'id': '51.0', 'name': '项目驱动零起点学Java 百万程序员Java学习经验总结', 'price': '49.0'},)
    35.             goods_name = result.get('name')
    36.             price = result.get('price')
    37.             num = eval(input('请输入要购买的数量：'))
    38.             # 调用本模块的添加进购物车的方法
    39.             insert_result = add_cart(goods_id, goods_name, price, num)
    40.             if insert_result > 0:
    41.                 print('商品已经添加到购物车！')
    42.             else:
    43.                 print('添加失败')
    44.  
    45.         elif choice == 5:
    46.             # 清空购物车
    47.             result = clear_cart()
    48.             if result > 0:
    49.                 print('成功清空购物车')
    50.             else:
    51.                 print('购物车中无商品！')
    52.  
    53.         elif choice == 6:
    54.             """6.删除购物车中指定商品"""
    55.             goods_id = eval(input('请输入要删除的商品编号： '))
    56.             result = delete_goods_in_cart(goods_id)
    57.             print('result:', result)
    58.             if result > 0:
    59.                 print('删除购物车商品成功')
    60.             else:
    61.                 print('购物车中无商品')
    62.  
    63.         elif choice == 7:
    64.             """7.查询购物车"""
    65.             result = search_cart()
    66.             show_cart(result)
    67.  
    68.         elif choice == 0:
    69.             print('谢谢使用 ！')
    70.             break
    71.         else:
    72.             print('输入错误，重新选择')
    73.  
    74. if __name__ == '__main__':
    75.     menu()

    3.2  查询全部商品

    sql = 'select * from yanxuan'

    1. def search_all():
    2.     """查询全部商品"""
    3.     sql = 'select * from yanxuan'
    4.     # 调用数据库查询
    5.     result = MySQLClientUtils(config=DBConfig).search_operation(sql, is_one=False)
    6.     return result
    7.  
    8. def show(result):
    9.     """数据显示"""
    10.     tb = pt.PrettyTable()
    11.     tb.field_names = ['编号', '商品名称', '售价']
    12.     for item in result:
    13.         id = item.get('id')
    14.         name = item.get('name')
    15.         price = item.get('price')
    16.         lst = [id, name, price]
    17.         tb.add_row(lst)  # 添加到表格中
    18.     print('tb:', tb)

    3.3 根据商品名称查询

    sql = 'select * from yanxuan where name like %s'

    1. def search_by_name(goods_name):
    2.     sql = 'select * from yanxuan where name like %s'
    3.     # 调用数据库查询
    4.     result = MySQLClientUtils(config=DBConfig).search_operation(sql, args=(f'%{goods_name}%',), is_one=False)
    5.     return result

    3.4 根据编号查询

    sql = 'select * from yanxuan where id = %s'

    1. def search_by_id(goods_id):
    2.     sql = 'select * from yanxuan where id = %s'
    3.     # 调用数据库查询
    4.     result = MySQLClientUtils(config=DBConfig).search_operation(sql, args=(goods_id,), is_one=False)
    5.     return result

    sql = 'insert into cart (goods_id, name, price, num) value (%s, %s, %s, %s)'

    1. def add_cart(id, goods_name, price, num):
    2.     sql = 'insert into cart (goods_id, name, price, num) value (%s, %s, %s, %s)'
    3.     result = MySQLClientUtils(config=DBConfig).dml_operation(sql, args=(id, goods_name, price, num))
    4.     return result

    3.5 清空购物车

    sql = 'delete from cart'

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

    3.6 删除购物车指定商品

    sql = 'delete from cart where goods_id=%s'

    1. def delete_goods_in_cart(goods_id):
    2.     """删除购物车指定商品"""
    3.     sql = 'delete from cart where goods_id=%s'
    4.     result = MySQLClientUtils(config=DBConfig).dml_operation(sql, args=(goods_id, ))
    5.     return result

    3.7 查询购物车所有商品

    sql = 'select * from cart'

    1. def search_cart():
    2.     """查询购物车所有商品"""
    3.     sql = 'select * from cart'
    4.     result = MySQLClientUtils(config=DBConfig).search_operation(sql, is_one=False)
    5.     return result

    显示购物车商品：

    1. def show_cart(result):
    2.     tb = pt.PrettyTable()
    3.     tb.field_names = ['序号', '商品编号', '商品名称', '售价', '数量']
    4.     for item in result:
    5.         id = item.get('id')
    6.         goods_id = item.get('goods_id')
    7.         name = item.get('name')
    8.         price = item.get('price')
    9.         num = item.get('num')
    10.         lst = [id, goods_id, name, price, num]
    11.         tb.add_row(lst)
    12.     print(tb)

  • 相关阅读:
    复习单片机：直流电机(内含1 直流电机介绍+2 ULN2003 芯片介绍+3 硬件设计+4 软件设计+5原始代码+6. 实验现象)
    【算法题】714. 买卖股票的最佳时机含手续费
    解决虚拟机centos8无法连接外网，ping: www.baidu.com: 未知的名称或服务
    U++学习笔记 ------ 多播委托
    【ACM学习】【STL】多重集合multiset和多重映射multimap
    什么是微服务？与分布式又有什么区别？
    贪心算法——活动安排问题
    【设计模式】26.结构型模式-代理模式(Proxy)
    Nmap抓包分析与绕过Windows防火墙
    二叉搜索树详解以及C++实现二叉搜索树（递归和非递归）
  • 原文地址：https://blog.csdn.net/March_A/article/details/133466589