【测试工具】将excel表数据一键导入Oracle数据库生成数据表

背景介绍：

        之前工作中经常会需要做一些数据核对的相关工作，业务方给过来的都是一些excel数据表，面对庞大的数据通过excel进行比对，工作量大且繁杂，如何解决这一问题呢？

解决办法：

        将数据导入数据库，自动生成数据表，通过数据库SQL自动化比对，并实现sql的自动化输出结果

代码实现：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @FileName  :importdata.py
# @Author    :段小王爷
# @Time      :2021-06-01 21:12


from tkinter import *
from tkinter import ttk
import tkinter.messagebox as mb
from tkinter.filedialog import askopenfilename
import cx_Oracle
import os
import xlrd


def login_db():
    db = None
    cr = None
    db_info = {"用户名": ['密码', 'ip:1528/test']}  
    username = select_db.get()  # 获取下拉框选择的数据库
    if username == '':
        mb.showwarning(message='请选择数据库')
    elif username not in db_info.keys():
        mb.showwarning( message='数据库未配置' )
    else:
        password = db_info[username][0]
        database = db_info[username][1]
        try:
            db = cx_Oracle.connect( username, password, database )
            cr = db.cursor()
        except Exception as e:
            mb.showwarning( message=str( e ) )
    return db, cr


def import_data_button():
    db, cr = login_db()
    path = var_path.get()
    if path == '':
        mb.showwarning( message='请输入文件路径' )
    else:
        path, xls_name = os.path.split( path )
        shortname, extension = os.path.splitext( xls_name )
        try:
            os.chdir( path )
            os.path.isfile( xls_name )
            workbooks = xlrd.open_workbook( xls_name )
            insert_data( db, cr, shortname, workbooks )
        except Exception as e:
            mb.showwarning( message='import_data_button' )


def select_file_path():
    path = askopenfilename( filetypes=[('xlsmfile', 'xls')] )
    var_path.set( path )


def insert_data(db, cr, shortname, workbooks):
    tablename = 'Temp_test' + shortname
    try:
        oracle_sql = 'drop table' + tablename
        cr.executr( oracle_sql )
    except Exception as e:
        mb.showwarning( message=str( e ).encode( 'utf-8' ) )
    for sheet in workbooks.sheets():
        param = []
        sheetname = sheet.name
        sheet = workbooks.sheet_by_name( sheetname )
        rows = sheet.nrows
        print( rows )
        ncols = sheet.ncols
        print( ncols )
        b = ''
        c = ''
        for i in range( 1, ncols + 1 ):
            insert_table = ':' + str( i ) + ','
            b = b + insert_table
            create_table_sql = 'Filed' + str( i ) + '  varchar2(500),'
            c = c + create_table_sql
        create_table_sql = c[:-1]
        create_table_sql = 'create  table ' + tablename + '(' + create_table_sql + ')'
        print( create_table_sql )
        cr.execute( create_table_sql )
        insert_table = b[:-1]

        if rows > 1:
            for i in range( rows - 1 ):
                single_row = []
                for j in range( ncols ):
                    cell_values = sheet.cell_value( i + 1, j )
                    cell_values = str( cell_values )
                    single_row.append( cell_values )
                param.append( single_row )
        sql = "insert into " + tablename + "values(" + insert_table + ")"
        try:
            cr.executemany( sql, param )
            mb.showwarning( message='数据导入成功' + tablename )
        except Exception as e:
            mb.showwarning( message=str( e ).encode( 'utf-8' ) )
        finally:
            db.commit()
    cr.close()
    db.close()


if __name__ == '__main__':
    root = Tk()
    var_path = StringVar()
    root.title( '报表数据导入' )
    sw = root.winfo_screenwidth()
    sh = root.winfo_screenheight()
    ww = 710
    wh = 430
    x = (sw - ww) / 2
    y = (sh - wh) / 2
    root.geometry( "%dx%d+%d+%d" % (ww, wh, x, y) )

    Label( root, text=r"使用说明:" ).grid( row=0, column=0, sticky=W )
    Label( root, text=r"             1.选择需要导入表的数据库:" ).grid( row=1, column=0, sticky=W )
    Label( root, text=r"             2.选择excel数据文件:" ).grid( row=2, column=0, sticky=W )
    Label( root, text=r"             3.工具存放路径:" ).grid( row=3, column=0, sticky=W )
    Label( root, text=r"             4.有问题请联系段龙志:" ).grid( row=4, column=0, sticky=W )
    Label( root, text=r"请选择数据库:" ).grid( row=5, column=0, columnspan=6, sticky=W )
    Label( root, text=r"请选择文件:" ).grid( row=6, column=0, columnspan=6, sticky=W )

    select_db = ttk.Combobox( root )
    select_db.grid( row=5, column=1 )
    select_db['value'] = ('fm')  # 数据库下拉选项
    select_db.configure( state='readonly' )

    enter_path = Entry( root, show=None, textvariable=var_path, font=('Arial', 12) )
    enter_path.grid( row=6, column=1 )
    Button( root, text='请选择文件', command=select_file_path ).grid( row=6, column=2, columnspan=6, sticky=W )

    btn_insert = Button( root, text='导入数据', bg="Gray", command=import_data_button )
    btn_insert.grid( row=7, column=1 )

    root.mainloop()
    import_data_button()
    root.destroy()