search

blackary / st_pages

An experimental version of Streamlit Multi-Page Apps
MIT License
471 stars 77 forks source link

Data uploading problem in postgresql using python script. #27

Closed Priyanka260895 closed 1 year ago

Priyanka260895 commented 1 year ago

I have one folder where continuous raw files are generated and my program takes these files from that folder reads it and uploads it into postgre sql and then moves into another folder but when i click on stop button files are moved into another folder. In postgre sql table is created but there is no data in table. What I want is when i click on stop button iteration should be stopped and data should be uploaded into postgre sql table. i am using below code for this. If there is any solution it would be great help.

Import Module

from tkinter import import tkinter as tk import tkinter.font as font import os import psycopg2 import numpy as np import pandas as pd import glob from Enter_Details_ver2 import from tkinter.filedialog import askdirectory from tkinter import filedialog import shutil import time from PIL import ImageTk, Image from tkinter import messagebox, ttk from tkinter.ttk import Combobox import psycopg2.extras as extras from New_table import from tkinter import import tkinter as tk from time import sleep import schedule from urllib.parse import quote_plus from datetime import datetime import IMCtermite import json import datetime import io from io import StringIO

root=Tk() root.configure(bg='#BFBFBF')

setting the windows size

root.geometry("765x550")

setting the title of window

root.title("UPLOADING STATUS")

path = source() dest_path = destination()

New file dictionary

newfilesDictionary = {}

hostname= host() dbname=DB() portID= Port_ID() username=user() password=pw() filetype = fileType() listdata = [] postgres_str = f'postgresql://{username}:%s@{hostname}:{portID}/{dbname}' % quote_plus(password) print(postgres_str)

conn_string = "host=%s dbname=%s user=%s password=%s port=%s" % (hostname, dbname, username, password,portID) conn = psycopg2.connect(conn_string) cursor = conn.cursor()

event = None disconnect_flag = False

cnx = create_engine(postgres_str)

table= table_name()

def add_trigger_time(trigger_time, add_time) : trgts = datetime.datetime.strptime(trigger_time,'%Y-%m-%dT%H:%M:%S') dt = datetime.timedelta(seconds=add_time) return (trgts + dt).strftime('%Y-%m-%dT%H:%M:%S')

def create_table():

if (filetype == ".csv"):
    files=os.path.join(path + "/**/", "*.csv")
    files = glob.glob(files)
    if any(".csv" in s for s in files):
        df = pd.concat(map(pd.read_csv,files),ignore_index=True)
        df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                      .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                      .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_")
                      for x in df.columns]
        df.columns = df.columns.str.lower()
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        df.to_sql(table, con=cnx, index=False)
        print("Done")
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)

        for roote, dirs, files_ in os.walk(path):
            for dire in dirs:
                try:
                    shutil.move(path + "/"+ dire, dest_path)
                except Exception as e:
                    print(e)
                    pass

        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files moved to destination folder successfully\n" %dtime)
        root.after(30000, start_append)

    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Execution Completed: No more csv files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more csv files to cocatenate")
        start['state'] = tk.DISABLED

elif (filetype == ".xlsx"):
    files=os.path.join(path + "/**/", "*.xls")
    files = glob.glob(files)
    if any(".xls" in s for s in files):
        df = pd.concat(map(pd.read_excel,files),ignore_index=True)
        df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                      .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                      .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_")
                      for x in df.columns]
        df.columns = df.columns.str.lower()
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        df.to_sql(table, con=cnx, index=False)
        print("Done")
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)

        for roote, dirs, files_ in os.walk(path):
            for dire in dirs:
                try:
                    shutil.move(path + "/"+ dire, dest_path)
                except Exception as e:
                    print(e)
                    pass

        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files moved to destination folder successfully\n" %dtime)
        root.after(30000, start_append)
    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Execution Completed: No more excel files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more excel files to cocatenate")
        start['state'] = tk.DISABLED

elif (filetype == ".raw"):
    files=os.path.join(path + "/**/", "*.raw")
    files = glob.glob(files)

print(files)

latest_file = max(files, key = os.path.getctime)

print(latest_file)

files.remove(latest_file)

print(files)

    if any(".raw" in s for s in files):
        for file in files:
            try:
                os.rename(file,file)
            except PermissionError as e:
                print(e)
                files.remove(file)

            imcraw = IMCtermite.imctermite(file.encode('utf-8'))
            #channels = imcraw.get_channels(False) 
            chns = imcraw.get_channels(True)
            xcol = "Datetime"
            xsts = [add_trigger_time(chns[0]['trigger-time'],tm) for tm in chns[0]['xdata']]
            chnnms = sorted([chn['name'] for chn in chns], reverse=False)
            chnsdict = {}
            for chn in chns :
                chnsdict[chn["name"]] = chn
            df = pd.DataFrame()
            df[xcol] = pd.Series(xsts)
            for chnnm in chnnms :
                chn = chnsdict[chnnm]
                ycol = chn['yname']+" ["+chn['yunit']+"]"
                df[ycol] = pd.Series(chn['ydata'])
                df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                              .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                              .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_").replace("[","_")
                              .replace("]","_" ).replace("(", "_").replace(")", "_").replace('"',"") for x in df.columns]
                df.columns = df.columns.str.lower()
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        try:
            df.to_sql(table, con=cnx, index=False)
            cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE TEXT USING CAST(datetime AS TEXT);"%table)
            conn.commit()
            query = "select * from %s" %table

            print(query)
        except ValueError as e:
            tk.messagebox.showerror("Error","Table name: %s already exists in the database. Please change the name of the table." %table)

        #cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE TEXT USING CAST(datetime AS TEXT);"%table)
        #conn.commit()
        #cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE timestamp USING to_timestamp(datetime, 'YYYY-MM-DD T HH24:MI:SS');" %table)
        #conn.commit()
        print("Uploaded the files: Done")
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)

        for roote, dirs, files_ in os.walk(path):
            for dire in dirs:
                try:
                    t = os.path.getctime(path + "/" + dire)
                    c_time = time.ctime(t)
                    t_obj = time.strptime(c_time)
                    T_stamp = time.strftime("%Y-%m-%d-%H-%M-%S", t_obj)
                    os.rename(path + "/" + dire, path + "/" +  T_stamp)
                    shutil.move(path + "/"+ T_stamp, dest_path)
                except Exception as e:
                    print(e)
                    #root.after(500, start_append)

        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Files moved to destination folder successfully\n" %dtime)

        root.after(30000, start_append)

    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        #cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE TEXT USING CAST(datetime AS TEXT);"%table)
        #cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE timestamp USING to_timestamp(datetime, 'YYYY-MM-DD T HH24:MI:SS');" %table)
        #conn.commit()
        Connection_status.insert(tk.END,"%s :Execution Completed: No more raw files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more raw files to cocatenate")
        start['state'] = tk.DISABLED

def start_append():

if (filetype == ".csv"):
    files=os.path.join(path + "/**/", "*.csv")
    files = glob.glob(files)
    if any(".csv" in s for s in files):
        df = pd.concat(map(pd.read_csv,files),ignore_index=True)
        df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                      .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                      .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_")
                      for x in df.columns]
        df.columns = df.columns.str.lower()
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        tuples = [tuple(x) for x in df.to_numpy()]
        cols = ','.join(list(df.columns))
        query="INSERT INTO %s(%s) VALUES %%s" % (table, cols)
        try:
            extras.execute_values(cursor, query, tuples)
            conn.commit()
        except (Exception, psycopg2.DatabaseError) as error:
            print("Error: %s" % error)
            tk.messagebox.showerror("Error","ERROR: %s" %error)
            conn.rollback() 
        else:
            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)
            for roote, dirs, files_ in os.walk(path):
                for dire in dirs:
                    try:
                        shutil.move(path + "/"+ dire, dest_path)
                    except Exception as e:
                        print(e)
                        pass

            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files shifted to destination folder successfully\n" %dtime)

        root.after(30000, start_append)

    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Execution Completed: No more csv files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more csv files to cocatenate")
        start['state'] = tk.DISABLED

elif (filetype == ".xlsx"):
    files=os.path.join(path + "/**/", "*.xls")
    files = glob.glob(files)
    if any(".xls" in s for s in files):
        df = pd.concat(map(pd.read_excel,files),ignore_index=True)
        df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                      .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                      .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_")
                      .replace('"',"")
                      for x in df.columns]
        df.columns = df.columns.str.lower()

        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        tuples = [tuple(x) for x in df.to_numpy()]
        cols = ','.join(list(df.columns))
        query="INSERT INTO %s(%s) VALUES %%s" % (table, cols)
        try:
            extras.execute_values(cursor, query, tuples)
            conn.commit()
        except (Exception, psycopg2.DatabaseError) as error:
            print("Error: %s" % error)
            tk.messagebox.showerror("Error","ERROR: %s" %error)
            conn.rollback() 
        else:
            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)
            for roote, dirs, files_ in os.walk(path):
                for dire in dirs:
                    try:
                        shutil.move(path + "/"+ dire, dest_path)
                    except Exception as e:
                        print(e)
                        pass

            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files shifted to destination folder successfully\n" %dtime)        

        root.after(30000, start_append)

    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"%s :Execution Completed: No more excel files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more excel files to cocatenate")
        start['state'] = tk.DISABLED

elif (filetype == ".raw"):
    files=os.path.join(path + "/**/", "*.raw")
    files = glob.glob(files)

print(files)

latest_file = max(files, key = os.path.getctime)

print(latest_file)

files.remove(latest_file)

print(files)

    if any(".raw" in s for s in files):
        for file in files:
            try:
                os.rename(file,file)
            except PermissionError as e:
                print(e)
                files.remove(file)

            imcraw = IMCtermite.imctermite(file.encode('utf-8'))
            #channels = imcraw.get_channels(False) 
            chns = imcraw.get_channels(True)
            xcol = "Datetime"
            xsts = [add_trigger_time(chns[0]['trigger-time'],tm) for tm in chns[0]['xdata']]
            chnnms = sorted([chn['name'] for chn in chns], reverse=False)
            chnsdict = {}
            for chn in chns :
                chnsdict[chn["name"]] = chn
            df = pd.DataFrame()
            df[xcol] = pd.Series(xsts)
            for chnnm in chnnms :
                chn = chnsdict[chnnm]
                ycol = chn['yname']+" ["+chn['yunit']+"]"
                df[ycol] = pd.Series(chn['ydata'])
                df.columns = [x.replace(" ", "_").replace("-","_").replace(r"/","_").replace("\\","_").replace(".","_")
                              .replace("$","_").replace("%","_").replace(":","_").replace("!","_").replace("@","_")
                              .replace("^","_").replace("&","_").replace(";","_").replace("*","_").replace("#","_").replace("[","_")
                              .replace("]","_" ).replace("(", "_").replace(")", "_")
                              .replace('"',"") for x in df.columns]
                df.columns = df.columns.str.lower()
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        Connection_status.insert(tk.END,"{} :Appending to table: {}\n".format(dtime, table))
        tuples = [tuple(x) for x in df.to_numpy()]
        cols = ','.join(list(df.columns))
        query="INSERT INTO %s(%s) VALUES %%s" % (table, cols)
        conn.set_session(autocommit=True)
        try:
            extras.execute_values(cursor, query, tuples)
            conn.commit()
        except (Exception, psycopg2.DatabaseError) as error:
            print("Error: %s" % error)
            tk.messagebox.showerror("Error","ERROR: %s" %error)
            conn.rollback() 
        else:
            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files inserted successfully\n" %dtime)
            for roote, dirs, files_ in os.walk(path):
                for dire in dirs:
                    try:
                        t = os.path.getctime(path + "/" + dire)
                        c_time = time.ctime(t)
                        t_obj = time.strptime(c_time)
                        T_stamp = time.strftime("%Y-%m-%d-%H-%M-%S", t_obj)
                        os.rename(path + "/" + dire, path + "/" +  T_stamp)
                        shutil.move(path + "/"+ T_stamp, dest_path)
                    except Exception as e:
                        print(e)
                        #root.after(500, start_append)

            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            Connection_status.insert(tk.END,"%s :Files shifted to destination folder successfully\n" %dtime)

        if disconnect_flag == False:
            root.after(30000, start_append)
        else:
            dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE TEXT USING CAST(datetime AS TEXT);"%table)

            cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE timestamp USING to_timestamp(datetime, 'YYYY-MM-DD T HH24:MI:SS');" %table)
            conn.commit()
            Connection_status.insert(tk.END,"%s :Execution Completed: No more raw files to cocatenate" %dtime)
            tk.messagebox.showinfo("Completed","Execution Completed: No more raw files to cocatenate")
            ##start['state'] = tk.DISABLED

        #root.after(30000, start_append)   

    else:
        dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE TEXT USING CAST(datetime AS TEXT);"%table)

        cursor.execute("ALTER TABLE %s ALTER COLUMN datetime TYPE timestamp USING to_timestamp(datetime, 'YYYY-MM-DD T HH24:MI:SS');" %table)
        conn.commit()
        Connection_status.insert(tk.END,"%s :Execution Completed: No more raw files to cocatenate" %dtime)
        tk.messagebox.showinfo("Completed","Execution Completed: No more raw files to cocatenate")
        ##start['state'] = tk.DISABLED

def disconnect(): global disconnect_flag

confirm=tk.messagebox.askquestion("Confirmation","Are you sure you want to disconnect")
if confirm == 'yes' :
    disconnect_flag = True
    dtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    Connection_status.insert(tk.END,"%s :Connection disconnected" %dtime)

else:
    root.after(50000, start_append)

def go_back():

root.destroy()

import ask_table_creation

Connection_status = Text(root, height = 18, width = 70,bg='#EEECE1')

Conn_label = Label(root, text = "Uploading Status", bg='#BFBFBF')

Connection_status.grid(row=2, column=1, pady=10, ipadx='10', ipady='10') Conn_label.grid(row=1,column=1, ipadx="10",pady='10',ipady="10",padx='5')

start = Button( root, text='Start', bg = '#17375E', fg = 'White', command = create_table ) start.grid(row=0, column=1, pady=10, ipadx='20', ipady='10', sticky='W')

disconn = Button( root, text='STOP', bg = '#17375E', fg = 'White', command = disconnect ) disconn.grid(row=3, column=2, pady=10, ipadx='20', ipady='10', sticky='W')

back = Button( root, text='BACK', bg = '#17375E', fg = 'White', command = go_back )

back.grid(row=3, column=0, pady=10, ipadx='20', ipady='10', sticky='SE')

Priyanka260895 commented 1 year ago

anyone can help me it would be great help.