Day32-Python连接MySQl

Day32-Python连接MySQl

1. Python程序接入MySQl数据库

   • 三方库

     • mysqlclient —> C —> 有可能会因为底层C语言库的缺失而失败 —> import MySQLdb
     • pymysql —> Python —> 安装一定会成功 —> import

   • 第一步: 建立连接

     • conn = pymysql.connect(host='*****', port=3306,
                              user='*****', passwd='*******',
                              database='hrs', charset='utf8mb4')
       

   • 第二步: 获取游标对象

     • with conn.cursor() as cursor:
       

   • 第三步: 通过游标向数据库发出SQL语句

     • affected_rows = cursor.execute(
           'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',
           (no, name, location)
       )
       

   • 第四步: 提交上面操作

     •     conn.commit()
       except pymysql.MySQLError as err:
           print(err)
       

   • 第四步: 回滚 (操作失败)

     • conn.rollback()
       

   • 第五步: 关闭连接 (释放资源)

     • conn.close()
       

2. Python插入MySQL数据完整代码

   • import pymysql
     
     
     no = int(input('部门编号: '))
     name = input('部门名称: ')
     location = input('部门所在地: ')
     # 第一步：建立连接
     # host  -  主机 - 确定要连接哪一台服务器(IP地域或主机名)
     # port  -  端口 - 确定连接服务器上的那个服务(d端口是用来区分不同的服务器的)
     # user和password - 用户和口令 (提示：一般不能用超级管理员账号)
     # database  -  链接的数据库
     # charset  -  字符编码
     
     
     conn = pymysql.connect(host='***', port=3306,
                            user='***', passwd='***',
                            database='hrs', charset='utf8mb4')
     
     try:
         # 第二步：获取游标对象
         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 affected_rows == 1:
                 print('添加部门成功')
         # 第四步：提交上面的操作
         conn.commit()
     except pymysql.MySQLError as err:
         print(err)
         # 第四步：回滚（操作失败）
         conn.rollback()
     finally:
         # 第五步：关闭连接（释放资源）
         conn.close()
     

3. Python查询MySQL完整代码

   • import pymysql
     
     
     # no = int(input('部门编号: '))
     # name = input('部门名称: ')
     # location = input('部门所在地: ')
     # 第一步：建立连接
     # host  -  主机 - 确定要连接哪一台服务器(IP地域或主机名)
     # port  -  端口 - 确定连接服务器上的那个服务(d端口是用来区分不同的服务器的)
     # user和password - 用户和口令 (提示：一般不能用超级管理员账号)
     # database  -  链接的数据库
     # charset  -  字符编码
     
     
     conn = pymysql.connect(host='*****', port=3306,
                            user='****', passwd='*****',
                            database='hrs', charset='utf8mb4')
     
     try:
         # 第二步：获取游标对象
         with conn.cursor() as cursor:
             # 第三步: 通过游标执行SQL
             cursor.execute('select dno as no, dname as name, dloc from tb_dept')
             # print(cursor.fetchone())
             # 第四步: 通过游标抓取数据(查询结果)
             # fetchont / fetchmany / fetchall
             # 提示: 如果查询结果数据体量很大, 那么最好不使用fetchall,
             #      否则程序可能因为内存不足而崩溃, 而且加载数据的时间也会特别长
             for row in iter(lambda : cursor.fetchone(), None):
                 print(row)
     # except指定的异常类型是父类型, 那么可以捕获到子类型的异常
     # 因为异常捕获需要遵循面向对象编程里氏替换原则 (LSP  -  Liskov Substitution Principle)
     # 任何时候都可以用子类型替换掉父类型, 父类型未必能替换子类型
     except pymysql.MySQLError as err:
         print(err)
     finally:
         # 第五步：关闭连接（释放资源）
         conn.close()
     

4. 获取MySQL数据写入Excel

   • """
     读取员工的编号、姓名 、职位、 月薪和部门名称, 写入Excel文件
     """
     import openpyxl
     import pymysql
     
     conn = pymysql.connect(host='******', port=3306,
                            user='******', passwd='******',
                            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('员工表')
             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()
     

5. 从Excel文件中读取数据写入数据库

   • """
     从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='******', port=3306,
                            user='******', passwd='*****',
                            database='hrs', 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()
     

6. 从Excel读取数据到数据库 (不使用批处理 )

   • """
     从Excel文件中读取数据写入数据库 - 不使用批处理
     """
     import openpyxl
     import pymysql
     
     conn = pymysql.connect(host='******', port=3306,
                            user='*******', passwd='*******',
                            database='hrs', charset='utf8mb4')
     try:
         with conn.cursor() as cursor:
             wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
             ws = wb.active
             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)
                 cursor.execute(
                     'insert into tb_baba_stock '
                     '   (trade_date, high_price, low_price, open_price, close_price, trade_volume) '
                     'values '
                     '   (%s, %s, %s, %s, %s, %s)',
                     values
                 )
         conn.commit()
     except pymysql.MySQLError as err:
         print(err)
         conn.rollback()
     finally:
         conn.close()
     

   ​