def clear_data(dbname, tblname):
"""Deletes ALL the data/rows in the specified table."""
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
tables = show_tables(dbname)
found = False
for x in range(len(tables)):
if tblname in tables[x]:
found = True
if not found:
raise Exception("This table does not exist anyway!")
command = "DELETE FROM " + tblname
cursor.execute(command)
success = count_rows(dbname, tblname)
connection.commit()
cursor.close()
connection.close()
if success == 0:
return True
else:
return False
def save_data(dbname, tblname, ndarray_input):
"""Save data in the specified table. Delete previous data from table or create table if it does not exist."""
if len(ndarray_input.shape) != 2:
raise Exception("This function requires multiple rows and an nd-array as input. "
"Try append_row or check the entries in each row.")
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
tables = show_tables(dbname)
present = False
for x in range(len(tables)):
if tblname in tables[x]:
present = True
cleared = clear_data(dbname, tblname)
if not cleared:
raise Exception("Data wasn't cleared!")
break
if not present:
raise Exception("Table does not exist!")
# create_table(dbname, tblname, columns, column_types)
num_of_s = ndarray_input.shape[1]
num_of_rows = ndarray_input.shape[0]
command = "INSERT INTO " + tblname + " VALUES ("
for x in range(num_of_s):
if x == 0:
command += "%s"
else:
command += ", %s"
command += ")"
for x in range(num_of_rows):
row = ndarray_input[x]
data = row.tolist()
cursor.execute(command, data)
connection.commit()
cursor.close()
connection.close()
count = count_rows(dbname, tblname)
if count > 0:
success = True
else:
success = False
return success
def count_rows(dbname, tblname):
"""Executes the SQL command to count the number of rows in the specified table."""
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
cmd = "SELECT COUNT(*) FROM " + tblname
cursor.execute(cmd)
count = cursor.fetchall()
connection.commit()
cursor.close()
connection.close()
return count[0][0]
def show_tables(dbname):
"""Executes the SQL command to show all tables in the specified database."""
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
cmmnd = "SHOW TABLES"
cursor.execute(cmmnd)
tables = cursor.fetchall()
connection.commit()
cursor.close()
connection.close()
return tables
def append_row(dbname, tblname, ndarray_input):
"""Append data from the given ndarray in the specified table and database.
Return true if some rows were appended."""
if len(ndarray_input.shape) != 1:
raise Exception("This function only takes a single row as input.")
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
cursor.execute("SELECT count(*) FROM information_schema.columns WHERE table_name = '" + tblname + "'")
columns = cursor.fetchall()
if columns[0][0] != len(ndarray_input):
raise Exception("This row does not have the same number of entries as there are columns!")
count1 = count_rows(dbname, tblname)
print(count1)
command = "INSERT INTO " + tblname + " VALUES ("
for x in range(ndarray_input.shape[0]):
if x == 0:
command += "%s"
else:
command += ", %s"
command += ")"
data = ndarray_input.tolist()
cursor.execute(command, data)
connection.commit()
cursor.close()
connection.close()
count2 = count_rows(dbname, tblname)
print(count2)
if count2 == count1+1:
success = True
else:
success = False
return success
def get_data(dbname, tblname):
"""Get the data from the specified table and database, and return it as an ndarray"""
connection = mysql.connector.connect(user=cred.get_user_name(), password=cred.get_password(),
host=cred.get_server_address(),
database=dbname)
cursor = connection.cursor()
cursor.execute("SELECT * FROM " + tblname)
results = cursor.fetchall()
data_array = np.array([])
for x in range(len(results)):
row = results[x]
if x == 0:
data_array = np.array(row)
else:
rownp = np.array(row)
data_array = np.vstack([data_array, rownp])
cursor.close()
connection.close()
return data_array