def retrieve_genres(): conn = connect() c = conn.cursor() select_lib = "SELECT g.name AS genre, count(*) AS num FROM copy " \ "JOIN book b ON copy.bookid = b.bookid " \ "JOIN genre g ON b.genreid = g.genreid " \ "WHERE sent IS NULL " \ "GROUP BY g.name " \ "ORDER BY g.name" select_sent = "SELECT g.name AS genre, count(*) AS num FROM copy " \ "JOIN book b ON copy.bookid = b.bookid " \ "JOIN genre g ON b.genreid = g.genreid " \ "WHERE sent IS NOT NULL " \ "GROUP BY g.name " \ "ORDER BY g.name" c.execute(select_lib) data = c.fetchall() library = {} for row in data: library[row[0]] = row[1] c.execute(select_sent) data = c.fetchall() sent = {} for row in data: sent[row[0]] = row[1] return library, sent
def bk_login(copy): conn = connect() with conn.cursor() as cursor: s = "UPDATE copy SET sent = NULL WHERE copyid = %s" cursor.execute(s, [copy]) conn.commit() disconnect(conn)
def bk_logout(copy): conn = connect() with conn.cursor() as cursor: s = "UPDATE copy SET sent = %s WHERE copyid = %s" cursor.execute(s, [date.today(), copy]) conn.commit() disconnect(conn)
def get_books(sql): conn = connect() c = conn.cursor() select = sql c.execute(select) data = c.fetchall() books = {} for tuple in data: if tuple[0] in books.keys(): if tuple[2] not in books[tuple[0]]['authors']: books[tuple[0]]['authors'].append(tuple[2]) # if tuple[3] != '': # books[tuple[0]]['editors'].append(tuple[3]) else: d = { 'title': proper_case(tuple[1]).replace("^", "'"), 'authors': [tuple[2]], #'editors': [tuple[3]], 'genre': tuple[3], 'logged': tuple[4], 'id': tuple[0] } d['logged'] = d['logged'].strftime("%m/%d/%Y") books[tuple[0]] = d disconnect(conn) books = books.values() for b in books: bauth = b['authors'] authors = '' for a in bauth: authors += proper_case(a) if a != bauth[len(bauth) - 1]: authors += ", " b['authors'] = authors # bedit = b['editors'] # editors = '' # if None not in b['editors']: # for e in b['editors']: # editors += proper_case(e) # if e != bedit[len(bedit)-1]: # editors += ", " # else: # editors = 'N/A' # # b['editors'] = editors return books
def get_ithaca(): conn = connect() c = conn.cursor() select = "SELECT latitude, longitude FROM ithaca" c.execute(select) data = c.fetchall()[0] coords = {'name': "Ithaca", 'latitude': data[0], 'longitude': data[1]} return coords
def submit_book(title, authors, editors, genre, quant): conn = connect() with conn.cursor() as cursor: s = "SELECT bookid FROM book WHERE title = %s" cursor.execute(s, [title]) data = cursor.fetchall() if len(data) == 0: s = "SELECT genreid FROM genre WHERE name = %s" cursor.execute(s, [genre]) gid = cursor.fetchall()[0][0] i = "INSERT INTO book(title, genreid, location) VALUES(%s, %s, 'SAC') RETURNING bookid" cursor.execute(i, [title, gid]) id = cursor.fetchall()[0][0] for auth in authors: s = "SELECT authorid FROM author WHERE name = %s" cursor.execute(s, [auth]) adata = cursor.fetchall() if len(adata) == 0: i = "INSERT INTO author(name) VALUES(%s) RETURNING authorid" cursor.execute(i, [auth]) aid = cursor.fetchall()[0][0] else: aid = adata[0][0] i = "INSERT INTO written_by(bookid, authorid) VALUES(%s, %s)" cursor.execute(i, [id, aid]) for edit in editors: s = "SELECT editorid FROM editor WHERE name = %s" cursor.execute(s, [edit]) edata = cursor.fetchall() if len(edata) == 0: i = "INSERT INTO editor(name) VALUES(%s) RETURNING editorid" cursor.execute(i, [edit]) eid = cursor.fetchall()[0][0] else: eid = edata[0][0] i = "INSERT INTO edited_by(bookid, editorid) VALUES(%s, %s)" cursor.execute(i, [id, eid]) else: id = data[0][0] for i in range(int(quant)): i = "INSERT INTO copy(bookid, logged) VALUES(%s, %s)" cursor.execute(i, [id, date.today()]) conn.commit() disconnect(conn)
def get_all_titles(): result = [] try: with connect().cursor() as cursor: sql = "select title from book order by title asc" cursor.execute(sql) data = cursor.fetchall() finally: for t in data: result.append(proper_case(t[0].replace("^", "'"))) return json.dumps(result)
def get_genres(): result = {} try: with connect().cursor() as cursor: sql = "select name, color, label from genre order by name asc" cursor.execute(sql) data = cursor.fetchall() finally: for t in data: result[t[0]] = [proper_case(t[1]), proper_case(t[2])] return json.dumps(result)
def get_all_editors(): result = [] try: with connect().cursor() as cursor: sql = "select name from editor where name not like 'VARIOUS' order by name asc" cursor.execute(sql) data = cursor.fetchall() finally: for t in data: result.append(proper_case(t[0].replace("^", "'"))) return json.dumps(result)
def retrieve_mailings(): conn = connect() c = conn.cursor() select = "SELECT sent, count(*) FROM copy WHERE sent IS NOT NULL GROUP BY sent;" c.execute(select) data = c.fetchall() mailings = {} for row in data: mailings[str(row[0])] = row[1] return mailings
def get_titles(query): result = [] try: with connect().cursor() as cursor: q = query.replace("'", "^") sql = "select title from book where title like UPPER('%" + q + "%')" cursor.execute(sql) data = cursor.fetchall() finally: for t in data: result.append(proper_case(t[0].replace("^", "'"))) return json.dumps({"suggestions": result})
def main(): connection, channel = connect() def callback(ch, method, properties, body): logger.info(' [x] Received %s', body) channel.basic_consume( queue='hello', on_message_callback=callback, auto_ack=True, ) logger.info(' [x] Waiting for messages. To exit press CTRL+C') channel.start_consuming()
def main(): connection, channel = connect() for n in range(100): time.sleep(5) body = f'Hello {n}' logger.info(' [x] Sending %s', body) channel.basic_publish( exchange='', routing_key='hello', body=body, ) connection.close()
def check_password(user, password): un = user.get_user() pw = user.get_pwhash() conn = connect() c = conn.cursor() select = "SELECT password FROM admin WHERE username = %s" c.execute(select, [un]) data = c.fetchall() if data == []: return False else: return bcrypt.check_password_hash(pw, password)
def get_user_by_name(username): conn = connect() c = conn.cursor() select = "SELECT id, username, password FROM admin WHERE username = %s" c.execute(select, [username]) data = c.fetchall() if data == []: return None else: user = User() user.set_id(data[0][0]) user.set_user(data[0][1]) user.set_password(data[0][2]) return user
def import_library(): conn = connect() try: #values = read_library_csv('catalog.csv') #insert_books(conn, values) #insert_copies(conn, values) #insert_authors(conn, values) values = read_prison_csv('packages_confirmed.csv') import_facilities(conn, values) conn.commit() except DatabaseError as e: print(f'Error {e}') sys.exit(1) disconnect(conn)
def retrieve_facilities(): conn = connect() c = conn.cursor() select = "SELECT name, latitude, longitude FROM facility" c.execute(select) data = c.fetchall() facilities = [] for tuple in data: dictionary = { 'name': tuple[0], 'latitude': tuple[1], 'longitude': tuple[2] } facilities.append(dictionary) for f in facilities: f['name'] = f['name'].replace("^", "'") return facilities
import sys from app.connect import connect if __name__ == '__main__': print("AI script is alive.") if len(sys.argv) != 3: print("Illegal number of arguments") else: connect(sys.argv[1], int(sys.argv[2])) print('AI script complete')