def create(self, token):
try:
if token is None:
raise Error('user is None')
return False
if token.value == '' or token.date is None:
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error DAOToken create: {}'.format(e))
return False
self.cursor = self.connection.cursor()
datatoken = (token.value, token.date)
sql = """ INSERT INTO {} (value, date) VALUES(%s,%s);""".format(
self.table)
try:
self.cursor.execute(sql, datatoken)
except Error as e:
print('Error DAOToken create: {}'.format(e))
return False
#self.connection.commit()
return True
def update(self, user):
try:
if user is None:
raise Error('user is None')
return False
if user.email == '' or user.password == '':
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error DAOUser update: {}'.format(e))
return False
cursor = self.connection.cursor()
dataUser = (user.email, user.password, user.token.id, user.id)
sql = """ UPDATE {} SET email=%s, password=%s, idtoken=%s WHERE idusers=%s;""".format(
self.table)
try:
cursor.execute(sql, dataUser)
if cursor.rowcount == 0:
raise Error(' no found row, no updated')
#print('Error: no found row, no updated')
return False
except Error as e:
print('Error DAOUser update: {}'.format(e))
return False
return True
def update(self, token):
try:
if token is None:
raise Error('token is None')
return False
if token.value == '' or token.date is None:
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error DAOToken update: {}'.format(e))
return False
cursor = self.connection.cursor()
datatoken = (token.value, token.date)
sql = """ UPDATE {} SET value=%s, date=%s WHERE idtokens='{}';""".format(
self.table, token.id)
try:
cursor.execute(sql, datatoken)
if cursor.rowcount == 0:
raise Error('no found row, no updated3')
#print('Error: no found row, no updated')
return False
except Error as e:
print('Error DAOToken update: {}'.format(e))
return False
return True
def test_batch_total():
pass
# Initialize, create batch and load JE's into journal_loader for batch
# Initialize tables
table_list = ['journal_loader',
'journal_batch',
]
delete_status = query_initialize_000(table_list)
if delete_status[0] != 0:
raise Error('Table initialization failed at test_batch_total()')
# Create a batch
# Load journal_batch with sample data dictionary from test_queries
for _ in test_sample_batch10:
insert_new_batch_name(**_)
# Get row id for batch
# Use batch name from the sample batch loaded to get the batch row id.
journal_batch_name = test_sample_batch10[0]['journal_batch_name']
batch_row_id = get_journal_batch_row_id_by_name(journal_batch_name)
if batch_row_id[1] != 'OK':
raise Error('*** ERROR: test_batch_total not finding name')
# Load sample data into batch
# Set up csv file to use
filename = 'csv_out01.csv'
table = 'journal_loader'
print(f'>>>> batch_row_id out: {batch_row_id}')
batch_row_id = batch_row_id[0][0][0]
# batch_row_id = batch_row_id[0][0][0] <<< use to test malformed query
# Load csv file to journal_loader
load_file = batch_load_je_file(filename, str(batch_row_id))
if load_file == 'LOAD OKAY':
status_ = 1
else:
status_ = 99
assert(status_ == 1)
# Call function to test
journal_txt, journal_DR_total, journal_CR_total = batch_total(table, batch_row_id)
# Load csv file inro pandas dataframe
df = pd.read_csv(working_data_folder + filename)
print(df.head())
print(f'batch_row_id: {batch_row_id}')
# Get DR/CR totals in dataframe
df_dr_total = df['journal_debit'].sum()
df_cr_total = df['journal_credit'].sum()
# Compare sample data to function result set
assert(round(df_dr_total, 2) == round(journal_DR_total, 2))
assert(round(df_cr_total, 2) == round(journal_CR_total, 2))
def insert_tr(self, currency, tr_list):
alarm_list = []
try:
cursor = self.conn.cursor()
sql_base = """INSERT IGNORE INTO tr_coinone(currency, price, qty, date_time)
VALUES {}
"""
sql_bulk_list = []
for tr in tr_list:
sql_data = "( {}, {}, {}, '{}' )".format(
currency, tr['price'], tr['qty'], tr['datetime'])
sql_bulk_list.append(sql_data)
sql_bulk = ", ".join(sql_bulk_list)
sql = sql_base.format(sql_bulk)
cursor.execute(sql)
self.conn.commit()
except Error as e:
self.conn.rollback()
logger.error(e)
raise Error(msg=e)
finally:
cursor.close()
return alarm_list
def close(self):
if (not self.connection.is_connected()):
raise Error("No connection available.")
self.cursor.close()
self.connection.close()
print("MySQL connection is closed")
def test_log_mysql_error():
try:
raise Error(msg='test message', errno=100, sqlstate='test sqlstate')
except Error as e:
result = log_mysql_error(e.msg, e.errno, e.sqlstate)
print(f"\n{result}")
assert isinstance(result, dict)
def updateAverageRatings():
average_rating_query = """update movie set average_rating=(ROUND(((imdb_rating+meta_rating+rt_rating)/3),2)) where not isnull(imdb_rating) and not isnull(meta_rating) and not isnull(rt_rating)"""
try:
sqlConnect()
cursor.execute(average_rating_query)
sqlCommit()
except mysql.connector.Error as error:
raise Error("SQL Error")
def sqlQueries(movies):
try:
sqlConnect()
sqlDelete()
sqlInsert(movies)
sqlCommit()
except mysql.connector.Error as error:
raise Error("SQL Error")
def create(self, user):
# generate token
try:
if user is None:
raise Error('user is None')
return False
mT = DAOManagerMysql()
if not mT.do(mT.TOKEN, mT.CREATE, user.token):
raise Error('no create token')
return False
#mT.create(user.token)
idToken = mT.do(mT.TOKEN, mT.LAST_ID_TOKEN)
except Error as e:
print('Error DAOUser create 1: {}'.format(e))
return False
try:
if user.email == '' or user.password == '':
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error DAOUser create 2: {}'.format(e))
return False
cursor = self.connection.cursor()
dataUser = (
user.email,
user.password,
idToken,
)
sql = """ INSERT INTO {} (email, password, idtoken) VALUES(%s,%s,%s);""".format(
self.table)
try:
cursor.execute(sql, dataUser)
except Error as e:
print('Error DAOUser create 3: {}'.format(e))
return False
#self.connection.commit()
return True
def __init__(self, host, database, user, password,port):
self.setMySQLConnection(host, database, user, password, port)
try:
# self.conn.config(**self.ConnectionStringArgs)
self.conn = MySQLConnection(**self.ConnectionStringArgs)
self.cursor = self.conn.cursor()
except Error as err:
raise Error("Error Message : " + str(err.msg))
def __init__(self, host: str, username: str, password: str):
self.__APP_DATABASE_NAME = "LDMS"
self.__USER_TABLE_NAME = "USERS"
try:
# Try connecting to the database
self._db_connection = mysql.connector.connect(host=host,
username=username,
password=password)
if self._db_connection.is_connected():
# get server information and store it.
self._db_info = self._db_connection.get_server_info()
self._db_cursor = self._db_connection.cursor()
# Obtain all database names in the server
self._db_cursor.execute("SHOW DATABASES")
all_dbs = self._db_cursor.fetchall()
print(f"All database: {all_dbs}")
# check to see if we can find our application database in the MYSQL database
found = False
for db_tup in all_dbs:
# print(f"Searching app. Testing {db_tup[0]}")
if db_tup[0] == self.__APP_DATABASE_NAME:
# print(f"Found our app: {db_tup[0]}")
found = True
break
# If we didn't find it, we create it.
if not found:
self._db_cursor.reset()
# create a new database, since OURs wasn't existing
self._db_cursor.execute(
f"CREATE DATABASE {self.__APP_DATABASE_NAME}")
# setup application to use this our newly created database, as the applications
# working database.
self._db_cursor.reset()
self._db_cursor.execute(f"USE {self.__APP_DATABASE_NAME}")
self._db_cursor.reset()
self._db_cursor.execute(f"SHOW TABLES")
tables = self._db_cursor.fetchall()
# If there are not tables in the database, we start creating them, else we just continue
if len(tables) == 0:
# Create a new USERS table in the our new database to reflect the USERS of our application.
self._db_cursor.reset()
self._db_cursor.execute(
f"CREATE TABLE {self.__USER_TABLE_NAME} (UserID int, Useranme varchar(255), Password varchar(255))"
)
else:
raise Error(msg="NOT_CONNECTED_TO_DB")
except Error as error:
raise error
def deleteDuplicateMovies():
delete_query = """delete from movie where (title,year) in (select * from (select a.title,a.year from movie as a, movie as b where a.title=b.title and a.year=b.year+1) as t);"""
try:
sqlConnect()
cursor.execute(delete_query)
print(delete_query)
sqlCommit()
except mysql.connector.Error as error:
raise Error("SQL Error")
def deleteBadEntries():
delete_query = """delete from movie where average_rating is null;"""
try:
sqlConnect()
cursor.execute(delete_query)
print(delete_query)
sqlCommit()
except mysql.connector.Error as error:
raise Error("SQL Error")
def create_conn_pool(self):
try:
self.connection_pool = mysql.connector.pooling.MySQLConnectionPool(
pool_name=self.pool_name,
pool_size=self.pool_size,
user=self.user,
password=self.password,
port=self.port,
host=self.host,
database=self.database)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
raise Error(
"Something is wrong with your Username and Password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
raise Error("Database does not exist")
else:
raise Error(err)
return self.connection_pool
def main():
device = AtlasI2C(
) # creates the I2C port object, specify the address or bus if necessary
#we should already have our address set here.
outputStr = device.query("R")
if (outputStr.find("Command succeeded") == -1):
print "ERROR: I2C read failed. Aborting"
sys.exit(2)
#Otherwise, it worked. Lets get the values. We assume EC,PPM and Salinity have been set manually with i2c.py
#those settings should persist, even if the power is interrupted.
commaStr = outputStr[((outputStr.find("ded")) + 4):]
numList = commaStr.split(",") #order should be EC, PPM, SALT
#print str(numList)
device.close(
) #should probably close the file pointers; don't want a hung state at some low level I can't debug easily.
#Now lets get our databaes request set up:
dbdate = getTime()
#print str(dbtime)
try:
query = "INSERT INTO ecppmsalt(date,cond,tds,salt) VALUES(%s,%s,%s,%s)"
args = (dbdate, numList[0], numList[1], numList[2])
conn = mysql.connector.connect(host="localhost",
database="datalogger",
user="******",
password="******")
cursor = conn.cursor()
if (not conn.is_connected()):
raise Error("Connection not made, aborting.")
cursor.execute(query, args)
if (not cursor.lastrowid):
raise Error("No last insertion ID; database insert failed.")
conn.commit()
except Error as e:
print e
sys.exit(2)
finally:
cursor.close()
conn.close()
def copyUrls():
query = """UPDATE movie SET imdb_url = imdbUrl WHERE imdbUrl IS NOT NULL;
UPDATE movie SET meta_url = metaUrl WHERE metaUrl IS NOT NULL;
UPDATE movie SET rt_url = rtUrl WHERE rtUrl IS NOT NULL;"""
try:
sqlConnect()
cursor.execute(query, multi=True)
print(query)
sqlCommit()
except mysql.connector.Error as error:
raise Error("SQL Error")
def test_select_batch_by_row_id():
# Initialize journal_batch table before inserting sample rows
table_list = ['journal_batch']
delete_status = query_initialize_000(table_list)
if delete_status[0] != 0:
raise Error('Table initialization failed at test_select_batch_by_row_id()')
# Load journal_batch with sample data dictionary from test_queries
for _ in test_sample_batches:
insert_new_batch_name(**_)
# Use an arbitrary batch name from the sample batches loaded to get the
# batch row id.
journal_batch_name = test_sample_batches[3]['journal_batch_name']
batch_row_id = get_journal_batch_row_id_by_name(journal_batch_name)
if batch_row_id[1] != 'OK':
raise Error('*** ERROR: test_select_batch_by_row_id not finding name')
# Put contents of test sample batch from dict into a list for later assert
test_sample_batch_to_check = list()
test_sample_batch_to_check.append(batch_row_id[0][0][0])
for key in test_sample_batches[3]:
test_sample_batch_to_check.append(test_sample_batches[3][key])
# Convert entity, currency & batch status to int
test_sample_batch_to_check[3] = int(test_sample_batch_to_check[3])
test_sample_batch_to_check[4] = int(test_sample_batch_to_check[4])
test_sample_batch_to_check[6] = int(test_sample_batch_to_check[6])
# Call select_batch_by_row_id and store result set
table = 'journal_batch'
journal_batch_row_id = batch_row_id[0][0][0]
function_result = select_batch_by_row_id(table, journal_batch_row_id)
# Compare sample data to function result set
assert(test_sample_batch_to_check == list(function_result[0]))
def update_employee(employee):
"""
Updates an existing entry in the Employee Table
Exception Raising:
raises DataLayerError exception.
"""
if employee == None:
raise DataLayerError(message="Employee Required")
if not isinstance(employee, Employee):
raise DataLayerError(
f"Found type {type(employee)}, required type <class 'Employee'>"
)
if employee.has_exceptions:
raise DataLayerError(exceptions=employee.exceptions)
if employee.emp_id == 0:
raise DataLayerError(
"Employee ID must not be assigned zero, it must already exist."
)
try:
connection = DBConnection.getConnection()
cursor = connection.cursor()
cursor.execute("select emp_id from employee where emp_id=%s",
(employee.emp_id, ))
rows = cursor.fetchall()
if len(rows) != 1:
raise DataLayerError(
message=f"{employee.emp_id} does not exists")
cursor.execute(
"update employee set name=%s, designation_code=%s, DOB=%s, salary=%s, gender=%s, is_indian=%s, pan_no=%s, aadhar_no=%s where emp_id=%s",
(employee.name,
employee.designation_code, employee.dob, employee.salary,
employee.gender.capitalize(), employee.indian,
employee.pan_no, employee.aadhar, employee.emp_id))
cursor.execute("select name from employee where emp_id=%s",
(employee.emp_id, ))
updated_data = cursor.fetchall()
connection.commit()
if len(updated_data) != 1 or (updated_data[0][0] != employee.name):
raise Error(
"Updation failed due to unknown interrupt, please try again"
)
except Error as err:
raise DataLayerError(message=err.msg)
finally:
try:
if cursor.is_open():
cursor.close()
if connection.is_connected():
connection.close()
except:
pass
def endTransaction(self, state):
cursor = self.conecction.cursor()
try:
if not state:
raise Error('failled transaction')
sql = """COMMIT;"""
cursor.execute(sql)
return True
except Error as e:
print('Error: {}'.format(e))
sql = """ROLLBACK;"""
cursor.execute(sql)
return False
def delete_designation(code):
"""
Removes an existing entry from the Designation Table
Exception Raising:
raises DataLayerError exception.
"""
if code == None:
raise DataLayerError(message="Designation Code Required")
if not isinstance(code, int):
raise DataLayerError(
f"Found type {type(code)}, required type {type(0)}")
if code <= 0:
raise DataLayerError(f"Invalid entry for code : {code}")
try:
connection = DBConnection.getConnection()
cursor = connection.cursor()
cursor.execute(
"select emp_id from employee where designation_code=%s",
(code, ))
rows = cursor.fetchall()
if len(rows) != 0:
raise DataLayerError(
message=
f"The designation code : {code}, cannot be deleted as employees exist against it"
)
cursor.execute("select code from designation where code=%s",
(code, ))
rows = cursor.fetchall()
if len(rows) != 1:
raise DataLayerError(message=f"Code : {code} does not exists")
cursor.execute("delete from designation where code=%s", (code, ))
cursor.execute("select * from designation where code=%s", (code, ))
updated_data = cursor.fetchall()
connection.commit()
if len(updated_data) != 0:
raise Error()
except Error:
raise DataLayerError(
message=
"Deletion failed due to unknown interrupt, please try again")
finally:
try:
if cursor.is_open():
cursor.close()
if connection.is_connected():
connection.close()
except:
pass
def ExecuteStoredProcedure(self, ProcedureName, in_args=None):
try:
if not self.conn.is_connected():
self.conn._open_connection()
self.cursor = self.conn.cursor()
Out_args = self.cursor.callproc(ProcedureName, in_args)
self.conn.commit()
except Error as err:
raise Error('ERROR MESSAGE: ' + str(err.msg))
finally:
self.cursor.close()
self.conn.close()
return Out_args
def withdraw(self, users_id, account, balance, description, createdAt, updatedAt, connection, cursor):
try:
balance = str(float(account[4]) - balance)
sql_update_query = """UPDATE accounts AS a SET a.balance = '%s' WHERE a.id = '%s' """ % (balance, account[0])
cursor.execute(sql_update_query)
connection.commit()
return True
except Error as e:
raise Error("Withdraw failure in Account dao!")
print(e.value)
logging.error('e.value')
def update_designation(designation):
"""
Updates an existing entry in the Designation Table
Exception Raising:
raises DataLayerError exception.
"""
if designation == None:
raise DataLayerError(message="Designation Required")
if not isinstance(designation, Designation):
raise DataLayerError(
f"Found type {type(designation)}, required type <class 'Designation'>"
)
if designation.has_exceptions:
raise DataLayerError(exceptions=designation.exceptions)
if designation.code <= 0:
raise DataLayerError(
"Designation Code must not be zero, as it is the primary key.")
try:
connection = DBConnection.getConnection()
cursor = connection.cursor()
cursor.execute("select code from designation where code=%s",
(designation.code, ))
rows = cursor.fetchall()
if len(rows) != 1:
raise DataLayerError(
message=f"Code : {designation.code} does not exists")
cursor.execute("update designation set title=%s where code=%s",
(designation.title, designation.code))
cursor.execute("select title from designation where code=%s",
(designation.code, ))
updated_data = cursor.fetchall()
connection.commit()
if len(updated_data) != 1 or (updated_data[0][0] !=
designation.title):
raise Error(
"Updation failed due to unknown interrupt, please try again"
)
except Error as err:
raise DataLayerError(message=err.msg)
finally:
try:
if cursor.is_open():
cursor.close()
if connection.is_connected():
connection.close()
except:
pass
def create(self, account, connection, cursor):
try:
sql_insert_query = """ INSERT INTO accounts (isbn_13, account_name, account_num, balance, typeA, description, createdAt, updatedAt,users_accounts_id, enabled) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
insert_tuple = ("000"+account.account_name, account.account_name, account.account_num, account.balance, account.typeA, account.description, account.createdAt, account.updatedAt, account.user_id, account.enabled)
result = cursor.execute(sql_insert_query, insert_tuple)
connection.commit()
return result
except mysql.connector.Error as error :
connection.rollback()
return False
except Error as e:
raise Error("Save account failure in Account dao!")
print(e.value)
logging.error('e.value')
def test_select_rowcount_row_id():
# row count from test_queries.test_sample_batch01
test_sample_batch_to_check = 3
# Initialize tables to test before inserting sample rows
table_list = ['journal_loader',
'journal_batch',
'journal',
]
delete_status = query_initialize_000(table_list)
if delete_status[0] != 0:
raise Error('Table initialization failed at test_select_batch_by_row_id()')
# Populate test tables with data
# Load journal_batch with sample data dictionary from test_queries
for _ in test_sample_batch00:
insert_new_batch_name(**_)
# Get the batch row id for "test_batch_100"
journal_batch_name = 'test_batch_100'
test_batch_row = get_journal_batch_row_id_by_name(journal_batch_name)
print(f'test_batch_row: {test_batch_row}')
print(f'test_batch_row[0][0]: {test_batch_row[0][0]}')
print(f'test_batch_row[0][0][0]: {test_batch_row[0][0][0]}')
test_journal_batch_row_id = test_batch_row[0][0][0]
# Load journal_loader with sample data dictionary from test_queries
# *** Need to pass the journal_batch_row_id from sample batch created
table = 'journal_loader'
row_id = test_journal_batch_row_id
for _ in test_sample_batch01:
_['journal_batch_row_id'] = row_id
load_status = insert_je_row(table, **_)
if load_status != 0:
print('*** ERROR at test_select_rowcount_row_id() ***')
break
# call select_rowcount_row_id function
function_result = select_rowcount_row_id(table, row_id)
# function_result = 50 # force a fail
# Compare sample data to function result set
assert(test_sample_batch_to_check == function_result)
def SelectCommand(self, cmdText):
try:
if not self.conn.is_connected():
self.conn._open_connection()
self.cursor = self.conn.cursor()
self.cursor.execute(cmdText)
rows = self.cursor.fetchall()
if len(rows) == 1:
rows = rows[0]
except Error as err:
raise Error('ERROR MESSAGE: ' + str(err.msg))
finally:
self.cursor.close()
self.conn.close()
return rows
def delete(self, idUser):
cursor = self.connection.cursor()
sql = """ DELETE FROM {} WHERE idusers = '{}';""".format(
self.table, idUser)
try:
cursor.execute(sql)
if cursor.rowcount == 0:
raise Error('no found row, no deleted')
#print('Error: no found row, no deleted')
return False
except Error as e:
print('Error DAOUser delete: {}'.format(e))
return False
#self.connection.commit()
return True
def get_description_of_table(db_connection, table_name):
cursor = db_connection.cursor(dictionary=True)
cursor.execute(f'DESCRIBE {table_name}')
result = cursor.fetchall()
if not result:
raise Error(f'Table {table_name} couldn\'t be found.')
fields = []
primary_keys = []
for row in result:
field = row['Field']
fields.append(field)
if row['Key'] == 'PRI': # Check if field is primary key in table
primary_keys.append(field)
return {'fields': fields, 'primary_keys': primary_keys}
def get_tr(self, currency, rows=100, page=0):
tr_list = []
try:
cursor = self.conn.cursor(dictionary=True)
sql = """SELECT price, qty, date_time
FROM tr_coinone
WHERE currency = {}
ORDER BY date_time DESC
LIMIT {}, {}
""".format(currency, rows * page, rows)
cursor.execute(sql)
tr_list = cursor.fetchall()
except Error as e:
logger.error(e)
raise Error(msg=e)
finally:
cursor.close()
return tr_list
def __init__(self): Error.__init__(self)
