class DBConnection:
db_file_name = "resource/Database/application_db.db"
in_memory_database = ":memory:"
sql_lite_url = db_file_name
error_logging = ErrorLogging()
_instance = None
def __new__(cls, *args, **kwargs):
if not cls._instance:
cls._instance = super(DBConnection,
cls).__new__(cls, *args, **kwargs)
return cls._instance
def set_in_memory(self):
self.sql_lite_url = self.in_memory_database
def create_connection(self):
connection = None
try:
connection = sqlite3.connect(self.sql_lite_url)
except sqlite3.Error as sql_exp:
self.error_logging.write_to_log(
"DBConnection.create_connection",
"An error occurred:" + sql_exp.args[0])
return connection
class DBConnection:
dbFileName = "resource/Database/applicationDB.db"
inMemoryDatabase = ":memory:"
sqlLiteUrl = dbFileName
errorLogging = ErrorLogging()
_instance = None
def __new__(cls, *args, **kwargs):
if not cls._instance:
cls._instance = super(DBConnection,
cls).__new__(cls, *args, **kwargs)
return cls._instance
def setInMemory(self):
self.sqlLiteUrl = self.inMemoryDatabase
def createConnection(self):
connection = None
try:
connection = sqlite3.connect(self.sqlLiteUrl)
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog("DBConnection.createConnection",
"An error occurred:" + sqlExp.args[0])
return connection
class CustomerDatabaseMapping:
errorLogging = ErrorLogging()
customerTableName = Customer.dataSourceName
emailAddressPosition = 0
firstNamePosition = 1
lastNamePosition = 2
passwordPosition = 3
dataSourceFields = ["emailAddress","firstName","lastName","password"]
customerFileReader = ReadCSVFile()
def setCustomerFileReader(self, readCSVFile):
self.customerFileReader = readCSVFile
def createCustomer(self, customerDetails):
customer = Customer(
customerDetails[self.emailAddressPosition],
customerDetails[self.firstNamePosition],
customerDetails[self.lastNamePosition],
customerDetails[self.passwordPosition]
)
return customer
def validateDataFromFileHeader(self, header):
return header == self.dataSourceFields
def getCustomerDataFromFile(self):
customerData = self.customerFileReader.getConfig(entitiesFolder,self.customerTableName + ".csv")
#if not self.validateDataFromFileHeader(header):
# self.errorLogging.writeToLog("CustomerDatabaseMapping.getCustomerDataFromFile","An error occurred:" + sqlExp.args[0])
return customerData
#def customerCreateTable(self):
# try:
# self.dbSetup.dropTable(self.customerTableName)
# self.dbSetup.createTable(self.customerTableName,self.dataSourceFields)
#except:
# self.errorLogging.writeToLog("CustomerDatabaseMapping.customerCreateTable","An error occurred:" + sys.exc_info()[0])
def getCustomerData(self):
return self.dataSource.getData(self.customerTableName,self.dataSourceFields)
def createAllCustomers(self):
allCustomers = []
allCustomerData = self.getCustomerData()
for customerRow in allCustomerData:
customer = self.createCustomer(customerRow)
allCustomers.append(customer)
return allCustomers
class DBConnection:
dbFileName = "resource/Database/applicationDB.db"
inMemoryDatabase = ":memory:"
sqlLiteUrl = dbFileName
errorLogging = ErrorLogging()
def setInMemory(self):
self.sqlLiteUrl = self.inMemoryDatabase
def createConnection(self):
connection = None
try:
connection = sqlite3.connect(self.sqlLiteUrl)
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog("DBConnection.createConnection",
"An error occurred:" + sqlExp.args[0])
return connection
class CustomerDatabaseMapping:
errorLogging = ErrorLogging()
customerTableName = Customer.dataSourceName
emailAddressPosition = 0
firstNamePosition = 1
lastNamePosition = 2
passwordPosition = 3
config = None
dataSourceFields = ["emailAddress", "firstName", "lastName", "password"]
dbSetup = DBSetup()
dataSource = DatabaseGetData()
def __init__(self, config=ConfigFromFile()):
self.config = config
def setConfig(self, config):
self.config = config
def createCustomer(self, customerDetails):
customer = Customer(customerDetails[self.emailAddressPosition],
customerDetails[self.firstNamePosition],
customerDetails[self.lastNamePosition],
customerDetails[self.passwordPosition])
return customer
def validateDataFromFileHeader(self, header):
return header == self.dataSourceFields
def getCustomerDataFromFile(self):
customerFileReader = ReadCSVFile()
customerData = customerFileReader.getFileData(
ENTITIES_FOLDER, self.customerTableName + ".csv")
header = customerData.pop(0)
if not self.validateDataFromFileHeader(header):
self.errorLogging.writeToLog(
"CustomerDatabaseMapping.getCustomerDataFromFile",
"An error occurred:" + sqlExp.args[0])
return customerData
def customerCreateTable(self):
try:
self.dbSetup.dropTable(self.customerTableName)
self.dbSetup.createTable(self.customerTableName,
self.dataSourceFields)
except:
self.errorLogging.writeToLog(
"CustomerDatabaseMapping.customerCreateTable",
"An error occurred:" + sys.exc_info()[0])
def customerPopulateDataSource(self):
customerInsertSql = self.dbSetup.generateInsertStatement(
self.customerTableName, self.dataSourceFields)
customerData = self.getCustomerDataFromFile()
try:
self.dbSetup.populateEntity(customerInsertSql, customerData)
except:
self.errorLogging.writeToLog(
"CustomerDatabaseMapping.customerCreateTable",
"An error occurred:" + sys.exc_info()[0])
def customerDataBaseSetup(self):
self.customerCreateTable()
self.customerPopulateDataSource()
def getCustomerData(self):
return self.dataSource.getData(self.customerTableName,
self.dataSourceFields)
def createAllCustomers(self):
allCustomers = []
allCustomerData = self.getCustomerData()
for customerRow in allCustomerData:
customer = self.createCustomer(customerRow)
allCustomers.append(customer)
return allCustomers
class DBExecuteSQL(object):
dbConnector = DBConnection()
errorLogging = ErrorLogging()
connection = None
_instance = None
def __new__(cls, *args, **kwargs):
if not cls._instance:
cls._instance = super(DBExecuteSQL,
cls).__new__(cls, *args, **kwargs)
return cls._instance
def setDbConnector(self, dbConnector):
self.dbConnector = dbConnector
self.connection = None
def getConnection(self):
if self.connection == None:
try:
self.connection = self.dbConnector.createConnection()
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog(
"DBExecuteSQL.getConnection",
"An error occurred:" + sqlExp.args[0])
return self.connection
def executeSQLCommand(self, sqlCommand):
try:
executeCursor = self.getConnection().cursor()
executeCursor.execute(sqlCommand)
executeCursor.close()
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog("DBExecuteSQL.executeSQLCommand",
"An error occurred:" + sqlExp.args[0])
self.errorLogging.writeToLog("DBExecuteSQL.executeSQLCommand",
"With command:" + sqlCommand)
raise
self.getConnection().commit()
def insertData(self, sqlCommand, dataRows):
insertDataCursor = self.getConnection().cursor()
try:
insertDataCursor.executemany(sqlCommand, dataRows)
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog("DBExecuteSQL.insertData",
"An error occurred:" + sqlExp.args[0])
insertDataCursor.close()
self.getConnection().commit()
def executeSqlSelect(self, selectCommand):
sqlData = []
try:
selectCursor = self.getConnection().cursor()
selectCursor.execute(selectCommand)
sqlData = selectCursor.fetchall()
except sqlite3.Error as sqlExp:
self.errorLogging.writeToLog("DBExecuteSQL.executeSqlSelect",
"An error occurred:" + sqlExp.args[0])
self.errorLogging.writeToLog("DBExecuteSQL.executeSqlSelect",
"With command:" + selectCommand)
raise
selectCursor.close()
return sqlData
def getListOfTables(self):
return self.executeSqlSelect(
"SELECT name FROM sqlite_master WHERE type='table';")
def switchToInMemory(self):
self.dbConnector.setInMemory()
self.connection = None
class DBSetup:
db_execute_sql = DBExecuteSQL()
error_logging = ErrorLogging()
def generate_drop_table(self, table_name):
return "DROP TABLE IF EXISTS " + table_name
def drop_table(self, table_name):
sql_drop_table = self.generate_drop_table(table_name)
try:
self.db_execute_sql.execute_sql_command(sql_drop_table)
except sqlite3.Error as sql_exp:
self.error_logging.write_to_log(
"DBSetup.create_table", "An error occurred:" + sql_exp.args[0])
raise
def generate_create_table_statement(self, table_name, field_names):
sql_create_table = "CREATE TABLE IF NOT EXISTS " + table_name + "(\n"
counter = 0
field_unique = False
for column_name in field_names:
if counter == 0:
field_unique = True
else:
sql_create_table += ","
sql_create_table += column_name + " TEXT NOT NULL "
if field_unique:
sql_create_table += " UNIQUE "
counter += 1
field_unique = False
sql_create_table += ");"
return sql_create_table
def create_table(self, table_name, field_names):
sql_create_table = self.generate_create_table_statement(
table_name, field_names)
try:
self.db_execute_sql.execute_sql_command(sql_create_table)
except sqlite3.Error as sql_exp:
self.error_logging.write_to_log(
"DBSetup.create_table", "An error occurred:" + sql_exp.args[0])
raise
def generate_insert_statement(self, table_name, field_names):
counter = 0
sql_columns = ""
sql_values = ""
sql_unique_column = ""
sql_insert = ""
for column_name in field_names:
if counter == 0:
sql_unique_column = column_name
else:
sql_columns += ", "
sql_values += ", "
sql_columns += column_name
sql_values += "?"
counter += 1
sql_insert = "INSERT INTO " + table_name + "(" + sql_columns + ") VALUES(" + sql_values + ") \n"
return sql_insert
def populate_entity(self, sql_command, data_rows):
try:
self.db_execute_sql.insert_data(sql_command, data_rows)
except sqlite3.Error as sql_exp:
self.error_logging.write_to_log(
"DBSetup.populate_entity",
"An error occurred:" + sql_exp.args[0])
raise
class CustomerDatabaseMapping:
error_logging = ErrorLogging()
customer_table_name = Customer.data_source_name
email_address_position = 0
first_name_position = 1
last_name_position = 2
password_position = 3
data_source_fields = [
"email_address", "first_name", "last_name", "password"
]
db_setup = DBSetup()
data_source = DatabaseGetData()
def create_customer(self, customer_details):
customer = Customer(customer_details[self.email_address_position],
customer_details[self.first_name_position],
customer_details[self.last_name_position],
customer_details[self.password_position])
return customer
def validate_data_from_file_header(self, header):
return header == self.data_source_fields
def get_customer_data_from_file(self):
customer_file_reader = ReadCSVFile()
customer_data = customer_file_reader.get_file_data(
ENTITIES_FOLDER, self.customer_table_name + ".csv")
header = customer_data.pop(0)
if not self.validate_data_from_file_header(header):
print("CustomerDatabaseMapping.get_customer_data_from_file",
"Not validate_data_from_file_header")
return customer_data
def customer_create_table(self):
try:
self.db_setup.drop_table(self.customer_table_name)
self.db_setup.create_table(self.customer_table_name,
self.data_source_fields)
except:
print("CustomerDatabaseMapping.customer_create_table",
"An error occurred:")
def customer_populate_data_source(self):
customer_insert_sql = self.db_setup.generate_insert_statement(
self.customer_table_name, self.data_source_fields)
customer_data = self.get_customer_data_from_file()
try:
self.db_setup.populate_entity(customer_insert_sql, customer_data)
except:
print("CustomerDatabaseMapping.customer_create_table",
"An error occurred:")
def customer_data_base_setup(self):
self.customer_create_table()
self.customer_populate_data_source()
def get_customer_data(self):
return self.data_source.get_data(self.customer_table_name,
self.data_source_fields)
def create_all_customers(self):
all_customers = []
all_customer_data = self.get_customer_data()
for customer_row in all_customer_data:
customer = self.create_customer(customer_row)
all_customers.append(customer)
return all_customers