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()
import os.path
import shutil
import time
import json
import csv
from classes.database_access import DB_Connect
my_db = DB_Connect('root', '', 'python_projects')
def import_file():
"""removes bad characters from customer_export.txt and then writes output to a new csv file"""
temp_output = ""
with open("week8-10final/text_files/customer_export.txt") as text_file:
data = text_file.read()
data = data.replace("#", "")
data = data.replace("|", ",")
temp_output = temp_output + data
with open("week8-10final/docs/customer_export.txt",
"w") as file_output:
file_output.write(temp_output)
"""csv backup area. will create backups if the file already exists """
if os.path.isfile("week8-10final/docs/customers.csv"):
shutil.copy2(
"week8-10final/docs/customers.csv",
"week8-10final/docs/customers.csv.backup" + str(time.time()))
with open("week8-10final/docs/customers.csv", "w") as file_output:
file_output.write(temp_output)
"""reads customer_export.txt, and formats the data into a json format """
with open('week8-10final/docs/customer_export.txt', 'r') as csvfile:
filereader = csv.reader(csvfile, delimiter=',')
import pymysql, io, json, csv, re, time, os.path, datetime, sys
from functions.functionLibrary import *
from classes.database_access import DB_Connect
from classes.clsDataSet import *
#DATABASE CONNECTION
my_db = DB_Connect('root', '', 'python_projects')
print("Welcome to the Customer Collection")
appRunning = True
while appRunning:
#GETS THE ACTION SELECTED BY THE USER"""
actionType = getAction()
#PRINTS THE SELECTED ACTION TO THE SCREEN FOR VERIFICATION
print(actionType.upper() + " IS NOW RUNNING!")
#LOGIC TO EXECUTE USERS DESIRED ACTION TYPE
if str(actionType).upper() == "IMPORT A NEW DATA FILE":
"""IMPORTS THE NEW FILE INTO THE DATABASES SERVICE"""
importFile = True
while importFile:
print("PLEASE SELECT THE NEW FILE")
#GETS THE DATA FILE FROM A OPEN FILE DIALOG
dataFile = getDataFile()
#CHECKS TO SEE IF FILE IS A TXT FILE
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])
import sys
import pymysql
from functions.funtionLibrary import *
from classes.database_access import DB_Connect
from classes.clsBook import *
my_db = DB_Connect('root', '', 'python_projects')
print("Welcome To The Library Database!\n")
hasCompleted = False
while not hasCompleted:
actionType = getAction()
print(actionType)
if actionType == "Show All Books":
"""Show All Book is selected"""
sqlStrSelect = selectAllSQLStr()
results = my_db.executeSelectQuery(sqlStrSelect)
count = len(results)
if count > 0:
for book in results:
print("--------------------------------")
for item in book:
print(str(item).upper() + " : " + str(book[item]))
else:
print("No books are in the database.")
continue