python 将Excel导入到oracle

有的时候我们需要把execl 等其他类型文件导入到数据库中，一般我们会使用一些工具自带的导入工具来实现，但是有的时候使用起来还是不方便，能不能自己写个脚本实现这个功能呢，下面直接上代码：

使用语音：python

实现功能：指定数据库连接信息->指定要导入的文件路径->指定要导入的表->动创建表->将execl文件导入到表里面

具体展示效果如下图所示:

下面附上python的代码：

import pandas as pd
import cx_Oracle
import xlrd2
 
if __name__ == "__main__":
    try:
        db_url = input("db_url:")
        conn = cx_Oracle.connect(db_url, encoding="UTF-8", nencoding="UTF-8")
        cursor = conn.cursor()
        file_name = input("file_name:")
        table_name = input("table_name:")
        insert_line = 0
        workbook = xlrd2.open_workbook(file_name)
        booksheet = workbook.sheet_by_index(0)
        nrows = booksheet.nrows  # 行数
        ncols = booksheet.ncols  # 列数
        # 动态生成表
        createSql = "create table %s(\n" % table_name
        cols = "values("
        # values = "% ("
        for x in range(ncols):
            createSql += "x%s varchar2(1000)" % x
            # values += "row_data[%s]" % x
            cols += "'%s'"
            if x < ncols - 1:
                createSql += ",\n"
                # values += ",\n"
                cols += ",\n"
            else:
                createSql += "\n)"
                # values += "\n )"
                cols += "\n)"
        # 创建表
        sql = "select count(*) num from user_tables where  lower(table_name)='%s'" % table_name
        cursor.execute(sql)
        tableCount = cursor.fetchone()
        if tableCount[0] > 0:
            print("表已存在")
        else:
            print("开始创建表")
            cursor.execute(createSql)
        for i in range(0, nrows):
            row_data = booksheet.row_values(i)
            if row_data:
                inSql = "insert into %s \n" % table_name
                values = "values ("
                for x in range(ncols):
                    values += "'%s'" % str(row_data[x])
                    if x < ncols - 1:
                        values += ",\n"
                    else:
                        values += "\n )"
                inSql += values
                log = "已插入:%s行" % i
                print(log)
                cursor.execute(inSql)
 
        conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(e)
        cursor.close()
        conn.close()