As requested by Professor Iyad Koteich, now the application saves the data on the SQLite database.
In the first post (here) we did a python program to join three spreadsheets in just one. This work involves knowledge of pandas to organize the data.
Now we create a database and a table to store all data. This version is so simple and does not check the user store option.
I'll create a new project on GitHub because I'll show how to merge these steps in one project. (god to understand git commands line)
GitHub: https://github.com/ed2ti/exercise04
[CODE]
# *************************** #
# College: Trebas Institute
# Professor: Iyad Koteich
# Class: Edward
# Day: 2022-10-13
# *************************** #
#Importing Libres
## Pandas ##
import pandas as pd
from pandas import DataFrame
## sqlite3 ##
import sqlite3
from sqlite3 import Error
###
### INFORMATIONS FROM Concordia ###
###
##Loading Data
concordia = pd.read_excel('Concordia.xlsx')
## Informing that this informations are form Concordia
concordia["College"] = 'Concordia'
#show Concordia Result
print(concordia.head())
print('n')
###
### INFORMATIONS FROM McGill ###
###
##Loading Data
mcgill = pd.read_excel('McGill.xlsx')
## Informing that this informations are form McGill
mcgill["College"] = 'McGill'
## Organizing the DataFrame
mcgill.rename(columns={'id': 'Student_Code'}, inplace = True)
mcgill.rename(columns={'name': 'Full_Name'}, inplace = True)
mcgill.rename(columns={'Course': 'Program'}, inplace = True)
mcgill.rename(columns={'country': 'Nationality'}, inplace = True)
mcgill.drop(columns=['city'], inplace = True)
#show McGill Result
print(mcgill.head())
print('n')
###
### INFORMATIONS FROM Trebas ###
###
##Loading Data And Print The Colums
trebas = pd.read_excel('TREBAS.xlsx')
## Informing that this informations are form TREBAS
trebas["College"] = 'TREBAS'
## Organizing the DataFrame
trebas.rename(columns={'Country': 'Nationality'}, inplace = True)
trebas.rename(columns={'Student_ID': 'Student_Code'}, inplace = True)
trebas["Full_Name"] = trebas["First_Name"] + " " + trebas["Last_Name"]
trebas.drop(columns=['City'], inplace = True)
trebas.drop(columns=['First_Name'], inplace = True)
trebas.drop(columns=['Last_Name'], inplace = True)
#Ajusting the sequence of the columns
trebas = trebas[["Student_Code","Full_Name","Nationality","Program", "College"]]
#show Trebas Result
print(trebas.head())
print('n')
final = pd.concat([trebas, concordia,mcgill], ignore_index=True, sort=False)
print(final)
# Exporting to excel (to_excel)
final.to_excel ('final.xlsx', index = True, header=True)
##
## Try to conect database (memory)
## This database is on the Memory (no file) for a best performance.
## If you want, you can change :memory: for a name file.
##
#target = ':memory:';
target = 'ex-04.db';
conn = None;
try:
conn = sqlite3.connect(target)
print("connect on Database sqlite3 "+sqlite3.version)
cur = conn.cursor()
except Error as e:
print(e)
# Create a final table (IF NOT EXISTS)#
## id = AUTOINCREMENT ##
cur.execute("CREATE TABLE IF NOT EXISTS final(id INTEGER PRIMARY KEY AUTOINCREMENT, Student_Code TEXT, Full_Name TEXT, Nationality TEXT, Program TEXT, College TEXT)")
## Trucate the final table ##
cur.execute("DELETE FROM final")
#
data = final.values
#print(len(data))
for i in range(len(data)):
#datal = Data Line
datal = data[i]
# writing on Database
cur.execute("INSERT INTO final(Student_Code, Full_Name, Nationality, Program, College) VALUES (?,?,?,?,?)", datal)
conn.commit()
conn.close()
