def createNewManager(username, firstname, lastname, password, usertype, status, company, address_street, address_city, address_state, address_zip, theaterName="NULL"): connection = util.db_connection() try: with connection.cursor() as cursor: createNewManager = "INSERT INTO users values (%s, %s, %s, MD5(%s), %s, %s)" cursor.execute( createNewManager, (username, firstname, lastname, password, usertype, status)) cursor.execute("SET foreign_key_checks = 0") addNewEmployee = "INSERT INTO employee values (%s, %s, %s, %s, %s, %s, %s, %s)" cursor.execute( addNewEmployee, (username, address_street, address_city, address_state, address_zip, "Manager", company, theaterName)) cursor.execute("SET foreign_key_checks = 1") connection.commit() finally: connection.close() return True
def get_vitals(symbol, current_date): conn = None try: one_date = date_only(current_date) conn = db_connection() """ read from support_resistance table """ cur = conn.cursor(cursor_factory=extras.DictCursor) query = """SELECT ROW_TO_JSON(a) FROM (SELECT * FROM vitals where symbol=%s) a""" # execute the SELECT statement cur.execute(query, (symbol, )) # get the generated id back result = cur.fetchone() conn.commit() if result == None or result[0]['published_on'] != one_date.strftime( "%Y-%m-%d"): return False, {} else: return True, result except (Exception, DatabaseError) as error: print(error) return False, {} finally: if conn is not None: conn.close() print('Database connection closed.')
def edit_attraction(attraction_id, name, description, price, street_num, street, city, state, zip, country, transit): connection = util.db_connection() try: with connection.cursor() as cursor: address_id = "SELECT attraction.address_id FROM attraction JOIN address using (address_id) WHERE attraction.attraction_id=%s" update_address = """ UPDATE address SET street_number=%s, street=%s, city=%s, state=%s, zip=%s, country=%s WHERE address_id=%s """ address_data = (street_num, street, city, state, zip, country, address_id) cursor.execute(update_address, address_data) update_attraction = """ UPDATE attraction SET name=%s, transit_stop=%s, description=%s, price=%s WHERE attraction_id=%s """ attraction_data = (name, transit, description, price, attraction_id) cursor.execute(update_attraction, attraction_data) connection.commit() finally: connection.close()
def list_of_books(): conn = util.db_connection() c = conn.cursor() books = [book for book in c.execute("SELECT * FROM %s;"%TBL_BOOKS)]; print type(books[0]) print dir(books[0]) return render_template('book-list.html', books=books, username=session['username'])
def getTheaterCapacity(theater): connection = util.db_connection() try: with connection.cursor() as cursor: getTheaterCapacity = "select capacity from theater where thname=%s;" cursor.execute(getTheaterCapacity, (theater)) result = cursor.fetchone() finally: connection.close() return result
def checkTheaterExists(company, theaterName): connection = util.db_connection() try: with connection.cursor() as cursor: checkTheaterExists = "select thname from theater where comname=%s and thname=%s" cursor.execute(checkTheaterExists, (company, theaterName)) result = cursor.fetchall() finally: connection.close() return result
def get_user_cc_ids(user_id): connection = util.db_connection() try: with connection.cursor() as cursor: get_cc_ids = "select credit_card_id, cc_number, expiry from user join credit_card on user.user_id = credit_card.user_id where user.user_id = %s" cursor.execute(get_cc_ids, user_id) result = cursor.fetchall() finally: connection.close() return result
def getManagerCompany(username): connection = util.db_connection() try: with connection.cursor() as cursor: getManagerCompany = "select comname from employee where username=%s" cursor.execute(getManagerCompany, (username)) result = cursor.fetchone() finally: connection.close() return result
def getTheaterNames(): connection = util.db_connection() try: with connection.cursor() as cursor: getTheaterNames = "select distinct thname from theater;" cursor.execute(getTheaterNames) result = cursor.fetchall() finally: connection.close() return result
def getCompanyNames(): connection = util.db_connection() try: with connection.cursor() as cursor: getCompanyNames = "select * from company" cursor.execute(getCompanyNames) result = cursor.fetchall() finally: connection.close() return result
def delete_user(user_id): connection = util.db_connection() try: with connection.cursor() as cursor: del_user = "******" cursor.execute(del_user, user_id) connection.commit() finally: connection.close()
def delete_attraction(attraction_id): connection = util.db_connection() try: with connection.cursor() as cursor: del_attraction = "DELETE FROM attraction WHERE attraction_id = %s" cursor.execute(del_attraction, attraction_id) connection.commit() finally: connection.close()
def login_user(username, password): connection = util.db_connection() try: with connection.cursor() as cursor: login_user = "******" cursor.execute(login_user, (username, password)) result = cursor.fetchall() finally: connection.close() return result
def get_city(trip_id): connection = util.db_connection() try: with connection.cursor() as cursor: get_city = "select city from trip where trip_id = %s" cursor.execute(get_city, trip_id) result = cursor.fetchone() finally: connection.close() return result
def get_attraction_id(city_id): connection = util.db_connection() try: with connection.cursor() as cursor: get_attraction_id = "select * from attraction join address using (address_id) where city = %s" cursor.execute(get_attraction_id, city_id) result = cursor.fetchall() finally: connection.close() return result
def get_city_ids(): connection = util.db_connection() try: with connection.cursor() as cursor: get_city_ids = "select distinct city from attraction join address using (address_id);" cursor.execute(get_city_ids) result = cursor.fetchall() finally: connection.close() return result
def belongs_to(trip_id, user_id): connection = util.db_connection() try: with connection.cursor() as cursor: get_trip = "select trip_id from trip where trip_id = %s and user_id = %s" cursor.execute(get_trip, (trip_id, user_id)) result = cursor.fetchone() finally: connection.close() return result is not None
def getAllPlayingMovies(): connection = util.db_connection() try: with connection.cursor() as cursor: getAllPlayingMovies = """select distinct movname from movieplay""" cursor.execute(getAllPlayingMovies) result = cursor.fetchall() finally: connection.close() return result
def creditCardCount(): connection = util.db_connection() try: with connection.cursor() as cursor: creditCardCount = "select username, count(*) as cnt" \ + " from customercreditcard group by username;" cursor.execute(creditCardCount) result = cursor.fetchall() finally: connection.close() return result
def getMovieReleaseDate(name): connection = util.db_connection() try: with connection.cursor() as cursor: getMovieReleaseDate = "select movreleasedate from movie where movname=%s" cursor.execute(getMovieReleaseDate, (name)) result = cursor.fetchone() finally: connection.close() return result
def getUserCreditCards(username): connection = util.db_connection() try: with connection.cursor() as cursor: getUserCreditCards = "select creditcardnum from customercreditcard where username=%s" cursor.execute(getUserCreditCards, (username)) result = cursor.fetchall() finally: connection.close() return result
def getAllUserData(): connection = util.db_connection() try: with connection.cursor() as cursor: getAllUserData = "select * from users;" cursor.execute(getAllUserData) result = cursor.fetchall() finally: connection.close() return result
def findMangerTheater(username): connection = util.db_connection() try: with connection.cursor() as cursor: findMangerTheater = "select thname from employee where username=%s" cursor.execute(findMangerTheater, (username)) result = cursor.fetchone() finally: connection.close() return result
def checkMovieExists(name, date): connection = util.db_connection() try: with connection.cursor() as cursor: checkMovieExists = "select * from movie where movname=%s and movreleasedate=%s" cursor.execute(checkMovieExists, (name, date)) result = cursor.fetchone() finally: connection.close() return result
def get_user_trips(user_id): connection = util.db_connection() try: with connection.cursor() as cursor: get_trips = "select trip_id, city, start_date from trip where user_id = %s order by start_date" cursor.execute(get_trips, user_id) result = cursor.fetchall() finally: connection.close() return result
def getManagers(): connection = util.db_connection() try: with connection.cursor() as cursor: getManagers = "select username from employee where employeetype=%s" cursor.execute(getManagers, ("Manager")) result = cursor.fetchall() finally: connection.close() return result
def getAllMovies(): connection = util.db_connection() try: with connection.cursor() as cursor: getAllMovies = """select * from movie""" cursor.execute(getAllMovies) result = cursor.fetchall() finally: connection.close() return result
def checkAddressExists(street, city, state, zipcode): connection = util.db_connection() try: with connection.cursor() as cursor: checkAddressExists = "select * from employee where manstreet=%s and mancity=%s and manstate=%s and manzipcode=%s" cursor.execute(checkAddressExists, (street, city, state, zipcode)) result = cursor.fetchone() finally: connection.close() return result
def get_theaterdata(): connection = util.db_connection() try: with connection.cursor() as cursor: get_theaterdata = "select * from theater;" cursor.execute(get_theaterdata) result = cursor.fetchall() finally: connection.close() return result
def get_all_users(): connection = util.db_connection() try: with connection.cursor() as cursor: get_email = "SELECT user_id, email, name, suspended, is_admin FROM user order by user_id" cursor.execute(get_email) result = cursor.fetchall() finally: connection.close() return result
def serve_book(book_id): conn = util.db_connection() c = conn.cursor() row = c.execute("SELECT * FROM %s WHERE id=?;"%TBL_BOOKS, (book_id,)).fetchone() if 'library' not in session: session['library'] = [] session['library'].append(book_id) r = redis.Redis(db=REDIS_DB_NUM) r.sadd('book:readers:%d'%book_id, session['username']) for user in r.smembers('book:readers:%d'%book_id): if user != session['username']: r.rpush('user:book:stream:%s:%d'%(user, book_id) , json.dumps({"type":"reading", "user":session['username']})) for user in r.smembers('book:readers:%d'%book_id): r.rpush('user:book:stream:%s:%d'%(session['username'], book_id) , json.dumps({"type":"reading", "user":user})) content = open(BOOKS_BASE_PATH + row['path'], 'r').read() return render_template('book-view.html', book_content=content.decode('utf-8'), info=row, username=session['username'])