Пример #1
0
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
Пример #2
0
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.')
Пример #3
0
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()
Пример #4
0
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'])
Пример #5
0
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
Пример #6
0
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
Пример #7
0
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
Пример #8
0
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
Пример #9
0
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
Пример #10
0
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
Пример #11
0
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()
Пример #12
0
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()
Пример #13
0
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
Пример #14
0
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
Пример #15
0
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
Пример #16
0
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
Пример #17
0
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
Пример #18
0
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
Пример #19
0
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
Пример #20
0
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
Пример #21
0
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
Пример #22
0
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
Пример #23
0
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
Пример #24
0
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
Пример #25
0
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
Пример #26
0
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
Пример #27
0
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
Пример #28
0
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
Пример #29
0
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
Пример #30
0
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
Пример #31
0
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'])