def get_pontlista_valtozas(orszag_id=0, limit=5):
column_headers = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
sql = 'SELECT * ' \
'FROM `lemu_pontlista` ' \
'WHERE `orszag_id` = "{orszag_id}" ' \
'ORDER BY `datum_created` DESC ' \
'LIMIT {limit};'.format(orszag_id=orszag_id, limit=limit)
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
result = [str(ex)]
mydb.close()
# Debug code
print(result)
# result returns with list of tuple
return column_headers, result
def upload_piac(content):
result = ''
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# NOTE: Be careful, that is order dependent!
sql = """
INSERT INTO lemu_piac (termek, mennyiseg, ar,
datum_created, other_datum_uploaded, other_who_uploaded
) VALUES (%s, %s, %s, %s, %s, %s);"""
print('Execute SQL command: ' + sql)
try:
mycursor.executemany(sql, content)
mydb.commit()
result += ' # ' + str(mycursor.rowcount) + 'record inserted.'
except mysql.connector.errors.IntegrityError:
result += 'Duplicated, has not not re-uploaded'
except Exception as ex:
result += str(ex)
print(result)
return result
def get_kronika():
result = []
column_headers = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# Debug all
#sql = 'SELECT * FROM lemu_pontlista'
sql = """
SELECT event_date, from_orszag_nev, from_orszag_id, to_orszag_nev, to_orszag_id,
is_successful, result, event_type
FROM lemu_kronika
ORDER BY lemu_kronika.event_date DESC;
"""
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
result = [str(ex)]
# Debug code
print(result)
return column_headers, result
def get_gomb_list():
result = []
column_headers = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
sql = 'SELECT * FROM lemu_orszagok'
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
result = [str(ex)]
mydb.close()
# Debug code
#print(result)
return column_headers, result
def felmeres_kereses(orszag):
result = ''
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
sql = 'SELECT * from lemu_felmeresek ' \
'WHERE LOWER(orszag_nev) LIKE "%{finding}%" ' \
'or orszag_id LIKE "%{finding}%" ' \
'ORDER BY datum_created DESC'.format(finding=orszag.strip().lower())
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
except Exception as ex:
err_msg = '[ERROR]: Exception at felmeres kereses: ' + str(ex)
eventlog.log_error(err_msg)
result = err_msg
print('SQL result: ' + str(result))
return result
def upload_gomb(content):
result = ''
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
sql = 'INSERT INTO lemu_orszagok (orszag_nev, orszag_id, sarkanykod, ' \
'birodalom_nev, birodalom_id, korszak, ' \
'sziget, gyalogsag, ostromgep, nep, vedo, elit_vedo, ' \
'rang, tamado, elit_tamado, harci_kedv, hajok, terulet, ' \
'lakos, magia, mana, ' \
'munkanelkuli_munkas, munkanelkuli_bolcs, runa, hit, ' \
'alvilag, armany, hangulat, dekadencia, denar, termeny, ' \
'pontszam, hirnev, helyezes_1, helyezes_2, datum_created, ' \
'other_datum_uploaded, other_who_uploaded, other_comment' \
') VALUES (%s, %s, %s, ' \
'%s, %s, %s, ' \
'%s, %s, %s, %s, %s, %s, ' \
'%s, %s, %s, %s, %s, %s, ' \
'%s, %s, %s, ' \
'%s, %s, %s, %s, ' \
'%s, %s, %s, %s, %s, %s, ' \
'%s, %s, %s, %s, %s, ' \
'%s, %s, %s)'
val = list(content.values())
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql, val)
mydb.commit()
result = str(mycursor.rowcount) + ' record inserted.'
except mysql.connector.errors.IntegrityError:
result = 'Duplicated, has not not re-uploaded'
except Exception as ex:
err_msg = '[ERROR] Exception at gombfeltoltes: ' + str(ex)
eventlog.log_error(err_msg)
result = err_msg
mydb.close()
print(result)
return result
def upload_pontlista(content):
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# Cross-check from previous pontszam data
pontlista_checker_result, pontlista_data = pontlista_previous_checker(mycursor, content)
# NOTE: Be careful, that is order dependent!
sql = 'INSERT INTO lemu_pontlista (orszag_nev, orszag_id, helyezes,' \
'szabi, ved, tilt, sarkanykod, ' \
'sziget, hirnev, pontszam, ' \
'tav, ' \
'datum_created, magved, ' \
'other_datum_uploaded, other_who_uploaded, other_comment, ' \
'pontlista_type, torolt' \
') VALUES (%s, %s, %s, ' \
'%s, %s, %s, %s, ' \
'%s, %s, %s, ' \
'%s, ' \
'%s, %s, ' \
'%s, %s, %s, ' \
'%s, %s);'
val = list(content.values())
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql, val)
mydb.commit()
pontlista_checker_result += ' # ' + str(mycursor.rowcount) + 'record inserted.'
except mysql.connector.errors.IntegrityError:
pontlista_checker_result += 'Duplicated, has not not re-uploaded'
except Exception as ex:
err_msg = '[ERROR]: Execption at pontlista feltoltes:' + str(ex)
eventlog.log_error(err_msg)
pontlista_checker_result += err_msg
mydb.close()
print(pontlista_checker_result)
return pontlista_checker_result, pontlista_data
def get_pontlista_from_db():
result = []
column_headers = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# Debug all
#sql = 'SELECT * FROM lemu_pontlista'
# TODO: sziget is removed from the SQL query do it has been requested from gömb
sql = """
SELECT orszag_nev, lemu_pontlista.orszag_id, helyezes, szabi, ved, tilt, sarkanykod, torolt, magved, lemu_pontlista.pontszam, hirnev, datum_created
FROM lemu_pontlista
JOIN (SELECT orszag_id, MAX(datum_created) as max_date FROM lemu_pontlista group by orszag_id) calculated_table
ON lemu_pontlista.orszag_id = calculated_table.orszag_id AND lemu_pontlista.datum_created = calculated_table.max_date
GROUP BY lemu_pontlista.orszag_id
ORDER BY lemu_pontlista.pontszam DESC;
"""
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
result = [str(ex)]
mydb.close()
# Debug code
print(result)
# result returns with list of tuple
return column_headers, result
def upload_kronika(content):
result = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# NOTE: Be careful, that is order dependent!
sql = """
INSERT INTO lemu_kronika (event_date, from_orszag_nev, from_orszag_id,
to_orszag_nev, to_orszag_id,
is_successful, result, event_type,
other_datum_uploaded, other_who_uploaded
) VALUES (
%s, %s, %s,
%s, %s,
%s, %s, %s,
%s, %s);"""
print('Execute SQL command: ' + sql)
for row in content:
try:
val = list(row.values())
mycursor.execute(sql, val)
result.append(' # ' + str(mycursor.rowcount) + 'record inserted.')
except mysql.connector.errors.IntegrityError as ex:
result.append('Duplicated, has not not re-uploaded: ' + str(ex))
except Exception as ex:
result.append('[ERROR]: ' + str(ex))
mydb.commit()
mydb.close()
print(result)
return result
def get_piac():
result = []
column_headers = []
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
# Debug all
#sql = 'SELECT * FROM lemu_pontlista'
sql = """
SELECT lemu_piac.termek, mennyiseg, ar, last_date
FROM lemu_piac
JOIN (SELECT termek, MAX(datum_created) as last_date FROM lemu_piac group by termek) calculated_table
ON lemu_piac.termek = calculated_table.termek AND lemu_piac.datum_created = calculated_table.last_date
GROUP BY lemu_piac.termek
ORDER BY lemu_piac.termek ASC;
"""
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
result = '[ERROR]: ' + [str(ex)]
# Debug code
print(result)
return column_headers, result
def user_validation(username, password):
is_successful_login = False
msg = ""
# Set user with highest restrictions
user_info = {
"is_admin": False,
"is_disabled": True,
"is_guest": True,
"notes": "",
"username": ""
}
# Fast checks
if username == '':
msg = "Not accepted username!"
return False, msg, None
if password == '':
msg = "Not accepted password!"
return False, msg, None
# Help: https://www.geeksforgeeks.org/md5-hash-python/
# encoding pw
password = hashlib.md5(password.encode())
password = password.hexdigest()
mydb = lemu_database.database_connection()
mycursor = mydb.cursor(
dictionary=True
) # NOTE!! Special diffence with other Python-SQL queries
sql = """
SELECT username, password, is_admin, is_disabled, is_guest, notes
FROM lemu_user
WHERE username LIKE "{username}";
""".format(username=username)
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql)
result = mycursor.fetchall()
#column_headers = mycursor.column_names
except Exception as ex:
# TODO: Temporary error handling
msg = [str(ex)]
is_successful_login = False
result = []
return is_successful_login, msg, user_info
finally:
mydb.close()
if len(result) == 0:
msg = 'There is no username: {}'.format(username)
is_successful_login = False
elif len(result) > 1:
msg = 'Catastrophe in the SQL when username searched: {}'.format(
username)
is_successful_login = False
elif len(result) == 1:
# Check pw
#password_index = column_headers.indexof('password')
result = result[0]
if result['password'] == password:
msg = 'Proper password'
is_successful_login = True
user_info['is_admin'] = result['is_admin']
user_info['is_disabled'] = result['is_disabled']
user_info['is_guest'] = result['is_guest']
user_info['notes'] = result['notes']
user_info['username'] = result['username']
if user_info['is_disabled']:
is_successful_login = False
else:
msg = 'Wrong password'
is_successful_login = False
else:
msg = 'Catastrophe in the Python at branching at SQL result: {}'.format(
username)
is_successful_login = False
return is_successful_login, msg, user_info
def upload_felmeres(content):
result = ''
mydb = lemu_database.database_connection()
mycursor = mydb.cursor()
print('Connected to MySQL')
print('Precheck uploaded felmérések')
# Dublicate checker
sql = 'SELECT * from lemu_felmeresek ' \
'WHERE LOWER(orszag_nev) = "{orszag_nev}" ' \
' AND orszag_id = {orszag_id} ' \
' AND felmeres_tipus LIKE "{felmeres_tipus}" ' \
' AND datum_created between ' \
' DATE_SUB("{datum_created}", INTERVAL 3 HOUR) ' \
' AND DATE_ADD("{datum_created}", INTERVAL 3 HOUR); ' \
''.format(
orszag_nev=content['orszag_nev'].lower().strip(),
orszag_id=content['orszag_id'],
felmeres_tipus=content['felmeres_tipus'],
datum_created=content['datum_created']
)
# SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);
# where date(game_date) between date('2012-11-03') and date('2012-11-05')
print('Execute SQL command: ' + sql)
duplicate_check_result = [
] # 0 length is a trick for the condition at below
try:
mycursor.execute(sql)
duplicate_check_result = mycursor.fetchall()
except Exception as ex:
err_msg = '[ERROR]: ' + str(ex)
result += err_msg + ' | '
eventlog.log_error(err_msg)
if len(duplicate_check_result) != 0:
print('Duplicate check SQL result: ' + str(duplicate_check_result))
result = 'Seems duplicated, has not re-uploaded: {}\n'.format(
duplicate_check_result)
else:
sql = 'INSERT INTO lemu_felmeresek (' \
'orszag_nev, orszag_id, ' \
'datum_created, ' \
'felmeres_tipus, content, ' \
'other_datum_uploaded, other_who_uploaded, other_comment' \
') VALUES (%s, %s, ' \
'%s,' \
'%s, %s,' \
'%s, %s, %s)'
val = list(content.values())
print('Execute SQL command: ' + sql)
try:
mycursor.execute(sql, val)
mydb.commit()
result += 'Felmeres uploaded: "{}" with type: "{}" with date "{}", {} record inserted'.format(
content['orszag_nev'], content['felmeres_tipus'],
content['datum_created'], str(mycursor.rowcount))
except mysql.connector.errors.IntegrityError:
result += 'Duplicated, has not not re-uploaded'
except Exception as ex:
err_msg = '[ERROR]: ' + str(ex)
eventlog.log_error(err_msg)
result += err_msg
print('Result: ' + str(result))
mydb.close()
return result