Python程序接入MySQL数据库

Python程序接入MySQL数据库

• 创建连接:Connection

  import pymysql
  conn = pymysql.connect(host = '', port = '', user = '', password = '', database = '', charset = '') 
  
  """
  host : 数据库的服务器地址，默认为localhost
  port : 端口，默认是3306
  user : 数据库登陆用户名，默认是当前程序运行用户
  password : 登录密码，默认是空字符串
  database : 操作的数据库
  charset : 数据库编码，一般是utf8
  """
  

• 获取游标:Cursor

  cursor = conn.cursor()
  

• 通过游标对象发出SQL :

  # 执行一条语句
  cursor.execute()
  # 进行批处理
  cursor.executemany()
  

• 两种情况：写数据：提交或者回滚 / 读数据：通过游标抓取

  # 写数据
  """
  1、提交
  conn.commit()
  2、回滚
  conn.rollback()
  """
  
  # 读数据
  """
  1、读一条
  cursor.fetchone()
  2、读全部
  cursor.fetchall()
  3、读多条
  cursor.fetchmany(size)
  """
  

• 关闭连接

  conn.close()
  

例一：Python接入MySQL数据库实现insert操作

# 导包
import pymysql

dept_no = int(input('部门编号:'))
dept_name = input('部门名称')
dept_location = input('部门所在地')

# 第一步：创建连接 - Connection
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')

try:
    # 第二步:获取游标对象
    cursor = conn.cursor()
    # 第三步:通过游标对象发出SQL
    affected_rows = cursor.execute(
        'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',
        (dept_no, dept_name, dept_location))
    if affected_rows == 1:
        print("部门添加成功！！！")
    # 第四步:提交事务
    conn.commit()

expect pymysql.MySQLError as err:
    print(err)
    # 第四步:回滚事务
    conn.rollback()

finally:
    # 第五步:关闭连接 ，释放资源
    conn.close()

例二：Python接入MySQL将二维表的数据导出到Excel文件

import openpyxl
import pymysql
from openpyxl.worksheet.worksheet import Worksheet

# 1. 创建工作簿
wb = openpyxl.Workbook()
# 2. 创建工作表
# sheet = wb.create_sheet('员工信息表')  # type: Worksheet
# 2. 获取默认的工作表
sheet = wb.active  # type: Worksheet
sheet.title = '员工信息表'
# 3. 写数据 - 添加表头
sheet.append(('工号', '姓名', '职位', '月薪', '补贴', '部门编号', '部门名称', '部门所在地'))

conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    cursor = conn.cursor()
    cursor.execute('select eno, ename, job, sal, comm, dno, dname, dloc from tb_emp natural join tb_dept')
    while row := cursor.fetchone():
        # 3. 将记录（作为一行）写入Excel工作表
        sheet.append(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    # 4. 保存工作簿
    wb.save('人力资源管理.xlsx')
    conn.close()

例三：将Excel工作表的数据导入到数据库二维表中

import openpyxl
import pymysql
from openpyxl.worksheet.worksheet import Worksheet


def batch_insert(conn: pymysql.Connection, data_list: list[tuple]):
    try:
        # with...as .. 执行完语句会自动关闭连接
        with conn.cursor() as cursor:
            # 数据量多，使用批处理
            cursor.executemany(
                'insert into sales_2020 '
                '    (sale_date, sale_area, sale_channel, order_no, brand, price, quantity) '
                'values '
                '    (%s, %s, %s, %s, %s, %s, %s)',
                data_list
            )
        # 事务提交
        conn.commit()
     # 事务回滚
    except pymysql.MySQLError as err:
        print(err)
        conn.rollback()


def main():
    # 加载Excel工作簿
    wb = openpyxl.load_workbook('2020年销售数据.xlsx')
    # 获取指定的工作表
    sheet = wb['data']  # type: Worksheet
    # 创建连接
    conn = pymysql.connect(host='localhost', port=3306,
                           user='guest', password='Guest.618',
                           database='hrs', charset='utf8mb4')
    try:
        data_list = []
        # 通过行号和列号拿到每一个数据
        for row_index in range(2, sheet.max_row + 1):
            row_data = []
            for col_index in range(1, sheet.max_column + 1):
                # 调用cell通过行列号拿到数据
                cell = sheet.cell(row_index, col_index)
                row_data.append(cell.value)
            data_list.append(row_data)
            # 做一个批处理，让数据100个100个的添加
            if len(data_list) == 100:
                batch_insert(conn, data_list)
                data_list.clear()
        if len(data_list) > 0:
            batch_insert(conn, data_list)
    # 关闭连接
	finally:
        conn.close()


if __name__ == '__main__':
    main()