def db_processor():
#db commit area
my_db = DB_Connect('root', '', 'python_projects')
if os.path.isfile("text_files/exam_data.csv"):
#clear all data in my_db
my_db.executeQuery("TRUNCATE TABLE grade_data")
with open("text_files/exam_data.csv", encoding="utf-8") as csvfile:
csv_data = csv.reader(csvfile)
#skip first row
next(csv_data)
#insert all data in the database
for row in csv_data:
insert_data_statement = (
'INSERT INTO grade_data (level_of_education,test_preparation,math_score,reading_score,writing_score) VALUES (\"'
+ str(row[0]) + " \",\"" + str(row[1]) + "\" ,\" " +
str(row[2]) + "\",\"" + str(row[3]) + " \",\"" +
str(row[4]) + "\");")
my_db.executeQuery(insert_data_statement)
my_db.conn.commit()
#takes current date and time
time_now = datetime.datetime.now()
#open log in text_files, and writes to it in the format listed
log_open = open("text_files/script_log.txt", "a")
log_open.write(
time_now.strftime("%b %d %Y %H:%M:%S") +
" - Database Import Processing is Complete! \n")
log_open.close()
def create_table():
my_db = DB_Connect('root', '', 'python_projects')
#create grade_data table if it doesn't already exist
try:
my_db.executeQuery("SELECT * FROM grade_data")
except:
my_db.executeQuery(
"CREATE TABLE grade_data (grade_ID INT AUTO_INCREMENT PRIMARY KEY, level_of_education VARCHAR(30), test_preparation VARCHAR(20), math_score VARCHAR(5), reading_score VARCHAR(5), writing_score VARCHAR(5))"
)
my_db.conn.commit()
#GETS THE DATA FILE FROM A OPEN FILE DIALOG
dataFile = getDataFile()
#CHECKS TO SEE IF FILE IS A TXT FILE
if not dataFile == False and dataFile.endswith( ".txt"):
try:
#PARSES THE SELECTED FILE AND CHECKS FOR DUPLICATES
newData = validateData(dataFile)
#BACKS UP THE FILE IN THE SAME FILE DIRECTORY
backUpFile(dataFile)
#CONVERTS THE FILE TO JSON
newData.convert_json()
#CONVERTS THE FILE TO CSV
newData.convert_csv()
#TRUNCATES THE DATABASE TABLES
my_db.executeQuery('TRUNCATE TABLE crm_data')
my_db.executeQuery('TRUNCATE TABLE mailings')
#IMPORT THE DATA INTO THE DATABASE CONTROLLER
serviceController('textfiles\customers.json')
print("CRM and Mailings Databases Records have been loaded successfully!")
break
except Exception as error:
print(error)
print("The import process failed. Please try this step again.")
break
else:
print("The file for the import process is not accepted. Please upload a valid .txt file!")
break
if str(actionType).upper() == "ADD A RECORD":
"""ADDS A NEW RECORD TO THE SELECTED DATABASE SERVICE"""
from bs4 import BeautifulSoup
from classes.database_access import DB_Connect
import pymysql
import os.path
import csv
import json
import unicodedata
my_db = DB_Connect('root', '', 'python_projects')
try:
my_db.executeQuery("SELECT * FROM customer_data")
except:
my_db.executeQuery(
"CREATE TABLE customer_data (customer_ID INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(30), last_name VARCHAR(30), street VARCHAR(50), city VARCHAR(30), state CHAR(5), zip VARCHAR(5))"
)
my_db.conn.commit()
my_db.executeQuery(
"CREATE TABLE customer_data_working (customer_ID INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(30), last_name VARCHAR(30), street VARCHAR(50), city VARCHAR(30), state CHAR(5), zip VARCHAR(5))"
)
my_db.conn.commit()
file_name_incorrect = False
while not file_name_incorrect:
initial_question = input(
"What is the filename of the file you would like to import to the database? Type it exactly as it is named! If you don't want to import a file type quit. "
)
file_extens = initial_question.split(".")
file_extension_string = repr(file_extens[-1])
numPurchased = input(
"Please enter the number of the books purchased: ")
if hasNumbers(numPurchased) == True:
break
else:
print("Invalid Number Purchased")
my_book = Book(bookAuthor,
ISBN,
numPurchased,
bookTitle,
numCheckedOut=0,
retailPrice=0)
strSQLAddBook = buildInsertBookSQL(bookAuthor, str(ISBN), bookTitle, 0,
numPurchased)
my_db.executeQuery(strSQLAddBook)
print("Book Added Successfully")
if actionType == "Edit a book":
"""Edit a books value in the database"""
isDone = False
while not isDone:
bookNameSTR = ""
bookNameSTR = input(
"What is the name of the book that you would like to edit? ")
if is_null(bookNameSTR) == False:
strSql = selectBookSQLStr(bookNameSTR)
bookVal = ""
bookVal = my_db.executeSelectQuery(strSql)
if len(bookVal) == 1:
for item in bookVal: