def createTables(curs, verbose):
if verbose:
print("\n--- Creating tables ...")
tableName = "Numbers"
if tableExist(curs, tableName, verbose):
if verbose:
print("\nTable '" + tableName + "' exists")
return True
else:
if verbose:
print("\nCreating table '" + tableName + "' ...")
q = ()
sql = (
"CREATE TABLE " + tableName +
" (number INT(30) PRIMARY KEY, highest INT(30), steps INT(30), note VARCHAR(1)"
)
doQuery(curs, q, sql, True, verbose)
if tableExist(curs, tableName, verbose):
print("\nCreated table '" + tableName + "'")
return True
else:
return False
def createUser(curs, verbose):
#dbUser = "******"
if userExist(curs, dbUser, verbose):
print("\nUser '" + dbUser + "' exists")
return True
else:
if verbose:
print("\n--- Creating user '" + dbUser + "' ...")
q = (dbUser, dbHost, dbPass)
sql = """CREATE USER %s@%s IDENTIFIED BY %s"""
doQuery(curs, q, sql, True, verbose)
q = (dbUser, dbHost)
sql = "GRANT ALL ON " + dbName + ".* TO %s@%s"
doQuery(curs, q, sql, True, verbose)
q = ()
sql = """FLUSH PRIVILEGES"""
doQuery(curs, q, sql, True, verbose)
if userExist(curs, dbUser, verbose):
print("\nCreated user '" + dbUser + "'")
return True
else:
return False
def writeToDb(number, highestNumber, steps, note, verbose):
if verbose:
print("\n--- Writing to database ... \n Number: " + str(number) +
"\n Highest: " + str(highestNumber) + " \n Steps: " +
str(steps))
q = ()
sql = ("INSERT INTO Numbers (number, highest, steps, note) VALUES (" +
str(number) + ", " + str(highestNumber) + ", " + str(steps) +
", '" + note + "')")
result = doQuery(curs, q, sql, True, verbose)
commitToDB(con, verbose)
def createDB(curs, verbose):
#dbName = "mysql"
if verbose:
print("\n--- Creating database '" + dbName + "' ...")
if databaseExist(curs, dbName, verbose):
print("\nDatabase '" + dbName + "' exists")
return True
else:
if verbose:
print("\n--- Creating database '" + dbName + "' ...")
q = ()
sql = ("CREATE DATABASE " + dbName)
doQuery(curs, q, sql, True, verbose)
commitToDB(con, verbose)
if databaseExist(curs, dbName, verbose):
print("\nCreated database '" + dbName + "'")
return True
else:
return False
def checkNumberExists(curs, number, verbose):
if verbose:
print("\n--- Checking if number '" + str(number) + "' exists ...")
q = (number, )
sql = ("""SELECT number, highest, steps FROM Numbers WHERE number = %s""")
result = doQuery(curs, q, sql, True, verbose)
if result:
if verbose:
print("\n--- Number: " + str(result[0]) + "\n Highest: " +
str(result[1]) + "\n Steps: " + str(result[2]))
return result[0], result[1], result[2]
else:
return False, False, False
def findLowestNumber(curs, verbose):
if verbose:
print("\n--- Finding lowest number ...")
q = ()
sql = (
"SELECT min(unused) AS unused "
"FROM ("
"SELECT MIN(t1.number)+1 as unused "
"FROM Numbers AS t1 WHERE NOT EXISTS (SELECT * FROM Numbers AS t2 WHERE t2.number = t1.number+1) "
"UNION "
#"-- Special case for missing the first row "
"SELECT 1 "
"FROM DUAL "
"WHERE NOT EXISTS (SELECT * FROM Numbers WHERE number = 1)) "
"AS subquery")
result = doQuery(curs, q, sql, True, verbose)
return result[0]
def userExist(curs, userName, verbose):
if verbose:
print("\n--- Checking if user '" + userName + "' exists ...")
q = (userName, )
sql = """SELECT User FROM user WHERE User = %s"""
result = doQuery(curs, q, sql, True, verbose)
if result:
if result[0] == userName:
if verbose:
print("\n--- User '" + userName + "' exists")
return True
else:
if verbose:
print("\n--- User '" + userName + "' does not exist")
return False
else:
if verbose:
print("\n--- User '" + userName + "' does not exist")
return False
def databaseExist(curs, database, verbose):
if verbose:
print("\n--- Checking if database '" + database + "' exists ...")
q = (database, )
sql = """SHOW DATABASES LIKE %s"""
result = doQuery(curs, q, sql, True, verbose)
if result:
if result[0] == database:
if verbose:
print("\n--- Database '" + database + "' exists")
return True
else:
if verbose:
print("\n--- Database '" + database + "' does not exist")
return False
else:
if verbose:
print("\n--- Database '" + database + "' does not exist")
return False
def tableExist(curs, tableName, verbose):
if verbose:
print("\nChecking if table '" + tableName + "' exists ...")
q = (tableName, )
sql = """SHOW TABLES LIKE %s"""
result = doQuery(curs, q, sql, True, verbose)
if result:
if result[0] == tableName:
if verbose:
print("\n--- Table '" + tableName + "' exists")
return True
else:
if verbose:
print("\n--- Table '" + tableName + "' does not exist")
return False
else:
if verbose:
print("\n--- Table '" + tableName + "' does not exist")
return False