def createTable(mode = 0, connection = None, database_spec = None, drop_tables = None):
if mode == 1:
menu.clearScreen()
print("\nAirline Booking Setup")
# connect to database
curs = database.cursor(connection)
try:
# drop pre-existing tables
checkTable(drop_tables, curs)
# create tables
execSQL(database_spec, curs)
connection.commit()
database.close(None,curs)
print("Setup Complete\n")
except cx_Oracle.DatabaseError as exc:
error, = exc.args
print(sys.stderr, "Oracle code:", error.code)
print(sys.stderr, "Oracle message:", error.message)
def maybe_authorize_user(username, password_hash, otp):
global users_cache
if username is None:
return None
if username in users_cache:
user_info = users_cache[username]
if user_info[1] != password_hash:
return None
else:
with db.cursor() as cur:
cur.execute(
(
'SELECT username, password_hash, two_factor FROM users\n'
'WHERE password_hash = ? AND username = ?'
),
[password_hash, username]
)
user_info = cur.fetchone()
if user_info is None:
return None
users_cache[username] = user_info
if user_info[2] is not None:
valid_otp = pyotp.totp.TOTP(user_info[2])
try:
if type(otp) is not str or len(otp) != 6:
raise Exception('Invalid otp: {}'.format(repr(otp)))
if not valid_otp.verify(otp, valid_window=2):
logger.debug('Wrong otp: {}', otp)
return None
except Exception as e:
logger.error(repr(e))
return None
user = User(username=user_info[0], password_hash=user_info[1], two_factor_enabled=(user_info[2] is not None))
return create_session(user)
def get_user_by_name(username):
with db.cursor() as cur:
cur.execute('SELECT username, password_hash, two_factor FROM users WHERE username = ?', [username])
user_info = cur.fetchone()
if user_info is None:
raise Exception(f'User `{username}` not found in the database')
return User(username=user_info[0], password_hash=user_info[1], two_factor_enabled=(user_info[2] is not None))
def collectEdgeUsedCounts(trial,
core,
timing=None,
medium=None,
verification=None):
nodes = database.listNodes(trial)
nm = {}
for n in nodes:
nm[n["id"]] = len(nm)
nodes = list(map(lambda n: n["name"], nodes))
res = database.cursor().execute(
"""
SELECT n1.id,n2.id,answers.*
FROM nodes AS n1, nodes AS n2
INNER JOIN answers ON (n1.id = answers.src AND n2.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE n1.trial = ? AND n2.trial = ? AND (timing=? OR %d) AND (medium=? OR %d) AND (verification = ? OR %d)
""" % (timing == None, medium == None, verification == None),
(trial, trial, timing, medium, verification)).fetchall()
table = [([0] * len(nodes)) for n in nodes]
for row in res:
table[nm[row[0]]][nm[row[1]]] += 1
nodes.append("Average")
for row in table:
row.append("%0.2f ±%0.2f" % (mean(row), pstdev(row)))
newRow = []
for col in range(len(table[0]) - 1):
newRow.append(
"%0.2f ±%0.2f" %
(mean(table, lambda x: x[col]), pstdev(table, lambda x: x[col])))
table.append(newRow + [""])
return ["table", nodes, nodes, table]
def collectEdgeCorrect(trial, core, timing = None, medium = None, verification = None):
nodes = database.listNodes(trial)
nm = {}
for n in nodes: nm[n["id"]] = len(nm)
nodes = list(map(lambda n: n["name"], nodes))
res = database.cursor().execute("""
SELECT n1.id,n2.id,answers.*
FROM nodes AS n1, nodes AS n2
INNER JOIN answers ON (n1.id = answers.src AND n2.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE n1.trial = ? AND n2.trial = ? AND (timing=? OR %d) AND (medium=? OR %d)
""" % (timing == None, medium == None), (trial,trial,timing,medium)).fetchall()
table = [[[0,0] for n in nodes] for n in nodes]
for row in res:
if int(row["verification"]) & verification == verification:
table[nm[row[0]]][nm[row[1]]][0] += 1
table[nm[row[0]]][nm[row[1]]][1] += 1
table = list(map(lambda r: list(map(lambda x: x[0]/x[1] if x[1] != 0 else 0, r)), table))
nodes.append("Average")
for row in table:
row.append("%0.2f ±%0.2f" % (mean(row), pstdev(row)))
newRow = []
for col in range(len(table[0])-1):
newRow.append("%0.2f ±%0.2f" % (mean(table, lambda x: float(x[col])), pstdev(table, lambda x: float(x[col]))))
table.append(newRow + [""])
table = reformat(table, formatPercent, isNumber)
return ["table", nodes, nodes, table]
def add_video(self, service, url, title, duration, start_time):
c = database.cursor()
c.execute('''
SELECT rank
FROM room_queue
WHERE room_id = ?
ORDER BY rank DESC
LIMIT 1'''
, (self.room_id,))
result_next_rank = c.fetchone()
if result_next_rank:
next_rank = result_next_rank[0] + 1.0
else:
next_rank = 0.0
video_id = Video.create(
self.room_id
, next_rank
, service
, url
, title
, duration
, start_time)
return Video(video_id)
def test_cursor(self): try: cur = database.cursor(self.conn) except Exception as e: raise cur.close()
def _set_query(self, column, value):
'''Construct a setter query with sanitized inputs.'''
column = column.lower()
self._check_column(column)
c = database.cursor()
return c.execute("UPDATE {} SET {} = ? WHERE _rowid_ = ?".format(
self.__class__.__name__, column), (value, self.rowid))
def collectNodeUsedCounts(trial, core, timing = None, medium = None, verification = None):
res = database.cursor().execute("""
SELECT
nodes.name,
COUNT(DISTINCT answers.solution) AS c1,
COUNT(*) AS c3
FROM nodes
LEFT JOIN answers ON (nodes.id = answers.src OR nodes.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE solutions.trial=? AND (timing=? OR %d) AND (medium=? OR %d) AND (verification=? OR %d)
GROUP BY nodes.id
ORDER BY c1 desc
""" % (timing == None, medium == None, verification == None), (trial,timing,medium,verification)).fetchall()
listing = list(map(lambda r: [
r[0],
"%s (%0.2f%%)" % (r[1], r[1]*100 / core["students"]),
"%s (%0.2f per student)" % (r[2], r[2] / core["students"])
], res))
if len(res) > 0:
foot = ["Average",
"%0.2f ±%0.2f" % (mean(res, lambda x: x[1]), pstdev(res, lambda x: x[1])),
"%0.2f ±%0.2f" % (mean(res, lambda x: x[2]), pstdev(res, lambda x: x[2])),
]
else: foot = None
return ["listing", [
"Node",
"Used by n students",
"Used in n connections"
], listing, foot]
def __init__(self, room_id): c = database.cursor() # Load room. c.execute( '''SELECT name , owner FROM rooms WHERE room_id = ? LIMIT 1''' , (room_id,)) result_room = c.fetchone() if result_room is None: raise NoSuchRoomException self.__room_id = room_id self.__name = result_room[0] self.__owner = result_room[1] self.__admins = [] # Load admins. c.execute( '''SELECT admin_id FROM room_admins WHERE room_id = ?''' , (room_id,)) result_admins = c.fetchall() for admin in result_admins: self.__admins.append(admin)
def collectNodeUsagePlot(trial,
core,
timing=None,
medium=None,
verification=None):
res = database.cursor().execute(
"""
SELECT
nodes.name,
COUNT(DISTINCT answers.solution) AS c1
FROM nodes
LEFT JOIN answers ON (nodes.id = answers.src OR nodes.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE solutions.trial=? AND (timing=? OR %d) AND (medium=? OR %d) AND (verification = ? OR %d)
GROUP BY nodes.id
ORDER BY c1 desc
""" % (timing == None, medium == None, verification == None),
(trial, timing, medium, verification)).fetchall()
res = list(map(lambda r: [r[0], [r[1]]], res))
return [
"image",
plot.barplot("nodeusage-%s-%s-%s.png" % (timing, medium, verification),
res)
]
def __init__(self, item_id): c = database.cursor() # Load video. c.execute( ''' SELECT room_id , rank , service , url , title , duration , start_time FROM room_queue WHERE item_id = ? LIMIT 1''' , (item_id,)) result_video = c.fetchone() if result_video is None: raise NoSuchVideoException self.__item_id = item_id self.__room_id = result_video[0] self.__rank = result_video[1] self.__service = result_video[2] self.__url = result_video[3] self.__title = result_video[4] self.__duration = result_video[5] self.__start_time = result_video[6]
def _get_query(self, column):
'''Construct a getter query with sanitized inputs.'''
column = column.lower()
self._check_column(column)
c = database.cursor()
return c.execute(
"SELECT {} FROM {} WHERE _rowid_ = ?".format(
column, self.__class__.__name__), (self.rowid, )).fetchone()[0]
def _set_query(self, column, value):
'''Construct a setter query with sanitized inputs.'''
column = column.lower()
self._check_column(column)
c = database.cursor()
return c.execute(
"UPDATE {} SET {} = ? WHERE _rowid_ = ?".format(
self.__class__.__name__, column), (value, self.rowid))
def _get_query(self, column):
'''Construct a getter query with sanitized inputs.'''
column = column.lower()
self._check_column(column)
c = database.cursor()
return c.execute("SELECT {} FROM {} WHERE _rowid_ = ?".format(
column, self.__class__.__name__),
(self.rowid,)).fetchone()[0]
def _get_table_foreign_key_references(table_name):
with database.cursor() as cursor:
cursor.execute(FOREIGN_KEY_QUERY_STRING + \
'where child_tab_cons.table_name = %s;', [table_name])
column_names = cursor_column_names(cursor)
return [
dict(zip(column_names, row))
for row in cursor
]
def remove(self):
c = database.cursor()
c.execute('''
DELETE FROM room_queue
WHERE item_id = ?'''
, (self.item_id,))
database.commit()
def logout(connection, user):
cursor = database.cursor(connection)
update = "Update users set last_login = sysdate where email = :user_email"
update = update.replace(":user_email", "'"+user+"'")
cursor.execute(update)
connection.commit()
database.close(cursor)
def from_id(cls, id):
'''Create an object from an existing id in the database.'''
c = database.cursor()
# NOTE: for queries with a single ?-value, execute wants a string or a
# sequence.
row = c.execute("SELECT * FROM {} WHERE _rowid_ = ?".format(cls.__name__),
(id,)).fetchone()
if row is None:
raise IndexError("No such entry in the database.")
return cls(id)
def saveUserAvatar(user_id, filename):
db = connection()
csr = db.cursor()
query = "UPDATE users SET avatar='" + filename + "' where id='" + str(
user_id) + "';"
csr.execute(query)
db.commit()
csr.close()
db.close()
def video_queue(self):
c = database.cursor()
c.execute('''
SELECT item_id
FROM room_queue
WHERE room_id = ?
ORDER BY rank ASC'''
, (self.room_id,))
result_videos = c.fetchall()
return map(lambda x: Video(x[0]), result_videos)
def output_is_final(output_id):
cursor = database.cursor()
cursor.execute(
"""
SELECT transaction_hash
FROM transactions, outputs
WHERE
transactions.transaction_id=outputs.output_id
AND output_id=%s
""", (output_id, ))
print cursor.fetchall()
def from_id(cls, id):
'''Create an object from an existing id in the database.'''
c = database.cursor()
# NOTE: for queries with a single ?-value, execute wants a string or a
# sequence.
row = c.execute(
"SELECT * FROM {} WHERE _rowid_ = ?".format(cls.__name__),
(id, )).fetchone()
if row is None:
raise IndexError("No such entry in the database.")
return cls(id)
def update_rank(self, rank):
self.__rank = rank
c = database.cursor()
c.execute('''
UPDATE room_queue
SET rank = ?
WHERE item_id = ?'''
, (rank, self.item_id))
database.commit()
def get_transactions(user):
with db.cursor() as cur:
cur.execute(
'SELECT sender, amount, comment FROM transactions WHERE user = ?',
[user.username])
data = cur.fetchall()
def helper():
for sender, amount, comment in data:
yield Transaction(sender=sender, amount=amount, comment=comment)
return list(helper())
def _init(cls):
'''Create a table in the database from the class' attributes.'''
if cls.__initialized:
return
# get the column names from the class' attributes
cols = [
var.lower() for var in dir(cls)
# exclude private attributes
if not var.startswith("_")
# exclude methods
and not (isinstance(getattr(cls, var), types.FunctionType)
or isinstance(getattr(cls, var), types.MethodType))
]
attrmap = {col: getattr(cls, col) for col in cols}
# begin constructing query & create all needed properties
lines = [] # the lines inside the CREATE TABLE block
for col, attr in attrmap.items():
# do not process cls.rowid unless it was explicitly overwritten
if col == "rowid" and type(attr) is property:
continue
# only a type was given, create a column with no default value
if type(attr) is type:
lines.append(cls._make_column_by_type(col, attr))
# otherwise, try to construct a column with default value
else:
lines.append(cls._make_column_by_value(col, attr))
# finally, add the new column to the class as a property
cls._add_prop(col)
query = "CREATE TABLE IF NOT EXISTS {} (\n".format(cls.__name__)
query += ",\n".join(lines)
query += " )"
# save the query for later review
cls.__schema__ = property(lambda _: query)
database.cursor().execute(query)
cls.__initialized = True
def new(cls, cols, vals):
'''Create a new row in the appropriate table and return a new instance
of the class.'''
cls._init()
for col in cols:
cls._check_column(col)
cols = "(" + ", ".join(cols) + ")"
# construct the placeholders string
plhs = "(" + ", ".join(["?" for _ in range(len(vals))]) + ")"
query = 'INSERT INTO {} {} VALUES {}'.format(cls.__name__, cols, plhs)
c = database.cursor()
c.execute(query, vals)
return cls(c.lastrowid)
def output_is_final(output_id):
cursor = database.cursor()
cursor.execute(
"""
SELECT transaction_hash
FROM transactions, outputs
WHERE
transactions.transaction_id=outputs.output_id
AND output_id=%s
""",
(output_id,),
)
print cursor.fetchall()
def get_tables():
with database.cursor() as cursor:
cursor.execute('''
select table_name
from information_schema.tables
where table_schema = 'public'
and table_type = 'BASE TABLE'
order by table_name
''')
return [
row[0]
for row in cursor
]
def new(cls, cols, vals):
'''Create a new row in the appropriate table and return a new instance
of the class.'''
cls._init()
for col in cols:
cls._check_column(col)
cols = "(" + ", ".join(cols) + ")"
# construct the placeholders string
plhs = "(" + ", ".join(["?" for _ in range(len(vals))]) + ")"
query = 'INSERT INTO {} {} VALUES {}'.format(cls.__name__, cols, plhs)
c = database.cursor()
c.execute(query, vals)
return cls(c.lastrowid)
def _init(cls):
'''Create a table in the database from the class' attributes.'''
if cls.__initialized:
return
# get the column names from the class' attributes
cols = [var.lower() for var in dir(cls)
# exclude private attributes
if not var.startswith("_")
# exclude methods
and not (isinstance(getattr(cls, var), types.FunctionType)
or isinstance(getattr(cls, var), types.MethodType))]
attrmap = {col: getattr(cls, col) for col in cols}
# begin constructing query & create all needed properties
lines = [] # the lines inside the CREATE TABLE block
for col, attr in attrmap.items():
# do not process cls.rowid unless it was explicitly overwritten
if col == "rowid" and type(attr) is property:
continue
# only a type was given, create a column with no default value
if type(attr) is type:
lines.append(cls._make_column_by_type(col, attr))
# otherwise, try to construct a column with default value
else:
lines.append(cls._make_column_by_value(col, attr))
# finally, add the new column to the class as a property
cls._add_prop(col)
query = "CREATE TABLE IF NOT EXISTS {} (\n".format(cls.__name__)
query += ",\n".join(lines)
query += " )"
# save the query for later review
cls.__schema__ = property(lambda _: query)
database.cursor().execute(query)
cls.__initialized = True
def create(name, owner):
c = database.cursor()
c.execute('''
INSERT INTO rooms (
name
, owner)
VALUES(?, ?)'''
, (name, owner.user_id))
room_id = c.lastrowid
database.commit()
return room_id
def test_tables_exist(self):
try:
cur = database.cursor(self.conn)
except Exception as e:
raise
names = ['member', 'author', 'song', 'trimmedname', 'influence',
'instrument', 'sample', 'samplehash']
for name in names:
cur.execute("select * from information_schema.tables where table_name=%s", (name,))
self.assertTrue(cur.rowcount > 0, "table not found: " + name)
cur.close()
def getUserID(user):
db = connection()
csr = db.cursor()
query = "select id from users where email='" + str(user) + "';"
csr.execute(query)
profile_data = csr.fetchall()[0]
user_id = profile_data[0]
csr.close()
db.close()
return user_id
def maybe_get_user_by_full_creds(username, password_hash):
if username is None:
return None
with db.cursor() as cur:
cur.execute(
(
'SELECT username, password_hash, two_factor FROM users\n'
'WHERE password_hash = ? AND username = "******"'
),
[password_hash]
)
user_info = cur.fetchone()
if user_info is None:
return None
return User(username=user_info[0], password_hash=user_info[1], two_factor_enabled=(user_info[2] is not None))
def create(name, password):
password_hashed = pwd_context.encrypt(password)
c = database.cursor()
c.execute('''
INSERT INTO users (
name
, password)
VALUES(?, ?)'''
, (name, password_hashed))
user_id = c.lastrowid
database.commit()
return user_id
def collectNodeUsagePlot(trial, core, timing = None, medium = None, verification = None):
res = database.cursor().execute("""
SELECT
nodes.name,
COUNT(DISTINCT answers.solution) AS c1
FROM nodes
LEFT JOIN answers ON (nodes.id = answers.src OR nodes.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE solutions.trial=? AND (timing=? OR %d) AND (medium=? OR %d) AND (verification = ? OR %d)
GROUP BY nodes.id
ORDER BY c1 desc
""" % (timing == None, medium == None, verification == None), (trial,timing,medium,verification)).fetchall()
res = list(map(lambda r: [r[0], [r[1]]], res))
return ["image", plot.barplot("nodeusage-%s-%s-%s.png" % (timing,medium,verification), res)]
def authenticate():
if not os.environ.has_key('REMOTE_USER') or not os.environ['REMOTE_USER']:
raise TiqitError("User is not logged in.")
elif os.environ['REMOTE_USER'] == 'tiqit-api':
# Should be a custom header with the auto token
cu = database.cursor()
cu.execute('SELECT uname FROM [tiqit#authtokens] WHERE token = ?',
(os.environ['HTTP_X_TIQIT_TOKEN'], ))
uname = cu.fetchone()
if uname and uname[0]:
os.environ['REMOTE_USER'] = uname[0]
else:
print "Status: 409\n\n"
sys.exit()
cu.close()
database.commit()
def authenticate(user_id, password):
c = database.cursor()
# Load room.
c.execute('''
SELECT password
FROM users
WHERE user_id = ?
LIMIT 1'''
, (user_id,))
result_user = c.fetchone()
if result_user is None:
return False
return pwd_context.verify(password, result_user[0])
def main():
database = mysql.connector.connect(host="localhost",
user="******",
passwd="")
cursor = database.cursor()
database.create_database()
cursor.execute("USE me_database")
etl(cursor)
create_reports(cursor)
cursor.close()
def __init__(self, user_id):
c = database.cursor()
# Load room.
c.execute('''
SELECT name
FROM users
WHERE user_id = ?
LIMIT 1'''
, (user_id,))
result_user = c.fetchone()
if result_user is None:
raise NoSuchUserException
self.__user_id = user_id
self.__name = result_user[0]
def test_song_table(self):
try:
cur = database.cursor(self.conn)
except Exception as e:
raise
columns = ['id', 'title', 'upload_date', 'downloads', 'original_url', 'render_url', 'filename']
cur.execute("select * from information_schema.columns where table_name='song'")
result = cur.fetchall()
for name in columns:
hit = False
for r in result:
if name==r['column_name']:
hit = True
self.assertTrue(hit, "Column %s not found from song table" % (name) )
def create(room_id, rank, service, url, title, duration, start_time):
c = database.cursor()
c.execute('''
INSERT INTO room_queue (
room_id
, rank
, service
, url
, title
, duration
, start_time)
VALUES(?, ?, ?, ?, ?, ?, ?)'''
, (room_id, rank, service, url, title, duration, start_time))
item_id = c.lastrowid
database.commit()
return item_id
def generateToken():
chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'
token = ''.join([random.choice(chars) for i in range(128)])
# Connect to the DB
cu = database.cursor()
# Before we insert this new token, we should probably delete any old tokens
# generated for the same user on the same host
cu.execute('DELETE FROM [tiqit#authtokens] WHERE uname = ? AND who = ?',
(os.environ['REMOTE_USER'], os.environ['REMOTE_ADDR']))
cu.execute(
'INSERT INTO [tiqit#authtokens] (uname, token, created, who) '
'VALUES (?, ?, datetime("now"), ?)',
(os.environ['REMOTE_USER'], token, os.environ['REMOTE_ADDR']))
cu.close()
database.commit()
return token
def createTable(mode = 0, connection = None, database_spec = None, drop_tables = None):
if mode == 1:
menu.clearScreen()
print("\nAirline Booking Setup")
# connect to database
curs = database.cursor(connection)
try:
# drop pre-existing tables
checkTable(drop_tables, curs)
# create tables
execSQL(database_spec, curs)
connection.commit()
# insert single queries
# data = [('Quadbury', 101, 7.99, 0, 0)]
# cursInsert = connection.cursor()
# cursInsert.bindarraysize = 1
# cursInsert.setinputsizes(32, int, float, int, int)
# cursInsert.executemany("INSERT INTO TOFFEES(T_NAME, SUP_ID, PRICE, SALES, TOTAL) "
# "VALUES (:1, :2, :3, :4, :5)", data);
# cursInsert.close()
# read from a table
# # executing a query
# curs.execute("SELECT * from TOFFEES")
# # get all data and print it
# rows = curs.fetchall()
# for row in rows:
# print(row)
database.close(None,curs)
print("Setup Complete\n")
except cx_Oracle.DatabaseError as exc:
error, = exc.args
print(sys.stderr, "Oracle code:", error.code)
print(sys.stderr, "Oracle message:", error.message)
def collectNodeUsedCounts(trial,
core,
timing=None,
medium=None,
verification=None):
res = database.cursor().execute(
"""
SELECT
nodes.name,
COUNT(DISTINCT answers.solution) AS c1,
COUNT(*) AS c3
FROM nodes
LEFT JOIN answers ON (nodes.id = answers.src OR nodes.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE solutions.trial=? AND (timing=? OR %d) AND (medium=? OR %d) AND (verification=? OR %d)
GROUP BY nodes.id
ORDER BY c1 desc
""" % (timing == None, medium == None, verification == None),
(trial, timing, medium, verification)).fetchall()
listing = list(
map(
lambda r: [
r[0],
"%s (%0.2f%%)" % (r[1], r[1] * 100 / core["students"]),
"%s (%0.2f per student)" % (r[2], r[2] / core["students"])
], res))
if len(res) > 0:
foot = [
"Average",
"%0.2f ±%0.2f" %
(mean(res, lambda x: x[1]), pstdev(res, lambda x: x[1])),
"%0.2f ±%0.2f" %
(mean(res, lambda x: x[2]), pstdev(res, lambda x: x[2])),
]
else:
foot = None
return [
"listing", ["Node", "Used by n students", "Used in n connections"],
listing, foot
]
def get_table_rows(table_name):
if not re.match(r'^[A-Za-z][A-Za-z0-9_]*$', table_name):
print 'Invalid table name {}'.format(table_name)
flask.abort(http_status.BAD_REQUEST)
for arg in flask.request.args.keys():
if not re.match(r'^[A-Za-z][A-Za-z0-9_]*$', table_name):
print 'Invalid filter name {}'.format(arg)
flask.abort(http_status.BAD_REQUEST)
if 'limit' in flask.request.args:
try:
limit = int(flask.request.args['limit'])
except ValueError:
print 'Expected limit to be an integer; got {!r}'.format(flask.request.args['limit'])
flask.abort(http_status.BAD_REQUEST)
else:
limit = 20
# Extract dict to list to guarantee the keys are always iterated
# in the same order
filters = [ (k, v)
for k, v in flask.request.args.iteritems()
if k != 'limit' ]
query_template = 'select * from {} '.format(table_name) + \
''.join([ 'where {} = %s '.format(key) for k, v in filters ]) + \
'limit %s'
query_template_parameters = tuple([v for k, v in filters] + [limit])
print query_template
print query_template_parameters
print query_template % query_template_parameters
with database.cursor() as cursor:
cursor.execute(query_template, query_template_parameters);
column_names = cursor_column_names(cursor)
return [
dict(zip(column_names, row))
for row in cursor
]
def add_user(**args):
"""
Adds a user to the database.
Throws UserDetailException on failure.
"""
required = ["username", "password", "email"]
for r in required:
if not r in args:
raise UserDetailException("A required field is missing: %s" % r)
validate_username(args["username"])
validate_email(args["email"])
validate_password(args["password"])
conn = database.connection()
cur = database.cursor(conn)
pwhash = generate_hash(args["password"])
screen_name = args["username"]
query = """
INSERT INTO Member
(username, screen_name, password_hash, hash_salt, email)
VALUES (%s, %s, %s, %s, %s)
"""
try:
cur.execute(query,
(args["username"], screen_name,
pwhash[0], pwhash[1], args["email"]))
except Exception as e:
print("Can't insert user: " + str(e))
raise
conn.commit()
cur.close()
conn.close()
def collectEdgeCorrect(trial,
core,
timing=None,
medium=None,
verification=None):
nodes = database.listNodes(trial)
nm = {}
for n in nodes:
nm[n["id"]] = len(nm)
nodes = list(map(lambda n: n["name"], nodes))
res = database.cursor().execute(
"""
SELECT n1.id,n2.id,answers.*
FROM nodes AS n1, nodes AS n2
INNER JOIN answers ON (n1.id = answers.src AND n2.id = answers.dest)
LEFT JOIN solutions ON (answers.solution = solutions.id)
LEFT JOIN students ON (solutions.student = students.id)
WHERE n1.trial = ? AND n2.trial = ? AND (timing=? OR %d) AND (medium=? OR %d)
""" % (timing == None, medium == None),
(trial, trial, timing, medium)).fetchall()
table = [[[0, 0] for n in nodes] for n in nodes]
for row in res:
if int(row["verification"]) & verification == verification:
table[nm[row[0]]][nm[row[1]]][0] += 1
table[nm[row[0]]][nm[row[1]]][1] += 1
table = list(
map(lambda r: list(map(lambda x: x[0] / x[1]
if x[1] != 0 else 0, r)), table))
nodes.append("Average")
for row in table:
row.append("%0.2f ±%0.2f" % (mean(row), pstdev(row)))
newRow = []
for col in range(len(table[0]) - 1):
newRow.append("%0.2f ±%0.2f" %
(mean(table, lambda x: float(x[col])),
pstdev(table, lambda x: float(x[col]))))
table.append(newRow + [""])
table = reformat(table, formatPercent, isNumber)
return ["table", nodes, nodes, table]
def profileData(user):
# get db components
db = connection()
csr = db.cursor()
query = "select users.first_name, users.last_name, users.avatar from users where users.email='" + str(
user) + "';"
csr.execute(query)
profile_data = csr.fetchall()[0]
with open("./images/" + profile_data[2], "rb") as avatar_image:
avatar = base64.b64encode(avatar_image.read())
profile = {
"first_name": profile_data[0],
"last_name": profile_data[1],
"avatar": avatar
}
csr.close()
db.close()
return profile
import database
cursor = database.cursor()
# Assumes all blocks share the same space.
cursor.execute("""
SELECT
block_hash,
depth,
span_left,
span_right
FROM blocks
WHERE space=0
ORDER BY depth DESC
""")
flat = []
tree = []
root = None
for name, depth, left, right in cursor.fetchall():
node = [name, depth, left, right, []]
is_child = lambda n: n[1] == depth + 1 and n[2] >= left and n[3] <= right
node[-1].extend(filter(is_child, flat))
flat.append(node)
if name == 'root':
root = node
def display_node(nodes, indent=0):
for name, depth, left, right, children in nodes:
print ' ' * (indent * 4) + name
if children:
display_node(children, indent + 1)
# choose different random range to generate different clusters
def get_samples():
samples = []
results = db.Database().inquire(class_name = 'person')
for result in results:
temp = result[0].decode()
temp = temp[1:][:-1]
temp = temp.split(',')# now temp is a list, but the elements are string, convert them into int
temp = list(map(lambda x: int(x), temp))
samples.append(Sample(np.array(temp)))
return samples
samples = get_samples()
db = pymysql.connect("localhost","root","0305","objectfeature")
cursor = db.cursor()
sql_fetch = "select num_samples from centroids where name = 'Endong'"
cursor.execute(sql_fetch)
result = cursor.fetchall()
#temp = result[0][0].decode()[1:][:-1].split(',')
#centroid = Sample(np.array(list(map(lambda x: float(x), temp))))
#dis_sum = 0
#for sample in samples:
# dis_sum += sample.distance(centroid)
#dis_mean = dis_sum / len(samples)
#print(dis_mean)
# the following part is the training part
def cursor(cls):
try:
import database
return database.cursor()
except:
raise "cursor method undefined, no database connection could be made"
