Python连接Mysql数据库操作、以Excel文件导入导出

Python连接Mysql数据库操作、以Excel文件导入导出

一、Pycharm连接Mysql数据库操作 - 增删改查操作

1、写入（增加）数据

在pycharm中先安装 pymysql第三方库

在Mysql数据库软件中支持的增删查改操作在pycharmy语法中也是支持的

例如：增删改查tb_dept数据库部门表的数据

import pymysql

no = input('部门编号:')
name = input('部门名称:')
location = input('部门所在地:')

第一步：创建连接  -->  connection 这是自己在数据库创建的用户

TCL -  transation control languaes

conn = pymysql.connect(host = '10.7.174.55',port = 3306,

​                                               user='long',password = '',

​                                                database='hrs',char='utf8m64')

try:  

​	#第二步：获取游标对象 --> cursor

​	with conn.cursor() as cursor:

​           #第三步：向数据库发出SQL语句 excute  ---> 执行

​           affected_row = cursor.excute(

​           'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',

​             (no, name, location)

​			#用%s来做占位符，不使用python的的其他格式占位符，其意义也不一样			

​              )

​				if affected_rows == 1;
				 	printf('添加部门成功！！')
    			 #第四步：提交事务
        		 #commit
except pymysql.MySQLError:
	#第四步：回滚事务
    #rollback
    conn.rollback()
finnaly:
    #第五步：关闭连接，释放资源
    conn.close()              

删改查的方法是一样的，换汤不换药。

2、删除操作

affected_rows = cursor.wxcute(

​ 'delete from tb_dept where dno = %s', (no,)

)

在输入那只要保留部门编号dno主键就可以删除数据库部门表中的的相应主键数据，

3、改操作

affected_rows = cursor.wxcute(
	​	`'update tb_dept set dname = %s. dloc = %s where dno = %s',`

​ 'update tb_dept set dname = %s. dloc = %s where dno = %s',

​ (name, location, no)

)

4、查操作

查看tb_dept数据库部门表的数据

import pymysql

from pymysql.cursors import Cursor

# 第一步：创建连接 ---> Connection
conn = pymysql.connect(host='10.7.174.55', port=3306,
                       user='long', password='long.123456',
                       database='hrs', charset='utf8mb4')
try:
    # 第二步：获取游标对象 ---> Cursor
    #with conn.cursor() as cursor:不使用字典条件时
    with conn.cursor(pymysql.cursors.DictCursor) as cursor:  # type: Cursor
         #第三步：向数据库发出SQL语句
cursor.execute(
	'select dno as no, danme as name, dloc as dlcation from tb_dept'
)
	print('编号\t部门名称\t部门所在地')
    #第四步：通过游标抓取数据
    #cursor.fetchall() --  抓到的数据以元组的形式打印，一行数据作为一个元组元素，用于抓取小数据时
    #cursor.fetchaone() --  抓到以元组形式打印，按顺序获取数据库数据，每次fetchaone就获取一行数据，超过表数据行就打印None
    #cursor.fetchamany(N) -- 获取指定得行数，超过表中行数打印None
    用while来判断当获取到None时说明表中数据已经取完，反之
    while row := cursor.fetchone:
    	no, name, location = row
    	print(f'{no}\t{name}\t{location}')
    print('cursor.fetchone())
    for row in cursor.fetchone():
    	print(row)
except pymysql.MySQLError as err:
print(err)
finally:
	#第五步：关闭连接，释放资源
	conn.close()

二、Pycharm连接数据库以Excel文件导入导出

1、Pycharm连接数据库以Excel文件导出

Pycharm连接数据库，将二维数据导出为excel文件

excel —> 工作簿 —> workbook ----> / — > 单元格 ----> 数据操作（增删查改）

pycharm操作excel要用到openyxl第三方库，要先在pycharm中安装

import pymysql

from openpyxl.workbook import Workbook
from pymysql.cursors import Cursor


def write_to_sheet(wb: Workbook, sheet_name: str, headers: tuple, cursor: Cursor):
    """
    将数据写入Excel
    :param wb: 工作簿
    :param sheet_name: 工作表的名字
    :param headers: 表头
    :param cursor: 抓取数据的游标
    """
    sheet = wb.create_sheet(sheet_name)
    sheet.append(headers)
    while row := cursor.fetchone():
        sheet.append(row)


def main():
    # 创建工作簿对象
    wb = Workbook()
    conn = pymysql.connect(host='10.7.174.55', port=3306,
                           user='long', password='long.123456',
                           database='hrs', charset='utf8mb4')
    try:
        with conn.cursor() as cursor:  # type: Cursor
            cursor.execute('select dno, dname, dloc from tb_dept')
            write_to_sheet(wb, '部门', ('编号', '名称', '所在地'), cursor)
            cursor.execute(
                'select eno, ename, job, mgr, sal, comm, dno from tb_emp'
            )
            write_to_sheet(wb, '员工', ('编号', '姓名', '职位', '主管', '月薪', '补贴', '所在部门'), cursor)

    except pymysql.MySQLError as err:
        print(err)
    finally:
        wb.save('人力资源信息表1.xlsx')
        conn.close()


if __name__ == '__main__':
    main()

2、Pycharm连接数据库以Excel文件导入

macOS: PyCharm —> Preferences —> Keymap

Windows: File —> Settings —> Keymap

当pycharm连接数据库，以excel文件写入数据库中的数据不小心被自己删除（做了截断表操作时），在pycharm中导入想找回数据后，在写入数据导出excel文件时

截断表：删除数据库指定表操作，表的格式还在只是清空了数据，但是一旦执行此操作，数据就恢复不了，与drop操作一样找不回来数据（要慎用慎用！！！！！）。

操作语法： truncate table 表名（tb_emp）

当截断表有外键约束时，无法删除表数据时，删除外键约束，在进行阶段操作

操作语法： alter … drop…

例如：截断表前面所写入的人力资源信息表1中的部门表数据

import openpyxl
import pymysql

from openpyxl.cell import Cell
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet

conn = pymysql.connect(host='10.7.174.55', port=3306,
                       user='long', password='long.123456',
                       database='hrs', charset='utf8mb4')
                       
加载excel工作簿
wb = openpyxl.load_workbook('人力资源信息表1.xlsx') #type: Workbook
#根据工作表的名字获取工作表
sheet = wb['部门'] # type: Workbook
#获取对行进行迭代的迭代器
rows_iter = sheet.iter_rows()
#读走表头
next(rows_iter) #迭代器获取数据
try:
	with coon.cursor() as cursor:
		#对表单中的所有行进行迭代
		for row in rows_iter:
			param = []
			for cell in row # Cell
				params.append(cell.value)
			cursor.excute( 'insert into tb_dept (dno, dname, dloc) values (%s, %s, %s)',

                params
			)
		conn.commit()
except pymysql.MySQLError as err:
	print(err)
	conn.rollback()
finally:
	conn.close()