day32_创建Mysql账户，以及python操作Mysql

day32

总结

• Python程序接入MySQL数据库
      ~ 三方库:
          - mysqlclient ---> C ---> 有可能会因为底层C语言的缺失而失败 ---> import MySQLdb
          - pymysql ---> Python ---> 安装一定会成功 ---> import pymysql
  
  except 指定的一场类型是父类型，那么可以捕获到子类型的异常
  因为异常捕获需要遵循面向对象编程的里氏替换原则 （LSP）
  

• 创建Mysql账户,并授权以及回收权限

  create user 'guest'@'%'
  identified by 'Guest123';
  
  grant create, alter, insert, update, select on hrs.* to 'guest'@'%' with grant option;
  grant create, alter, insert, update, select on school.* to 'guest'@'%' with grant option;
  grant all privileges on school.* to 'guest'@'%' with grant option;
  grant all privileges on hrs.* to 'guest'@'%' with grant option;
  grant drop on hrs.* to 'guest'@'%';
  grant drop on school.* to 'guest'@'%';
  grant drop on crawp.* to 'guest'@'%';
  grant create on *.* to 'guest'@'%' with grant option;
  revoke create on *.* from 'guest'@'%';
  
  create database if not exists crawp default charset utf8mb4;
  grant all privileges on crawp.* to 'guest'@'%' with grant option;
  

• 查看mysql执行计划

  use hrs;
  
  explain select eno, ename, job from tb_emp where eno=7800;
  
  explain select eno, ename, job from tb_emp where ename='张三丰';
  
  create index as idx_ename on hrs.tb_emp (ename);
  

• python连接数据库

  • insert,update,delete

  """
  !./env python
  -*- coding: utf-8 -*-
  @Time:  2021/6/10 10:44
  @Author:    三玖天下第一
  @File: example01.py
  @Software: PyCharm
  
  pip config set global.Index-url https://pypi.doubanio.com/simple
  pip install pymysql
  向关系型数据库的二维表中插入数据
  """
  
  import pymysql
  
  # 第一步: 建立数据库连接
  # host - 主机，确定连接哪一台服务器， port - 端口,确定服务器上的端口
  # user - 用户名,  password - 密码, （提示:强烈建议不用root)
  # database和charset - 连接数据库和使用的字符集
  # autocommit - 自动提交
  conn = pymysql.connect(host='10.7.174.55', port=3306, user='guest',
                         password='Guest123', database='hrs',
                         charset='utf8mb4', autocommit=False)
  try:
      try:
          print('添加部门')
          no = int(input('请输入编号:'))
          name = input('请输入部门名称:')
          location = input('请输入部门位置:')
          # 第二步：获取游标对象
          with conn.cursor() as cursor:
              # 第三步：通过游标想数据库服务器发出SQL语句，获取执行结果
              affected_rows = cursor.execute(
                  'insert into tb_dept(dno, dname, dloc) values (%s, %s, %s)',
                  (no, name, location)
              )
              # if conn.affected_rows() == 1:
              if affected_rows == 1:
                  print('添加部门成功')
              # 第四步：提交上面的操作
          conn.commit()
      except pymysql.MySQLError as err:
          print('添加失败', f'错误提示:{err}')
          # 第四步： 回滚（操作失败）
          conn.rollback()
      try:
          no = int(input('请输入要删除部门的编号:'))
          with conn.cursor() as cursor:
              affected_rows = cursor.execute(
                  'delete from tb_dept where dno=%s',
                  no
              )
              if conn.affected_rows() == 1:
                  print(f'删除部门{no}成功...')
          conn.commit()
      except pymysql.MySQLError as err:
          print('删除失败', f'错误提示{err}')
          conn.rollback()
      try:
          no = int(input('请输入要修改部门的编号:'))
          name = input('请输入修改后的部门名称:')
          location = input('请输入修改后的部门位置:')
          with conn.cursor() as cursor:
              affected_rows = cursor.execute(
                  'update tb_dept set dname=%s,dloc=%s where dno=%s',
                  (name, location, no)
              )
              if affected_rows == 1:
                  print(f'更新部门{no}成功...')
          conn.commit()
      except pymysql.MySQLError as err:
          print('更新失败', f'错误提示{err}')
          conn.rollback()
  finally:
      # 第五步：关闭连接
      conn.close()
  
  

  • select

  """
  !./env python
  -*- coding: utf-8 -*-
  @Time:  2021/6/10 14:06
  @Author:    三玖天下第一
  @File: example02.py
  @Software: PyCharm
  """
  
  import pymysql
  import openpyxl
  
  wb = openpyxl.Workbook()
  sheet = wb.active
  sheet.title = '部门信息'
  sheet.append(['员工编号', '姓名', '职位', '隶属', '工资', '补贴', '部门名称', '部门所在位置', '部门编号'])
  
  # 第一步: 连接到数据库
  conn = pymysql.Connect(host='127.0.0.1', port=3306,
                         user='guest', password='Guest123',
                         database='hrs', charset='utf8mb4')
  
  try:
      try:
          # 第二步: 获取游标对象
          # 可设置读取数据是字典还是元组
          # with conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
          with conn.cursor() as cursor:
              # 第三步: 通过游标执行sql
              cursor.execute(
                  '''SELECT t1.eno as 员工编号, t1.ename 姓名, t1.job 职位, t3.ename 隶属, t1.sal 工资, t1.comm 补贴, 
                  dname 部门名称, dloc 部门所在位置, t1.dno 部门编号
                  FROM hrs.tb_emp t1 inner join hrs.tb_dept t2 on t1.dno=t2.dno
                  left join hrs.tb_emp t3 on t1.mgr=t3.eno'''
              )
              # 第四步: 通过游标获取数据
              # cursor.fetchall() # 获取全部数据
              # print(cursor.fetchone()) # 获取一行数据
              # print(cursor.fetchmany(100))  # 获取100行数据
              # 提示: 如果数据体量太大,fetchall最好不用,否则可能内存不足而崩溃,而且加载数据的时间也会特别长
              # 迭代器
              # for row in iter(lambda: cursor.fetchmany(2), []):
              #     print(row)
              for row in cursor:
                  sheet.append(row)
      except pymysql.MySQLError as err:
          print(err)
  finally:
      wb.save(r'./file/hrs.xlsx')
      wb.close()
      conn.close()
  
  

  • sql数据转excel

  """
  从数据库中读取员工的编号、姓名、职位、月薪和部门名称，写入Excel文件
  """
  import openpyxl
  import pymysql
  
  conn = pymysql.connect(host='10.7.174.103', port=3306,
                         user='guest', password='Guest.618',
                         database='hrs', charset='utf8mb4')
  try:
      with conn.cursor() as cursor:
          cursor.execute(
              'select eno, ename, job, sal, dname from tb_emp t1 '
              'inner join tb_dept t2 on t1.dno=t2.dno'
          )
          wb = openpyxl.Workbook()
          # ws = wb.create_sheet('员工表')
          ws = wb.active
          titles = ('工号', '姓名', '职位', '月薪', '部门')
          # openpyxl操作Excel时，行和列的索引都是从1开始的
          for col_idx, col_name in enumerate(titles):
              ws.cell(1, col_idx + 1, col_name)
          for row_idx, emp_row in enumerate(cursor.fetchall()):
              for col_idx, col_value in enumerate(emp_row):
                  ws.cell(row_idx + 2, col_idx + 1, col_value)
          wb.save('人力资源管理.xlsx')
  except pymysql.MySQLError as err:
      print(err)
  finally:
      conn.close()
  
  

  • excel转sql数据

    """
    从Excel文件中读取数据写入数据库
    create database stock default character set utf8mb4;
    
    use stock;
    
    create table tb_baba_stock
    (
    stock_id bigint unsigned auto_increment comment '编号',
    trade_date date not null comment '交易日',
    high_price decimal(12, 4) not null comment '最高价',
    low_price decimal(12, 4) not null comment '最低价',
    open_price decimal(12, 4) not null comment '开盘价',
    close_price decimal(12, 4) not null comment '收盘价',
    trade_volume bigint unsigned not null comment '交易量',
    primary key (stock_id)
    );
    """
    import openpyxl
    import pymysql
    
    wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
    ws = wb.active
    params = []
    for row_idx in range(2, ws.max_row + 1):
        values = []
        for col_idx in range(1, ws.max_column):
            values.append(ws.cell(row_idx, col_idx).value)
        params.append(values)
    
    conn = pymysql.connect(host='10.7.174.103', port=3306,
                           user='guest', password='Guest.618',
                           database='stock', charset='utf8mb4')
    try:
        with conn.cursor() as cursor:
            # 执行批量插入操作
            cursor.executemany(
                'insert into tb_baba_stock '
                '   (trade_date, high_price, low_price, open_price, close_price, trade_volume) '
                'values '
                '   (%s, %s, %s, %s, %s, %s)',
                params
            )
        conn.commit()
    except pymysql.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()
    
    

  • example

    """
    !./env python
    -*- coding: utf-8 -*-
    @Time:  2021/6/10 15:26
    @Author:    三玖天下第一
    @File: example_insert.py
    @Software: PyCharm
    
    excel 转sql
    """
    import pymysql
    import openpyxl
    
    wb = openpyxl.load_workbook(r'./file/阿里巴巴2020年股票数据.xlsx')
    
    ws = wb.active
    row_iter = ws.iter_rows()
    
    conn = pymysql.Connect(host='127.0.0.1', port=3306,
                           user='guest', password='Guest123',
                           database='crawp', charset='utf8mb4')
    
    
    def insertmany():
        try:
            with conn.cursor() as cursor:
                next(row_iter)
                ws_data = []
    
                def execute_sql():
                    cursor.executemany(
                        '''insert into tb_alidata(ali_date, ali_hight, ali_low, ali_open, ali_close, ali_volume, ali_adj_close) values(%s,%s,%s,%s,%s,%s,%s)''',
                        ws_data
                    )
    
                index = 0
                for row_data in row_iter:
                    index += 1
                    ws_data.append([cell.value for cell in row_data])
                    if index >= 100:
                        index = 0
                        execute_sql()
                        ws_data.clear()
                execute_sql()
            conn.commit()
        except pymysql.MySQLError as err:
            print(err)
            conn.rollback()
    
    
    def insert1():
        try:
            with conn.cursor() as cursor:
                titles = next(row_iter)
                for row_data in row_iter:
                    row_values = [cell.value for cell in row_data]
                    cursor.execute(
                        '''insert into tb_alidata(ali_date, ali_hight, ali_low, ali_open, ali_close, ali_volume, ali_adj_close) values(%s,%s,%s,%s,%s,%s,%s)''',
                        row_values
                    )
            conn.commit()
        except pymysql.MySQLError as err:
            print(err)
            conn.rollback()
    
    
    if __name__ == '__main__':
        try:
            insertmany()
        finally:
            conn.close()
    
    

    """
    !./env python
    -*- coding: utf-8 -*-
    @Time:  2021/6/10 18:32
    @Author:    三玖天下第一
    @File: example_insert_csv.py
    @Software: PyCharm
    
    csv 转 sql
    """
    
    import pymysql
    import csv
    
    f = open(r'./file/jd2.csv', 'r', newline='', encoding='utf-8')
    reader = csv.reader(f)
    
    conn = pymysql.Connect(host='127.0.0.1', port=3306,
                           user='guest', password='Guest123',
                           database='crawp', charset='utf8mb4')
    
    
    def insertmany():
        try:
            with conn.cursor() as cursor:
                def execute_sql(data):
                    cursor.executemany(
                        '''insert into tb_nb_computer(title, detail, img, price, comment_num, comment_url, business, tags) values(%s,%s,%s,%s,%s,%s,%s,%s)''',
                        data
                    )
    
                next(reader)
                temp_data = []
                index = 0
                for row_data in reader:
                    temp_data.append(row_data)
                    if index >= 1:
                        index = 0
                        execute_sql(temp_data)
                        temp_data.clear()
                if temp_data:
                    execute_sql(temp_data)
            conn.commit()
        except pymysql.MySQLError as err:
            print(err)
            conn.rollback()
    
    
    if __name__ == '__main__':
        try:
            insertmany()
        finally:
            f.close()
            conn.close()
    
    

---