python建立简单的人事管理系统（六）下

sql->excel:pandas,openpyxl                               excel->sql:xlrd,openpyxl

导入数据库时，每执行完一行excel就执行一次sql语句 [xlrd]

sql2 = "insert rs values('{0}','{1}','{2}','{3}','{4}')".format(gh, xm, xb, cs, zw)

未导入前，数据库中只有0004号员工，excel表中是ayy数据

导入前 ：                                                                     导入后：

import pyodbc
import xlrd
path = 'D:\\A\\test-xlwt.xlsx'#【该表是xlwt创建的】
dr=xlrd.open_workbook(path)
sheetName=dr.sheets()[0]#获取指定表名【将此表作为要操作的表】
rows=sheetName.nrows#获取总行数
cols=sheetName.ncols#获取总列数
#连接数据库
co = pyodbc.connect('DRIVER={sql server};SERVER=127.0.0.1;DATABASE=rsgldb')
yb=co.cursor()
for i in range(1,rows):
    values=[]
    for j in range(cols):
        # print(sheetName.cell_value(i,j),end='\t')
        values.append(sheetName.cell_value(i,j))
    # print(values)
    sql= "insert rs values('{}','{}','{}','{}','{}')".format(values[0],values[1], values[2], values[3], values[4])
    # print(sql)
    yb.execute(sql)
    co.commit()#一定要提交，保证数据库数据同步，少了此句，数据库不会加入数据
yb.close()
co.close()
print('excel->sql完成')

从数据库导出数据[pandas]

• 将获取的数据转为一维列表【数据库按行返回 类型是<class 'pyodbc.Row'>，提取之后是<class 'str'>】
• 将一维列表组合成二维表
• 设置clounms【列表签】
• 设置是否打印序号

import pyodbc
import pandas as pd
path = 'D:\\A\\sqltoexcel.xlsx'
list_info=[]

co = pyodbc.connect('DRIVER={sql server};SERVER=127.0.0.1;DATABASE=rsgldb')
yb=co.cursor()
sql = "select * from rs"
yb.execute(sql)
arr=yb.fetchone()
while arr:
    value = (arr[0], arr[1], arr[2], arr[3], arr[4])
    list_info.append(value)
    arr=yb.fetchone()
yb.close()
co.close()

title=['工号','姓名','性别','出生','部门']
df=pd.DataFrame(list_info,columns=title)
print(df)
df.to_excel(path,index=False)
print('sql->excel完成')

        

添加进系统[openpyxl]

不管那个模块，先想办法转换成list 然后就简单多了，利用前面的导入导出，再加上数据库，导入导出就做成了

from tkinter import filedialog
path=filedialog.askopenfilename(title='请选择一个文件')#生成选择目录,只能选择文件
path2=filedialog.asksaveasfilename(title='输入保存路径')#需要手动输入文件名和后缀,或者在程序中设置要生成的格式

• 主窗口创建的按钮点击进去出现两个按钮一个是导入系统excel->sql,另一个是到出系统sql->excel,点击按钮执行相对于的操作

from tkinter import *
r6 = Tk()
r6.title('导入导出')
r6.geometry('300x200+100+100')
f6 = Frame(r6, bg='#52d362')  # 建立背景区域设置大小
f6.place(x=0, y=0, width=300, height=200)
b1 = Button(r6, text="导入系统", width=37, height=3)#, command=lambda: In())
b1.place(x=10, y=10)
b2 = Button(r6, text="导出系统", width=37, height=3)#, command=lambda: Out())
b2.place(x=10, y=90)
b3 = Button(r6, text="返回", width=37, height=1, command=r6.destroy)
b3.place(x=10, y=160)
r6.mainloop()

•  sql->excel【注意openpyxl的excel单元格是从1开始】

import openpyxl
import pyodbc
from tkinter import filedialog
def Out():  # sql->excel
    path = filedialog.asksaveasfilename(title='选择保存位置')
    co = pyodbc.connect('DRIVER={sql server};SERVER=127.0.0.1;DATABASE=rsgldb')  # 连接数据库
    yb = co.cursor()
    sql = "select * from rs"
    yb.execute(sql)
    arr = yb.fetchone()
    wb = openpyxl.Workbook()
    work = wb.active
    work.cell(1, 1).value = '工号'
    work.cell(1, 2).value = '姓名'
    work.cell(1, 3).value = '性别'
    work.cell(1, 4).value = '出生日期'
    work.cell(1, 5).value = '所属部门'
    n = 2  # excel的行
    while arr:
        # print(arr[0], arr[1], arr[2], arr[3], arr[4])
        work.title = "入职人员"
        for i in range(0, 5):  # excel的列
            work.cell(n, i + 1).value = arr[i]
        n += 1
        arr = yb.fetchone()
    wb.save(path + '.xlsx')
    yb.close()
    co.close()

• 导入到系统

这里用pandas生成了一个xlsx表，也可以自己创建一个有信息的表，这里呢并没有涉及到检测信息这些操作，所以要保证自己的数据是完成符合要求的，且工号不能重复【'0001','1'是两个工号，信息均是字符串】，生成的数据如下

info=[
    ['0102','一号','男','19980712','HQ'],
    ['6016','二号','女','19990417','CW'],
    ['0729','三号','男','18980418','IS']
]
import pandas as pd
df=pd.DataFrame(info,columns=['工号','姓名','性别','出生','职位'])
print(df)
topath = filedialog.asksaveasfilename(title='请选择要保存在位置,A目录下')#需要手写后缀
df.to_excel(topath,index=False)
print('文件生成完成')

 

 打开数据库->新建查询->访问rs表【use rsgldb;go;select * from rs;不同行我用分号隔开】执行

会展示已经有的数据

import openpyxl
import pyodbc
from tkinter import filedialog
def In():  # excel->sql
    path = filedialog.askopenfilename(title='请选择一个文件')#为选中文件的路径包含文件名
    print(path)
    path=path.replace('/','//')#将单斜杠转化为双斜杠
    wb = openpyxl.load_workbook(path)
    ws = wb.worksheets[0]
    rows = ws.max_row
    cols = ws.max_column
    # 连接数据库
    co = pyodbc.connect('DRIVER={sql server};SERVER=127.0.0.1;DATABASE=rsgldb')
    yb = co.cursor()
    for i in range(1, rows):
        values = []
        for j in range(cols):
            values.append(ws.cell(i+1, j+1).value)
        # print(values)
        sql = "insert rs values('{}','{}','{}','{}','{}')".format(values[0],values[1],values[2],values[3],values[4])
        # print(sql)
        yb.execute(sql)
        co.commit()  # 一定要提交，保证数据库数据同步，少了此句，数据库不会加入数据
    yb.close()
    co.close()
    print('excel->sql完成')

In()

如果报OSError: File contains no valid workbook part，【是说你用的是从xls表转化为xlsx表的，另启用一张xlsx表就解决了】【表在打开情况下也能执行】

其他模块的导入导出就不写了，参照前面的步骤，基本就八九不离十了

可以加上检测数据的函数，将检测数据拎出来写一个def，用来返回Ture OR False ，遇到不合规的数据，将数据放进list中并越过该行数据的导入