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 _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"