Python & Pandas & SQLite

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()