Beispiel #1
0
def post():
    # 获取经纬坐标
    coordinate_lng = request.form.get('coordinate_lng', '')
    coordinate_lat = request.form.get('coordinate_lat', '')
    if coordinate_lng and coordinate_lat:
        # 先从数据库中 通过坐标获取地点,如果有直接返回,没有则保存
        venue = db.session.query(Venue).filter(
            Venue.coordinate_lng == coordinate_lng,
            Venue.coordinate_lat == coordinate_lat).first()

        if venue:
            data = {'id': venue.id}
            return jsonify({'code': 1, 'result': data})
        else:
            try:
                venue = Venue()
                venue.name = request.form.get('name', '')
                venue.address = request.form.get('address', '')
                venue.coordinate_lng = coordinate_lng
                venue.coordinate_lat = coordinate_lat
                venue.category = request.form.get('category', '')
                venue.link_man_mobile = request.form.get('link_man_mobile', '')

                venue = Venue().add(venue)
                data = {'id': venue.id}
                return jsonify({'code': 1, 'result': data})
            except Exception, e:
                current_app.logger.error(str(e))
                e_msg = ' venue save error '
                return jsonify({'code': 0, 'error': e_msg})
Beispiel #2
0
def create_venue_submission():
    error = False
    data = request.form

    try:
        venue = Venue()
        venue.name = data['name']
        venue.city = data['city']
        venue.state = data['state']
        venue.address = data['address']
        venue.phone = data.get('phone', '')
        venue.facebook_link = data.get('facebook_link', '')
        venue.genres = [
            VenueGenres(genre=GenreEnum[genre])
            for genre in data.getlist('genres')
        ]
        db.session.add(venue)
        db.session.commit()
    except:
        error = True
        db.session.rollback()
    finally:
        data = venue.to_dict()
        db.session.close()

    if not error:
        # on successful db insert, flash success
        flash(f'Venue {data["name"]} was successfully listed!',
              'alert-success')
    else:
        # on unsuccessful db insert, flash an error
        flash(
            f'An error occurred. Venue {data["name"]} could not be listed. \
              Does the venue already exist?', 'alert-danger')

    return render_template('pages/home.html')
Beispiel #3
0
def import_data_from_test_csv(db_session, db_uri):
    import pandas as pd
    from sqlalchemy import create_engine
    from app.models import City, Venue, Team, Official

    # read raw matches csv files
    df = pd.read_csv('test_files/matches.csv', parse_dates=['date'])

    # save cities and venues
    city_venue_df = df[['city', 'venue']].drop_duplicates()
    zip_city_venue = zip(city_venue_df['city'], city_venue_df['venue'])
    for cv in zip_city_venue:
        city = City()
        city.name = cv[0]
        db_session.add(city)
        db_session.commit()

        venue = Venue()
        venue.city_id = city.id
        venue.name = cv[1]
        db_session.add(venue)
        db_session.commit()

    # save teams
    team_set1 = set(df['team1'].drop_duplicates())
    team_set2 = set(df['team2'].drop_duplicates())
    all_teams = team_set1.union(team_set2)
    for team in all_teams:
        team_model = Team()
        team_model.name = team
        db_session.add(team_model)

    # save umpires
    umpire_set1 = set(df['umpire1'].drop_duplicates())
    umpire_set2 = set(df['umpire2'].drop_duplicates())
    all_umpires = umpire_set1.union(umpire_set2)
    for umpire in all_umpires:
        umpire_model = Official()
        umpire_model.name = umpire
        db_session.add(umpire_model)

    db_session.commit()

    # create a dict with key and name mappings for
    # cities, venues, teams and officials
    cities = db_session.query(City).all()
    cities_dict = {city.name: city.id for city in cities}
    venues = db_session.query(Venue).all()
    venues_dict = {venue.name: venue.id for venue in venues}
    teams = db_session.query(Team).all()
    teams_dict = {team.name: team.id for team in teams}
    officials = db_session.query(Official).all()
    officials_dict = {official.name: official.id for official in officials}

    # cities = df_matches['city'].drop_duplicates()
    # cities_dict = {x: y for y,x in enumerate(cities)}
    db_session.close()

    # change city, venue, team1, team2, umpire1, umpire2
    # names to their corresponding 'ids' from database
    df['city'] = df['city'].apply(lambda x: cities_dict[x])
    df['venue'] = df['venue'].apply(lambda x: venues_dict[x])
    df['team1'] = df['team1'].apply(lambda x: teams_dict[x])
    df['team2'] = df['team2'].apply(lambda x: teams_dict[x])
    df['toss_winner'] = df['toss_winner'].apply(lambda x: teams_dict[x])
    df['winner'] = df['winner'].apply(lambda x: teams_dict[x])
    df['umpire1'] = df['umpire1'].apply(lambda x: officials_dict[x])
    df['umpire2'] = df['umpire2'].apply(lambda x: officials_dict[x])

    # create sqlalchemy engine
    engine = create_engine(db_uri)

    # Insert dataframe into 'Matches' table
    df.to_sql('Matches', con=engine, if_exists='replace', index=False)
Beispiel #4
0
def load_csv_data_into_tables(db_session):
    from lib.src.d02_intermediate.clean_data import clean_null_values_in_csv
    from lib.src.d03_processing.get_cleaned_data import get_dataframe

    # clean the raw 'matches.csv' file
    clean_null_values_in_csv()

    # get pandas dataframe from the cleaned up csv
    df = get_dataframe()

    # save cities and venues
    city_venue_df = df[['city', 'venue']].drop_duplicates()
    zip_city_venue = zip(city_venue_df['city'], city_venue_df['venue'])
    for cv in zip_city_venue:
        city = City()
        city.name = cv[0]
        db_session.add(city)
        db_session.commit()

        venue = Venue()
        venue.city_id = city.id
        venue.name = cv[1]
        db_session.add(venue)
        # db_session.commit()

    # save teams
    teams_df = df['team1'].drop_duplicates()
    teams_list = [team for team in teams_df]
    for team in teams_list:
        team_model = Team()
        team_model.name = team
        db_session.add(team_model)

    # save umpires
    umpire_set1 = set(df['umpire1'].drop_duplicates())
    umpire_set2 = set(df['umpire2'].drop_duplicates())
    all_umpires = umpire_set1.union(umpire_set2)
    for umpire in all_umpires:
        umpire_model = Official()
        umpire_model.name = umpire
        db_session.add(umpire_model)

    db_session.commit()

    # create a dict with key and name mappings for
    # cities, venues, teams and officials
    cities = db_session.query(City).all()
    cities_dict = {city.name: city.id for city in cities}
    venues = db_session.query(Venue).all()
    venues_dict = {venue.name: venue.id for venue in venues}
    teams = db_session.query(Team).all()
    teams_dict = {team.name: team.id for team in teams}
    officials = db_session.query(Official).all()
    officials_dict = {official.name: official.id for official in officials}

    # cities = df_matches['city'].drop_duplicates()
    # cities_dict = {x: y for y,x in enumerate(cities)}
    # db_session.close()

    # change city, venue, team1, team2, umpire1, umpire2
    # names to their corresponding 'ids' from database
    df['city'] = df['city'].apply(lambda x: cities_dict[x])
    df['venue'] = df['venue'].apply(lambda x: venues_dict[x])
    df['team1'] = df['team1'].apply(lambda x: teams_dict[x])
    df['team2'] = df['team2'].apply(lambda x: teams_dict[x])
    df['toss_winner'] = df['toss_winner'].apply(lambda x: teams_dict[x])
    df['winner'] = df['winner'].apply(lambda x: teams_dict[x])
    df['umpire1'] = df['umpire1'].apply(lambda x: officials_dict[x])
    df['umpire2'] = df['umpire2'].apply(lambda x: officials_dict[x])

    # create sqlalchemy engine
    engine = db_session.get_bind()

    # Insert dataframe into 'Matches' table
    df.to_sql('Matches', con=engine, if_exists='replace', index=False)