2020-09-15 插入数据oracle

# -*- coding:utf-8 -*-
from tkinter import *
import tkinter.filedialog
import pymysql.cursors
import cx_Oracle
import  uuid
# 测试俩列相同不
def test(x,y):
    if x==y:
        return
    else:
        return '修改'

def xz():
    filename1=tkinter.filedialog.askopenfilename()
    if filename1 != '':
         lb.config(text=u'您选择的文件是'+filename1)
         return  filename1
    else:
         lb.config(text=u'您没有选择任何文件')


def demo1(res):
    # print(11)
    with open(res, "r", encoding='UTF-8') as f:
    # with open(res) as f:  # 打开新的文本
            # text_new = f.read()
            # 读取整个sql文件，以分号切割。[:-1]删除最后一个元素，也就是空字符串
        # sql_list = f.read().split(';')[:-1]

        # res=f.read().replace('@|@',"','")
        # res1=res.replace('\n',"';'")
        # str1="'"+res1[0:len(res1)-1]
        #
        # sql_list=str1.split(';')[:-1]

        res = f.read().replace('@|@', "','")
            # print(res)
            # print(11)
        res1 = res.replace('\n', "';'")
        # print(res1)
        str1 = "'" + res1 + "'"
        sql_list = str1.split(';')
        sql_item=''
        num=1
        str1=''
        for i in range(0,len(sql_list)):

            if num<1000:
                if str1=='':
                    # rdi_corp_close_fin_acct_beij_20200607
                    tablename="sh_bos_org"
                    str1="insert into sh_bos_org (ID, CUST_ID,CUST_NAME,CUST_NAME_ABBR,CUST_NAME_EN,CUST_NAME_EN_ABBR ) "
                    # select '27', 'jack', '男', 22, '13345674567' from dual
                    # print(sql_list[i].split('VALUES')[1])
                    uuids = uuid.uuid4()
                    res=sql_list[i]
                    # select '27', 'jack', '男', 22, '13345674567' from dual
                    res1=' select '+"'"+str(uuids)+"'"+','+res+"  from dual  "
                    str1+=res1

                else:

                    res = sql_list[i]

                    uuids = uuid.uuid4()
                    #   union select '35', 'jack', '男', 22, '13345674567' from dual
                    res1 = 'union select  ' +"'"+str(uuids)+"'"+','+ res + "  from dual  "
                    str1+=res1
                    num += 1

            else:

                runsq(str1)
                num=1
                str1=""
                str1 = "insert into sh_bos_org (ID, CUST_ID,CUST_NAME,CUST_NAME_ABBR,CUST_NAME_EN,CUST_NAME_EN_ABBR ) "
                # select '27', 'jack', '男', 22, '13345674567' from dual
                # print(sql_list[i].split('VALUES')[1])
                uuids = uuid.uuid4()
                res = sql_list[i]

                # select '27', 'jack', '男', 22, '13345674567' from dual
                res1 = '  select ' + "'"+str(uuids)+"'"+','+ res + "  from dual  "
                str1 += res1


        runsq(str1)
        lb = Label(root, text='')
        lb.config(text=u'插入完毕')
        lb.pack()
def write_sql(sql):
    filename = 'sqlerror.txt'
    with open(filename, 'a') as file_object:
        file_object.write('-'*100)
        file_object.write(sql)
    file_object.close()





def runsq(sql):
    import os
    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'  # 或者os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'

    conn = cx_Oracle.connect('guest/12345*@127.0.0.1/XE')  # 用自己的实际数据库用户名、密码、主机ip地址 替换即可

    curs = conn.cursor()

    # print(22222222)
    # print(sql)

    # 执行sql语句
    print(sql)

    rr = curs.execute(sql)
    print(5656598)

    conn.commit()
        # 提交到数据库执行
    #
    # except:
    #     # 如果发生错误则回滚
    #     print(sql)
    #     print("执行错,打印到txt中")
    #     # write_sql(sql)
    # # 关闭数据库连接
    curs.close()

    conn.close()

root = Tk()
lb = Label(root,text='')
lb.pack()
btn=Button(root,text=u'选择第一个文件',command= xz)
btn.pack()

res=xz()

button1 = Button(root,text=u"下载", bg="lightblue", width=10,
                                      command=lambda :demo1(res))  # 调用内部方法  加()为直接调用
button1.pack()
root.mainloop()



'''
选择格式   其他自己写




        insert into Student(id, name, sex, age, tel)
select '27', 'jack', '男', 22, '13345674567' from dual

      union select '35', 'jack', '男', 22, '13345674567' from dual
  union select '36', 'jack', '男', 32, '13345674567' from dual
  union select '37', 'jack', '男', 22, '13345674567' from dual
  union select '38', 'jack', '男', 32, '13345674567' from dual
  union select '39', 'jack', '男', 22, '13345674567' from dual
  union select '40', 'jack', '男', 32, '13345674567' from dual
  union select '41', 'jack', '男', 22, '13345674567' from dual
  union select '42', 'jack', '男', 32, '13345674567' from dual

  union select '43', 'jack', '男', 22, '13345674567' from dual
  union select '44', 'jack', '男', 32, '13345674567' from dual
  union select '45', 'jack', '男', 22, '13345674567' from dual
  union select '46', 'jack', '男', 32, '13345674567' from dual

  union select '47', 'jack', '男', 22, '13345674567' from dual
  union select '48', 'jack', '男', 32, '13345674567' from dual
  union select '49', 'jack', '男', 22, '13345674567' from dual
  union select '50', 'jack', '男', 32, '13345674567' from dual

  union select '60', 'jack', '男', 32, '13366676667' from dual
  union select '61', 'jack', '男', 22, '13366676667' from dual
  union select '62', 'jack', '男', 32, '13366676667' from dual

  union select '63', 'jack', '男', 22, '13366676667' from dual
  union select '64', 'jack', '男', 32, '13366676667' from dual
  union select '65', 'jack', '男', 22, '13366676667' from dual
  union select '66', 'jack', '男', 32, '13366676667' from dual

  union select '67', 'jack', '男', 22, '13366676667' from dual
  union select '68', 'jack', '男', 32, '13366676667' from dual
  union select '69', 'jack', '男', 22, '13366676667' from dual

DELETE from Student



'''