I am trying to make a GUI application using tkinter

Samy Moustafa 0
Viewed 55

I want to make two main function first import from Excel file(.xlsx) to my application and save the imported data to mysql database , second function is to export the file to excel file (.xlsx) after doing some other things to every record

My main problem is:

  1. I want to export and import *.xlsx extension *not .csv.
  2. I want to export seperate columns with headings.
  3. I dont want to export my data to file of data seperate by comma.
    Edit: what after I import xlsxwriter. Note : I don't want to import from mysql I want to import to mysql.

Here is my code:

'''

#import all what I need:
import tkinter as tk 
from tkinter import * 
from tkinter import ttk
from tkinter import messagebox, filedialog
import mysql.connector as mysql 
import csv
import os
import pandas as pd

# define mydatabase:
db = mysql.connect( host = "localhost", user = "root", passwd = "S@mY2829", database="sample", auth_plugin="mysql_native_password")
cursor = db.cursor()  

#Global variable:
mydata = []
root = Tk()

#define my fuctions:
#1
def update(records):
  global mydata
  mydata = records
  trv.delete(*trv.get_children())
  for i in records:
     trv.insert('', 'end', values=i)

#2
def clear():
  query = "SELECT * FROM demo"
  cursor.execute(query)
  records = cursor.fetchall()
  update(records)

#3
def import_():
  fln = filedialog.askopenfilename(initialdir=os.getcwd(), title="Open Window", filetypes=[('ALL Files', '*.xlsx *.xlsm *.sxc *.ods *.csv *.tsv')])
  with open(fln) as myfile:
     csvread = csv.reader(myfile, delimiter=',')
     for i in csvread:
         mydata.append(i)
  update(mydata)
  for i in mydata:
     uid = i[0]
     fname = i[1]
     lname = i[2]
     age = i[3]
     date = i[4]
     query = "INSERT INTO demo(id, first_name, last_name, age, date) VALUES(NULL, %s, %s, %s, NOW())"
     cursor.execute(query, (fname, lname, age))
  db.commit()
  clear()

#4
def export_():
  if len(mydata) < 1:
     messagebox.showerror("Error Window", "No data avaliable to export")
     return False
  fln = filedialog.asksaveasfilename(initialdir=os.getcwd(), title="Save Window", filetypes=(("CSV File", "*.csv"),("XLSX File","*.xlsx")))
  with open(fln, mode='w', newline='') as myfile:
     exp_writer = csv.writer(myfile, delimiter=',')
     for i in mydata:
         exp_writer.writerow(i)
  messagebox.showinfo("Data Exported", "Your data has been exported to "+os.path.basename(fln)+" successfully.")


#section my window:
section1 = LabelFrame(root, text="Customer List Section")
section1.pack(fill="both", expand="yes", padx=20, pady=10)


# define my treeview:
trv= ttk.Treeview(section1, columns=(1,2,3,4,5), show="headings", height="6")
trv.pack()
trv.heading(1, text="ID")
trv.heading(2, text="First Name")
trv.heading(3, text="Last Name")
trv.heading(4, text="Age")
trv.heading(5, text="Date")

#Display the columns in database:
query = "SELECT * from demo" # demo is name of the table
cursor.execute(query)
records = cursor.fetchall()
update(records) 

#make the buttons:
expbtn = Button(section1, text="Export File", command=export_)
expbtn.pack(side=tk.LEFT, padx=10, pady=10)

impbtn = Button(section1, text="Import File", command=import_)
impbtn.pack(side=tk.LEFT, padx=10, pady=10)



root.title("Demo")
root.geometry("2000x1000")
root.mainloop()

'''

python
2 Answers

If you don't want csv, you don't want the csv module. Use pip to install the xlsxwriter module and use that instead.

Mark Reed0

I'd suggest to use sqlalchemy and openpyxl with pandas, they are much more simple. Here's how the code which loads data could look lie:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql://root:S@mY2829@localhost/sample')
mydata = pd.read_sql('SELECT * FROM demo', engine)

Then, you could use mydata.itertuples() or mydata.todict() to populate the screen form.

Saving to Excel is that simple too: mydata.to_excel(filename, index=False). Note that openpyxl package has to be installed as it is been used under the hood.

kol0
Related