Continue the previous article: python cx_oracle operation database (1): import excle table into database

Because of the recent use of cx_oracle, tkinter, pyinstaller, socket modules to make a project to implement the interface query operation of the database through the exe program, so it has not been updated.

Step by step

Step 1: Use cx_oracle to query the database.

#-*- coding: utf-8 -*-
import cx_Oracle as cx
import pandas as pd
from datetime import *
import os
import calendar
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
class fbrw:
    def __init__(self,p_name,ks_sj,js_sj):
        self.ks_sj = ks_sj
        self.js_sj = js_sj
        self.p_name = p_name
        self.sz_sj = datetime.strptime(self.ks_sj, '%Y-%m-%d')
        od = timedelta(days=1)
        if self.sz_sj.weekday() != calendar.SUNDAY:
            while self.sz_sj.weekday() != calendar.SUNDAY:
                self.sz_sj -= od
        else:
            self.sz_sj = self.sz_sj - timedelta(days=7)
        self.sz = self.sz_sj.strftime('%Y-%m-%d')
        #The parameter configuration is mainly used for time parameter and stored procedure name parameter. If the SQL statement is not written as a stored procedure, cursor.execute(sql statement) is directly used to execute the query statement.
    def qushuju(self):
        con = cx.connect('User name/Password@data base IP/Database name')
        cur = con.cursor()
        cur.callproc(self.p_name,[self.ks_sj,self.js_sj,self.sz])
        print('Stored procedure complete!')
        cur.execute('select * from {}'.format(self.p_name.split('_')[1]))
        data = cur.fetchall()
        print('Get the data to complete!')
        cur.close()
        con.close()
        fm =pd.DataFrame(data,columns= ['Custom Column 1','Custom Column 2','...','...',...)
        fm.to_excel('{}data.xlsx'.format(self.ks_sj),index=False,encoding='utf-8')
        print('Data saving completed!')#Write excle using pandas
         

The second step is to use tkinter to program the interface and socket to verify the login.

#-*- coding: utf8 -*-
from tkinter import *
from tkinter import messagebox
import time
import os
import Custom Query Module 1
#import custom query module 2
#import custom query module 3
#import custom query module 4
#import custom query module 5
#import custom query module 6
from socket import *
class Excchuli:
    def __init__(self):
        self.clie = socket()
        self.clie.connect(('scoket The server IP', 5050))#The port number is self-defined, but the client and the server need to be consistent.
    def my_gui(self):
        self.dlui = Tk()
        self.dlui.title('Login window')
        self.dlui.geometry('500x300+20+20')
        ti = Label(self.dlui, text='Welcome to login')
        ti.grid(row=0, column=4, columnspan=3, rowspan=2)
        # ti.pack(side = 'top')
        ti.config(font=('Arial', '25'))
        fram = Frame(self.dlui)
        fram.grid(row=3, column=1, rowspan=3, columnspan=3)
        aj_text = Label(fram, text='User name:')
        aj_text.grid(row=0, column=0)
        var_usr_name = StringVar()
        var_usr_name.set('abc')
        self.aj = Entry(fram,textvariable=var_usr_name)
        self.aj.grid(row=0, column=1, columnspan=2)
        pa_text = Label(fram, text='Password:')
        pa_text.grid(row=1, column=0)
        self.pa = Entry(fram)
        self.pa.grid(row=1, column=1, columnspan=2)
        an = Button(fram, text='register')
        an.grid(row=2, column=1)
        an1 = Button(fram, text='Sign in', command=self.dl_dl)
        an1.grid(row=2, column=2)
        an2 = Button(fram, text='Sign out', command=self.dlui.quit)
        an2.grid(row=2, column=0)
        self.dlui.mainloop()
    def huoqunp(self):
        self.name = self.aj.get()
        self.pas = self.pa.get()

    def dl_dl(self):
        self.huoqunp()
        while True:
            self.clie.sendall(bytes(str(self.name+','+self.pas), encoding='utf-8'))
            # clie.sendall(bytes(str(self.pas), encoding='utf-8'))
            ret_b = self.clie.recv(1024)
            ret_s = str(ret_b, encoding='utf-8')
            if ret_s == 'fase':
                messagebox.showinfo(message='User name does not exist!')
                break
            elif ret_s == 'yes':

                messagebox.showinfo(message='Log in successfully!')
                self.set_zdy_window()
                break
            else:
                messagebox.showinfo(message='Account or password error, please re-enter!')
                break

    def proc(self):
        mag = 'Query may take a long time, please wait patiently!'
        messagebox.showinfo(title='Prompt message', message=mag)
        self.dc_sj = self.ks_sjcs.get().strip()
        self.dc_sj1 = self.js_sjcs.get().strip()
        gc = Custom Module 1.fbrw('p_xxxx',self.dc_sj,self.dc_sj1)
        gc.qushuju()
        data = 'Completed!'
        self.in_wenben.delete(1.0, END)
        self.in_wenben.insert(1.0, data)
        messagebox.showinfo(title='complete',message='Task data has been extracted and saved in the same path!')
    #def chaxun1(self):
    #    pass
    def xiaoxi(self):
        mag = 'Query may take a long time, please wait patiently!'
        messagebox.showinfo(title='Prompt message', message=mag)
    def dakai(self):
        lj = os.path.abspath('.')
        os.system(r'explorer.exe /n,{}'.format(lj))#Read the location of the file where the program is located and open it
    def set_zdy_window(self):
        self.dlui.destroy()
        self.zdy_window_name = Tk()
        self.zdy_window_name.title('Extract data, author: xxx')
        self.zdy_window_name.geometry('1086x700+10+10')
        self.zdy_window_name.attributes("-alpha",0.9)

        self.in_label = Label(self.zdy_window_name,text = 'Partial data:',fg = 'red')
        self.in_label.place(x =20 ,y =35  )
        self.in_label1 = Label(self.zdy_window_name, text='Since the data is extracted directly from the database, the speed may be a little slow! Please wait patiently....The execution process is displayed on the command line', fg='black',font = '4')
        self.in_label1.pack(side = 'bottom',anchor ='w')
        self.caidan = Menu(self.zdy_window_name)
        self.caidan2 = Menu(self.caidan,tearoff = 0,fg = 'red')
        self.caidan2.add_command(label = 'Tips',command =self.xiaoxi)
        self.caidan2.add_command(label = 'open',command = self.dakai)
        self.caidan.add_cascade(label = 'file',menu = self.caidan2)
        self.zdy_window_name.config(menu = self.caidan)
        self.in_wenben = Text(self.zdy_window_name,width = 143,height = 25)
        self.in_wenben.place(x =40,y = 55)
        #self.in_wenben.config(font = ('Arial',8))
        self.sca =Scrollbar(self.zdy_window_name)
        # self.sca.pack(side = RIGHT)
        self.sca.config(command = self.in_wenben.yview)
        self.in_wenben.config(yscrollcommand = self.sca.set)
        #self.sca.pack(side = RIGHT,ipady = 250)
        self.sca.place(x = 1045,y = 57,height = 320)
        self.in_canshuzhi = Text(self.zdy_window_name, width=143, height=3)
        self.in_canshuzhi.place(x=40, y=420)
        self.canshu = Label(self.zdy_window_name,text = 'Other parameters: ps:Format such as: Zhang San,Li Si')
        self.canshu.place(x = 20,y = 395)
        self.canshu1 = Label(self.zdy_window_name, text='Start time:')
        self.canshu1.place(x=20, y=470)
        self.ks_sjcs = Entry(self.zdy_window_name,width =25)
        self.ks_sjcs.place(x=80,y = 470)
        self.canshu2 = Label(self.zdy_window_name, text='Ending time:')
        self.canshu2.place(x=270, y=470)
        self.js_sjcs = Entry(self.zdy_window_name,width =25)
        self.js_sjcs.place(x=330,y = 470)
        self.qingk = Button(self.zdy_window_name, text='empty', command=self.qingkong)
        self.qingk.place(x=1046, y=28)
        self.in_canshuzhi2 = Text(self.zdy_window_name, width=100, height=8)
        self.in_canshuzhi2.place(x=40, y=525)
        #self.canshu = Label(self.zdy_window_name,text='query process:')
        #self.canshu.place(x=20, y=500)

        self.diaoyu = Button(self.zdy_window_name,text = 'XXXX data',width = 10,height = 1,command =self.dayin,bg = 'violet')
        self.diaoyu.place(x = 10 , y = 5)
        self.renwu = Button(self.zdy_window_name, text='YYYY data', width=10, height=1, bg='hotpink',command = self.proc)
        self.renwu.place(x=350, y=5)
        self.zdy_window_name.mainloop()


    def canshuhuoqu(self):
        pass
        # self.src = self.in_canshuzhi.get(1.0, END).strip().replace("\n", "").encode()
    def queren(self):
        messagebox.showinfo(title='In operation',message = 'Data is being extracted, please wait a moment!')
    def qingkong(self):
        self.in_wenben.delete(1.0,END)


if __name__=='__main__':
    ck = Excchuli()
    ck.my_gui()

Step 3: socket server.

import socketserver

host ='The server IP'
port = 5050#port

ddicc ={
    'abccc':'12345','fffee':'f123456','dddfwq':'df123'
}
class MyHandler(socketserver.BaseRequestHandler):
    def handle(self):
        while True:
            data = self.request.recv(1024)

            # data1 =json.loads(data)
            data1 = str(data).split('\'')[1].split(',')[0]
            data2 = str(data).split('\'')[1].split(',')[1]
            if data1 not in ddicc.keys():
                self.request.sendall(bytes(str('fase'), encoding='utf-8'))
            elif data1 in ddicc.keys():
                if data2 == ddicc[data1]:
                    # re_data = str(data,encoding='utf-8')
                    # conn.sendall(bytes(str('tru'),encoding = 'utf-8'))
                    # if da1 == ddicc[data1]:
                    self.request.sendall(bytes(str('yes'), encoding='utf-8'))
                else:
                    self.request.sendall(bytes(str('no'), encoding='utf-8'))

s = socketserver.ThreadingTCPServer((host,port),MyHandler)
s.serve_forever()

 

Step 3: Pack the program with pyinstaller.

First install pyinstaller, pip install pyinstaller directly, and then enter pyinstaller path + program name. py on the command line. This packing process tramples a lot of holes. 1. When packing, it always reports an error when hook-zmq.py:

'UTF-8' codec can't decode byte0xce in position 136:invalid continuation .....................................:

I found a lot of ways on the Internet, and even deleted hook-zmq.py. Although this method succeeded, I always thought there was a more perfect way. Later, I found that I could use chcp 65001 (input on the command line). Although I don't know why, I did solve the problem.

https://blog.csdn.net/qq_35203425/article/details/80992870

2. The second pit is that the customized module will not be packaged when it is packaged, and many methods have been tried, including modifying and adding the introduction path, and finally using spec to configure it to solve the problem.

# -*- mode: python -*-

block_cipher = None


a = Analysis(['shuju.py',
              'E:\\pa\\xxxx Custom Module 1.py',
              'E:\\pa\\xxxx Custom Module 2.py',
              'xxx.py
              ],#Introducing Custom Modules
             pathex=['E:\\pa'],#Route
             binaries=[],
             datas=[],
             hiddenimports=['pandas','cx_Oracle','calendar','datetime','os','tkinter','time','socket''tkinter.messagebox'],#python module needed
             hookspath=[],
             runtime_hooks=[],
             excludes=[],
             win_no_prefer_redirects=False,
             win_private_assemblies=False,
             cipher=block_cipher,
             noarchive=False)
pyz = PYZ(a.pure, a.zipped_data,
             cipher=block_cipher)
exe = EXE(pyz,
          a.scripts,
          [],
          exclude_binaries=True,
          name='shujutiqu',
          debug=False,
          bootloader_ignore_signals=False,
          strip=False,
          upx=True,
          console=True )
coll = COLLECT(exe,
               a.binaries,
               a.zipfiles,
               a.datas,
               strip=False,
               upx=True,
               name='shuju')

https://www.cnblogs.com/yemeng/p/6253097.html

3. The third pit is about cx_oracle error reporting: cx_oracle.DatabaseError:DPI-1047:64-bit oracle client library cannot be loaded: "...............".

Best of all, it turns out that pyinstaller did not package the 64-bit oracle client's dll at the time of packaging.

Solution: Manually copy and paste these DLLs into the program folder.

http://jvniu.jb51.net:81/201708/tools/instantclientx64_jb51.rar Download, then copy and paste several dll files.

OK!

Basically, this is such a process, because it involves security information, part of the code has been deleted. If you have any questions, you can leave a message, or add QQ573377138 to study together and exchange!

UI interface screenshot:

 

Finally, when I sent the program to other computers, I found another pit...

That is to point out that the execution program can not connect to the server side of my computer. The solution is:

Client access was denied because of the windows firewall.

Setting reference: https://blog.csdn.net/dasunwarman/article/details/78595678

 

Learn to communicate and share, so that more people less stepped on the pit I have trampled, do not like spraying... Do not spray uuuuuuuuu Thank you very much for pointing out the problems that have not been done well.