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()
      
      

上一篇:Day32:把数组排成最小的数


下一篇:day32_学习总结