def login_query(values):
vals_arr = values.split()
if(len(vals_arr) == 2):
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
cursor = cnx.cursor()
print(vals_arr)
sql_command = ("SELECT*FROM "+str(table_name)+" WHERE emailadd = '"+vals_arr[0]+"' and passwd = '"+vals_arr[1]+"';")
print(sql_command)
try:
cursor.execute(sql_command,None)
print("execute ok")
counter = 0
for row in cursor.fetchall():
counter+=1
if(counter>0):
print("1")
return 1
elif(counter==-1):
return -1
else:
return 0
cursor.close()
except:
cnx.rollback()
cnx.close()
else:
return -2
def insert(table_name, data_array): #inserts data to table
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
table_exists = False
cursor = cnx.cursor()
if (str(table_name) == "usertable"):
sql_command = (
"INSERT INTO " + str(table_name) +
" (first_name, last_name, emailadd, passwd, regi_date) "
"VALUES (%s, %s, %s, %s, %s)")
table_exists = True
if (str(table_name) == "record_data"):
print("foo")
sql_command = (
"INSERT INTO " + str(table_name) +
"(record_datetime, lat, lon, tolerance, frequency, user_id) "
"VALUES (%s, %s, %s, %s, %s, %s)")
table_exists = True
if (str(table_name) == "noisesources"):
sql_command = ("INSERT INTO " + str(table_name) + "(description) "
"VALUES (%s)")
table_exists = True
if (str(table_name) == "noisesource_loc"):
sql_command = ("INSERT INTO " + str(table_name) +
"(lat, lon, source_id) "
"VALUES (%s, %s, %s)")
table_exists = True
if (str(table_name) == "noise_levels"):
sql_command = ("INSERT INTO " + str(table_name) + "(description) "
"VALUES (%s)")
table_exists = True
if (str(table_name) == "datetable"):
sql_command = ("INSERT INTO " + str(table_name) +
"(inputdate, myear, mmonth, season) "
"VALUES (%s, %s, %s, %s)")
table_exists = True
if (table_exists):
try:
print(sql_command)
cursor.execute(sql_command, data_array)
print("foofooo")
cnx.commit()
cursor.close()
except:
cnx.rollback()
raise
cnx.close()
def fetch_table(table_name): #shows the whole table
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
cursor = cnx.cursor()
if (table_name in table_list):
sql_command = ("SELECT*FROM " + str(table_name) + ";")
#print(sql_command)
cursor.execute(sql_command)
# return cursor.fetchall()
#cnx.commit();
print_results(cursor.fetchall(), cursor.description)
cursor.close()
cnx.close()
def delete_row(table_name, key, keys_value): #deletes a given row in table
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
cursor = cnx.cursor()
if (table_name in table_list):
sql_command = ("DELETE FROM " + str(table_name) + " WHERE " + key +
" = " + keys_value + ";")
# print(sql_command)
cursor.execute(sql_command)
# return cursor.fetchall()
cnx.commit()
#print_results(cursor.fetchall(),cursor.description)
cursor.close()
cnx.close()
def order(table_name, field,
order_type): #orders data in table by a given value
if (order_type == "ASC" or order_type == "DESC"):
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
cursor = cnx.cursor
if (table_name in table_list):
sql_command = ("SELECT*FROM " + str(table_name) + " ORDER BY " +
field + " " + order_type + ";")
cursor.execute(sql_command)
# return cursor.fetchall()
# print_results(cursor.fetchall(),cursor.description)
cursor.close()
cnx.close()
def update(table_name, changable_field, new_value, key,
keys_value): #updates data in table
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
cursor = cnx.cursor()
if (table_name in table_list):
sql_command = ("UPDATE " + str(table_name) + " SET " +
changable_field + " = " + new_value + " WHERE " + key +
" = " + keys_value + ";")
# print(sql_command)
cursor.execute(sql_command)
# return cursor.fetchall()
cnx.commit()
#print_results(cursor.fetchall(),cursor.description)
cursor.close()
cnx.close()
def select_columns(table_name, column_array): #shows data by given columns
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
cursor = cnx.cursor()
columns = ""
i = 0
for x in column_array:
columns += (x + ", ")
columns = columns[:-2]
if (table_name in table_list):
sql_command = ("SELECT " + columns + " FROM " + str(table_name) + ";")
# print(sql_command)
cursor.execute(sql_command)
# return cursor.fetchall()
#cnx.commit();
print_results(cursor.fetchall(), cursor.description)
cursor.close()
cnx.close()
def select_columns_by_val(table_name, column_or_columns, key, val):
cnx = mysql.connector.connect(user=details.user(),
password=details.password(),
host=details.host(),
database=details.database())
sql_command = ""
global cursor
cursor = cnx.cursor(buffered=True)
col = ""
if (type(column_or_columns) is list):
for x in column_or_columns:
col += (x + ", ")
col = col[:-2]
else:
col = column_or_columns
if (table_name in table_list):
sql_command = ("SELECT " + col + " FROM " + str(table_name) +
" WHERE " + key + " = '" + val + "';")
cursor.execute(sql_command)
tup = cursor.fetchone()
return (' '.join(map(str, tup)))
cursor.close()
cnx.close()
