def delete_project_table():
"""
Deletes the table in MySQL that this project uses. Will do nothing if the
table does not yet exist.
"""
query = "DROP TABLE IF EXISTS %s;"
SQLConnector.execute(query % (COURSES_TABLE_NAME,))
SQLConnector.execute(query % (STUDENT_TABLE_NAME,))
def searchEvents(filter):
sql = SQLConnector()
res = sql.externalSearch('CalendarEvents', filter)
sql.cleanUp()
return res
def delete(self, eventId):
sql = SQLConnector()
sql.cursor.execute("DELETE FROM `CalendarEvents` WHERE `id` = ?",
[eventId])
sql.cnx.commit()
sql.cleanUp()
return True, 200, AJAX_HEADER
def is_table_set_up():
"""
Returns True if this project's MySQL table is set up, False otherwise
"""
query = "SELECT table_name FROM information_schema.tables WHERE table_schema='%s' and table_name='%s';"
retval = SQLConnector.execute(query % (PROJECT_DB_NAME, COURSES_TABLE_NAME,))
courses_setup = retval != None and len(retval) == 1
retval = SQLConnector.execute(query % (PROJECT_DB_NAME, STUDENT_TABLE_NAME))
student_setup = retval != None and len(retval) == 1
return courses_setup and student_setup
def deleteUser(user, password):
sql = SQLConnector()
# Removing the user cascades the deletion to the CalendarEvents table
sql.cursor.execute(
'DELETE FROM `Users` WHERE `Username` = ? AND `Password` = ?',
[user, hashString(password)])
sql.cnx.commit()
sql.cleanUp()
def addCalendarEvent(targetUser, eventDate, description, shortName,
priorityLevel):
sql = SQLConnector()
try:
sql.insert(
'CalendarEvents', {
'OwnerId': targetUser,
'Date': eventDate,
'Description': description,
'ShortName': shortName,
'PriorityLevel': priorityLevel
})
sql.cnx.commit()
sql.cursor.execute("SELECT LAST_INSERT_ID()")
res = [row for row in sql.cursor][0][0]
except:
print('ERROR: Insertion failed for Event for user [%s]' % targetUser)
sql.cleanUp()
return False
sql.cleanUp()
return res
def userExists(user):
sql = SQLConnector()
# Use the externalSearch function for queries using external inputs (i.e. user-entered)
check = sql.externalSearch('Users', {'Username': user})
sql.cleanUp()
if (len(check) != 0):
return True
return False
def send_token(token):
connection = pika.BlockingConnection(
pika.ConnectionParameters('localhost'))
channel = connection.channel()
unique_identifier = str(uuid.uuid4())
if token.isdigit():
remote_path = SQLConnector.debug(token)
MEM_CACHE.set(unique_identifier, [remote_path, token])
else:
remote_path = token
MEM_CACHE.set(unique_identifier, [remote_path])
queue = channel.queue_declare(queue='execution',
durable=True,
exclusive=False,
auto_delete=False)
channel.basic_publish(exchange='',
routing_key='execution',
body=unique_identifier,
properties=pika.BasicProperties(delivery_mode=2))
if queue.method.consumer_count <= 0:
DeployDaemon.daemon_deploy()
DeployDaemon.scaling()
connection.close()
return " [x] Sent %r" % unique_identifier
def validatePassword(user, password):
sql = SQLConnector()
res = sql.getRecord('Users', {
'Username': user,
'Password': hashString(password)
})
sql.cleanUp()
if (len(res) != 0):
return True
return False
def takePhotoWSQLData():
killgphoto2Process()
gp(saveinsdcardCommand)
gp(clearCommand)
createSaveFolder()
scon = sqc.SQLConnector()
currentid = scon.readFromDB(sqq.selectmaxidquery)
query = sqq.selectLLSquery(currentid)
LLS = scon.readFromDB(query)
if LLS is not None:
print("Current LLS: " + str(LLS))
if int(LLS) <= 200:
gp(autoisoCommand)
gp(dayshutterspeedCommand)
elif int(LLS) > 200 and int(LLS) <= 640:
gp(autoisoCommand)
gp(sunsetshutterspeedCommand)
elif int(LLS) > 640 and int(LLS) < 940:
gp(autoisoCommand)
gp(twilightshutterspeedCommand)
else:
gp(autoisoCommand)
gp(nightshutterspeedCommand)
captureImages()
renameFiles(picID)
sqlpathtophoto = (save_location + shot_time + picID + ".JPG")
query = sqq.buildInsertCamQuery(sqlpathtophoto, currentid)
scon.writeToDB(query)
print("Camera end\n")
def takePhotoWSQLDataTest():
killgphoto2Process()
gp(clearCommand)
createSaveFolder()
scon = sqc.SQLConnector()
currentid = scon.readFromDB(sqq.selectmaxidquery)
print(currentid)
query = sqq.selectLLSquery(currentid)
LLS = scon.readFromDB(query)
if LLS < 200:
gp(autoisoCommand)
gp(dayshutterspeedCommand)
elif LLS > 200 and LLS < 400:
gp(autoisoCommand)
gp(dayshutterspeedCommand)
else:
gp(autoisoCommand)
gp(dayshutterspeedCommand)
captureImages()
renameFiles(picID)
sqlpathtophoto = (save_location + shot_time + picID + ".JPG")
query = sqq.buildInsertCamQuery(sqlpathtophoto, currentid)
scon.writeToDB(query)
def create_project_table(courses_column_names, student_column_names):
"""
Sets up MySQL with a table with a set schema given a list of column_names.
Does nothing if the table is already set up.
Params:
courses_column_names - A list of strings containing column names for the courses table
student_column_names - A list of strings containing column names for the students table
Returns:
None
"""
# Tons of format strings!
query = "CREATE TABLE IF NOT EXISTS %s (%s);"
schema = (("%s VARCHAR(64), " * len(courses_column_names))[:-2]) % tuple(courses_column_names)
SQLConnector.execute(query % (COURSES_TABLE_NAME, schema))
schema = (("%s VARCHAR(64), " * len(student_column_names))[:-2]) % tuple(student_column_names)
SQLConnector.execute(query % (STUDENT_TABLE_NAME, schema))
def __init__(self, logger=None):
self.logger = logger or logging.getLogger(__name__)
self.br = mechanize.Browser()
self.fileCount = 0 #counter for files.
self.id = 0 #counter for id in file
self.nextLink = "" #link for new page
self.logOutUser = ""
self.sql = SQLConnector.MySql(host='localhost',
user='******',
password='******',
dbName='fora')
self.sql.dBConfiguration()
def get(self, username, year, month, day):
sql = SQLConnector()
sql.cursor.execute(
"""
SELECT
*
FROM
`CalendarEvents`
WHERE
`OwnerId` = ?
AND YEAR(Date) = ?
AND MONTH(Date) = ?
AND DAY(Date) = ?
""", [username, year, month, day])
res = sql.cursor.fetchall()
retVal = []
for row in res:
record = {}
record['id'] = row[0]
record['OwnerId'] = row[1].decode('utf-8')
record['Date'] = str(row[2])
record['Description'] = row[3].decode('utf-8')
record['ShortName'] = row[4].decode('utf-8')
record['PriorityLevel'] = row[5]
retVal.append(record)
sql.cleanUp()
return retVal, 200, AJAX_HEADER
def addUser(user, password):
sql = SQLConnector()
# Don't store the plaintext password
sql.insert('Users', {'Username': user, 'Password': hashString(password)})
sql.cnx.commit()
sql.cleanUp()
str(cfg.getData(cfgInflux.Section, cfgInflux.Host)),
str(cfg.getData(cfgInflux.Section, cfgInflux.Port)),
str(cfg.getData(cfgInflux.Section, cfgInflux.User)),
str(cfg.getData(cfgInflux.Section, cfgInflux.Password)),
str(cfg.getData(cfgInflux.Section, cfgInflux.DbName)),
str(cfg.getData(cfgInflux.Section, cfgInflux.DbUser)),
str(cfg.getData(cfgInflux.Section, cfgInflux.DbPassword)),
str(cfg.getData(cfgInflux.Section, cfgInflux.Measurement, default="XiaomiMiSensors"))
)
inflx.writeData(sensorDaten, aTimeShift="+01:00", aTraceOn=True, aTestOnly=False)
# Write data into MySQL database ()
try:
db = SQLConnector.MySQLConnector(log,
cfg.getData(cfgMySql.Section, cfgMySql.DbHost),
cfg.getData(cfgMySql.Section, cfgMySql.DbUser),
cfg.getData(cfgMySql.Section, cfgMySql.DbPassword),
cfg.getData(cfgMySql.Section, cfgMySql.DbName),
cfg.getData(cfgMySql.Section, cfgMySql.DbTableName))
db.writeData(sensorDaten, nameMapping=SQLTableMapping)
db.closeDbConnection()
except Exception as ex:
log.error("MySQL database not rechable or other error: '{}'".format(ex))
log.info("Measurement ends for sensor mac={}".format(mac))
log.info("------------End measurement------------{}".format(os.linesep))
def load_excel_file(datafile):
"""
Takes the Pandas datafiles and inserts the data into the project's MySQL table.
If the project's table is not yet created, this function will call 'create'.
The column names are specified within COURSES_TABLE_COLUMNS and
STUDENT_TABLE_COLUMNS
If they are not present in the datafile, a KeyError will be raised
"""
column_names = get_column_names(datafile) # loads file
new_student_columns = STUDENT_TABLE_COLUMNS
# check if COURSES_TABLE_COLUMNS are completed contained in the column names
for name in COURSES_TABLE_COLUMNS:
if name not in column_names:
raise KeyError("%s is not present in the provided datafile" % name)
sys.exit(1)
# check if STUDENT_TABLE_COLUMNS are completely contained in the column names
for name in new_student_columns:
if name not in column_names:
raise KeyError("%s is not present in the provided datafile" % name)
sys.exit(1)
for i in range(0, TOTAL_REQ_COUNT):
new_student_columns.append("REQ%02d" % i) # grad reqs
delete_project_table()
create_project_table(COURSES_TABLE_COLUMNS, new_student_columns)
rows, cols = datafile.shape
counter = 0
print "Populating %s" % COURSES_TABLE_NAME
print "Inserting %d rows with %d data fields each" % (rows, cols)
student_data = {} # dictionary used to temporarily store data for students datatabe
for row in datafile.itertuples():
if counter % 100 == 0:
print "Progress: %d of %d" % (counter, rows)
data = list(row._asdict().values())[1:]
#assert(len(data) == len(column_names))
# FIXME THIS IS BAD BUT IDK HOW TO MAKE IT BETTER - YICHENG
# Works for now tho
if str(data[0]) not in student_data.keys():
student_data[str(data[0])] = [data[1], data[2], data[3], data[4]]
data = [data[0], data[5], data[6], data[7], data[8], data[9], data[11], data[12]]
# Generate query
query = "INSERT INTO %s (%s) VALUES (%s);"
schema = (("%s , " * len(COURSES_TABLE_COLUMNS))[:-2]) % tuple(COURSES_TABLE_COLUMNS)
values = (("'%s' , " * len(data))[:-2]) % tuple(( str(it).upper() for it in data))
query = query % (COURSES_TABLE_NAME, schema, values)
# print query
SQLConnector.execute(query)
counter += 1
print "Done!"
print "Begin analysis... Populating %s" % STUDENT_TABLE_NAME
script_path = os.path.realpath(__file__)
script_loc = os.path.dirname(script_path)
reqs = json.loads(open(script_loc + '/../static/reqs.json', 'r').read())['grad_requirements']
query = "SELECT DISTINCT STUDENTID FROM %s;" % (COURSES_TABLE_NAME)
res = SQLConnector.execute(query)
print "Inserting %d students" % (len(res))
counter = 0
for (osis, ) in res:
if counter % 100 == 0:
print "Progress: %d of %d" % (counter, len(res))
query = "SELECT COURSE FROM %s WHERE STUDENTID = %s AND \
((MARK >= 65 AND MARK REGEXP '^[0-9]+$') OR MARK='P' OR MARK='C' OR \
MARK='CR' OR MARK REGEXP '^[A-D]$');" % (COURSES_TABLE_NAME, osis)
r = SQLConnector.execute(query)
if r:
courses_passed = [course for (course,) in r]
else:
courses_passed = []
query = "SELECT COURSE FROM %s WHERE STUDENTID = %s AND \
((MARK < 65 AND MARK REGEXP '^[0-9]+$') OR MARK = 'F' OR \
MARK='NC' OR MARK = 'NS');" % (COURSES_TABLE_NAME, osis)
r = SQLConnector.execute(query)
if r:
courses_failed = [course for (course, ) in r]
else:
courses_failed = []
req_status = []
for requirement in reqs:
status = HAS_FAILED # use min to compute
for option in requirement['options']:
status = min(status,
get_track_completion_status(courses_passed, courses_failed, option['course-code']))
if status == COMPLETED:
break
req_status.append(str(status))
query = "INSERT INTO %s (%s) VALUES (%s);"
schema = (("%s, " * len(new_student_columns))[:-2]) % tuple(new_student_columns)
values = (("'%s', " * len(new_student_columns))[:-2]) % tuple([osis] + student_data[str(osis)] + req_status)
query = query % (STUDENT_TABLE_NAME, schema, values)
r = SQLConnector.execute(query)
counter += 1
def getBvrpReport(self):
SQLConnector.get_report('bvrp')
self.test = closeWindow()
self.test.show()
self.hide()
def getVrpReport(self):
SQLConnector.get_report('vrp')
self.test = closeWindow()
self.test.show()
def getKvrzReport(self):
SQLConnector.get_report('kvrz')
self.test = closeWindow()
self.test.show()
def getHCReport(self):
SQLConnector.get_report('hc')
self.test = closeWindow()
self.test.show()