一、python数据库操作:

1.数据库编程概述:

数据库编程就是针对数据库的操作，通过编写程序的方式，让程序做为数据库的客户端进行数据库操作。这种通过使用程序代码的方式去连接数据库服务器，通过和服务器进行交互完成对数据库的增删改查的方式，就称为数据库编程。而此刻学习的pymysql就是一种客户端。

2.Python 中操作 MySQL 步骤:

• 连接数据库;
• 获取游标;
• 数据库操作;
• 关闭游标;
• 关闭数据库连接。

3.pymysql操作:

在 Pyhton 中，使用第三方模块 Pymysql 模块来对数据库进行编程。

下载模块:

# 终端运行
pip3 install pymysql

3.1 导入模块

#导入pymysql模块下的connect
 from pymysql import connect

3.2Connection 对象 目的：用于建立代码与数据库的连接

创建连接对象 conn=connect(参数列表)

  * 参数host：连接的mysql主机，如果本机是'localhost'
  * 参数port：连接的mysql主机的端口，默认是3306
  * 参数database：数据库的名称
  * 参数user：连接的用户名
  * 参数password：连接的密码
  * 参数charset：通信采用的编码方式，推荐使用utf8

3.3 关闭连接 conn.close()

3.4提交数据 conn.commit()

3.5 撤销数据 conn.rollback()

3.6 通过连接获取游标 cur = conn.cursor()返回Cursor对象，用于执行sql语句并获得结果

Cursor游标对象 目的: 执行sql语句

获取Cursor对象 cur = conn.cursor()

使用游标执行SQL语句 cur.execute(operation , [parameters])

执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete语句

获取结果集中的一条 cur.fetchone() 返回一个元组 如 (1,'妲己',18)

获取结果集中的一条 cur.fetchmany(2) 返回一个元组 如 ((1,'妲己',18),2,'公孙离',20)）

获取结果集中的所有 cur.fetchall() 执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回. 如((1,'妲己',18),（2,'公孙离',20),(3,'姜子牙',28))

关闭游标 cur.close()

4. 使用 pymsql 完成数据查询:

4.1准备数据

 -- 创建数据库
 create database python_db charset=utf8;

 -- 使用数据库
 use python_db;

 -- students表
 create table students(
     id int unsigned primary key auto_increment not null,
     name varchar(20) default '',
     age tinyint unsigned default 0,
     height decimal(5,2),
     gender enum('男','女','中性','保密') default '保密',
     cls_id int unsigned default 0,
     is_delete int default 0
 );

 -- classes表
 create table classes (
     id int unsigned auto_increment primary key not null,
     name varchar(30) not null
 );

 -- 向students表中插入数据
 insert into students values
 (0,'小明',18,180.00,2,1,0),
 (0,'小月月',18,180.00,2,2,1),
 (0,'彭于晏',29,185.00,1,1,0),
 (0,'刘德华',59,175.00,1,2,1),
 (0,'黄蓉',38,160.00,2,1,0),
 (0,'凤姐',28,150.00,4,2,1),
 (0,'王祖贤',18,172.00,2,1,1),
 (0,'周杰伦',36,NULL,1,1,0),
 (0,'程坤',27,181.00,1,2,0),
 (0,'刘亦菲',25,166.00,2,2,0),
 (0,'金星',33,162.00,3,3,1),
 (0,'静香',12,180.00,2,4,0),
 (0,'郭靖',12,170.00,1,4,0),
 (0,'周杰',34,176.00,2,5,0);

 -- 向classes表中插入数据
 insert into classes values (0, "python_01期"), (0, "python_02期");

4.2 查询数据

 # 导入模块
 from pymysql import connect
 # 连接数据库
 conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
 # 获取游标
 cur = conn.cursor()
 # 以字符串形式书写SQL语句,因为SQL语句中也会出现字符串，所以建议使用 ``` 引号形式将SQL诗句引起来
 sql_str = '''select * from students;'''
 # 执行SQL语句
 row_count = cur.execute(sql_str)
 # 显示执行 SQL 语句影响的行数
 print(row_count)
 # 获取一条记录
 row_one = cur.fetchone()
 # 显示获取的记录
 print(row_one)
 # 获取多条记录
 row_many = cur.fetchmany(4)
 # 遍历输出所有的结果
 for t in  row_many:
     print(t)
 # 获取所有的数据
 row_all = cur.fetchall()
 # 遍历输出所有的结果
 for t in  row_all:
     print(t)
 # 关闭游标
 cur.close()
 # 关闭数据库
 conn.close()

注意：因为在获取数据时，游标是移动的，所以前面取过的数据，后面不会再取了。

4.3 增删改

 # 导入模块
 from pymysql import connect
 # 连接数据库
 conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
 # 获取游标
 cur = conn.cursor()
 # 以字符串形式书写SQL语句
 # sql_str = '''insert into students values(0,'新来的',20,180,'男',1,1)'''
 # sql_str = '''update students set name = '王钢蛋' where name = '新来的'; '''
 sql_str = '''delete from students where name='王钢蛋'; '''
 # 执行SQL语句
 row_count = cur.execute(sql_str)
 # 在执行增删改操作时，需要向数据库提交操作，否则操作不成功
 conn.commit()
 # 关闭游标
 cur.close()
 # 关闭数据库
 conn.close()

4.4 回滚(取消操作)

 # 导入模块
 from pymysql import connect
 # 连接数据库
 conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123',charset='utf8')
 # conn.autocommit(True)
 # 获取游标
 cur = conn.cursor()
 # 以字符串形式书写SQL语句
 sql_str = '''insert into students values(0,'新来的',20,180,'男',1,1)'''
 #插入10条数据
 for i in range(10):
     # 执行SQL语句
     row_count = cur.execute(sql_str)
 # 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
 conn.rollback()
 # 关闭游标
 cur.close()
 # 关闭数据库
 conn.close()

5.参数化列表防止SQL注入

5.1什么是SQL注入？

• 产生原因: 后台对用户提交的带有恶意的数据和 SQL 进行字符串方式的拼接，得到了脱离原意的 SQL 语句，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。
• 如何防止: SQL 语句的参数化, 将 SQL 语句的所有数据参数存在一个列表中传递给 execute 函数的第二个参数
• 注意:
• 此处不同于python的字符串格式化，必须全部使用%s占位

  # 导入模块
  from pymysql import connect
  find_name = input("请输姓名：")
  # 连接数据库
  conn = connect(host='localhost', port=3306, database='python_db', user='root', password='123123', charset='utf8')
  # 获得Cursor对象
  cur = conn.cursor()
  # # # 非安全的方式
  # # # 输入 "小明" or 1
  # sql = '''select * from students where name=%s''' % find_name
  # print("""sql===>%s<====""" % sql)
  # # 执行select语句，并返回受影响的行数：查询所有数据
  # count = cur.execute(sql)

  # 安全的方式
  # 构造参数列表 
  params = [find_name]
  sql = '''select * from students where name=%s;'''
  print("""sql===>%s<====""" % sql)
  # 执行select语句，execute方法在内部实现了防SQL注入的功能，但具体如何实现并不清楚，隐藏了细节 
  count = cur.execute(sql, params)
  # 注意：
  # 如果要是有多个参数，需要进行参数化
  # 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可 
  # 打印受影响的行数
  print(count)
  # 获取查询的结果
  result = cur.fetchall()
  # 打印查询的结果
  print(result)
  # 关闭Cursor对象
  cur.close()
  # 关闭Connection对象
  conn.close()

6. 数据库编程练习

6.1准备数据

创建数据库

  create database JDDB charset=utf8;
  use JDDB

导入数据

  source JDDB.sql

6.2 功能实现:

#!/usr/bin/venv python3
# coding: utf-8
"""完成对JD数据的操作"""

from pymysql import connect
from config import *


# 定义一个JD数据服务类
class JDServer(object):
    """定义数据哭服务类"""
    def __init__(self):
        # 获取数据库连接
        self.__connect = connect(host = host, port = port, database= database, 
                                 user=user, password=password, charset='utf8')
        # 获取游标
        self.__cur = self.__connect.cursor()

    def __del__(self):
        # 释放连接
        self.__cur.close()
        self.__connect.close()

    def __show(self):
        """定义功能选项"""
        print("欢迎使用JD仓储系统(v1.0)".center(50, "*"))
        print("1查询所有商品信息")
        print("2查询所有商品所在种类信息")
        print("3查询所有商品所在品牌信息")
        print("4添加商品种类")
        print("5根据id查询商品信息")
        print("6根据id查询商品信息安全方式")
        print("7退出 ")

    # 定义功能方法
    @staticmethod
    def __print_ret(result):
        for tmp in result:
            print(tmp)
        print("="*80)

    def __fetch_all(self):
        """查询所有商品信息"""
        # sql语句
        sql_format = """select * from goods;"""
        self.__cur.execute(sql_format)
        result = self.__cur.fetchall()
        self.__print_ret(result)

    def __fetch_brands(self):
        """查询所有商品所在种类信息"""
        sql_format = """select goods.name as 商品名, goods_cates.name 种类名 
                        from goods left join goods_cates on goods.cate_id = goods_cates.id;"""
        self.__cur.execute(sql_format)
        result = self.__cur.fetchall()
        self.__print_ret(result)

    def __fetch_cates(self):
        """查询所有商品所在品牌信息"""
        sql_format = """select goods.name, goods_brands.name 
                        from goods left join goods_brands on goods.brand_id = goods_brands.id;"""
        self.__cur.execute(sql_format)
        result = self.__cur.fetchall()
        self.__print_ret(result)

    def __add_brand(self):
        """添加商品种类"""
        brand = input("输入新添加的商品种类：")
        sql_format = """insert into goods_brands(name) values (%s);"""
        self.__cur.execute(sql_format, (brand,))
        self.__connect.commit()
        print("添加商品类型%s成功..." % brand)

    def __fetch_goods_by_id(self):
        """根据id查询商品信息"""
        goods_id = input("输入你要查询商品的id：")
        sql_format = """select * from goods where id = %s; """ % goods_id
        self.__cur.execute(sql_format)
        result = self.__cur.fetchall()
        self.__print_ret(result)

    def __fetch_goods_by_id_safe(self):
        """根据id查询商品信息安全方式"""
        goods_id = input("输入你要查询商品的id：")
        sql_format = """select * from goods where id = %s; """
        self.__cur.execute(sql_format, (goods_id,))
        result = self.__cur.fetchall()
        self.__print_ret(result)

    # 定义启动方法
    def run(self):
        while True:
            self.__show()
            option = input("输入你要执行的操作：")
            if option == "1":
                self.__fetch_all()
            elif option == "2":
                self.__fetch_brands()
            elif option == "3":
                self.__fetch_cates()
            elif option == "4":
                self.__add_brand()
            elif option == "5":
                self.__fetch_goods_by_id()
            elif option == "6":
                self.__fetch_goods_by_id_safe()
            elif option == "7":
                print("欢迎下次使用...")
                exit(0)
            else:
                print("输入的选项不正确，请重新输入...")


if __name__ == '__main__':
    jd = JDServer()
    jd.run()