def show_user():
    model.connect()
    user_id = request.args.get("id")
    user = model.get_user_by_id(user_id)
    # user_profile = model.user_name.get_posts()

    return render_template("user_profile.html", user= user)

def write_all_user_predictions_to_sql():
	# user_index_to_id, movie_id_to_index = load_json_indexes()
	# user_id_to_index = load_user_id_to_index_index()
	session = model.connect()
	existing_ratings = get_existing_ratings_from_file()
	for user_index in xrange(70000): #fixme
		write_one_user_prediction_to_sql(user_index, session, existing_ratings)

def start():
    u = Updater( model.connect() )
    u.next([
        create_table_link_projection,
        create_table_response_cache,
    ])
    u.execute()

def load_demographics():
    with open('seed_data/SlimmerData_Consolidated.csv', 'rU') as csvfile:
        reader = csv.reader(csvfile, dialect='excel')
        for row in reader:
            print "row:", row
            # row = troubleshooting.  If issue, see where.
            try:
                session=model.connect()
                demo_obj = model.Demographic()

                demo_obj.zipcode = row[0]
                demo_obj.popdensity = float(row[1])
                demo_obj.pctemployed = float(row[2])
                demo_obj.pctmnf = float(row[3])
                demo_obj.pctlogistics = float(row[4])
                demo_obj.pctit = float(row[5])
                demo_obj.pctprof = float(row[6])
                demo_obj.hhincq10 = int(row[7])
                demo_obj.hhincq30 = int(row[8])
                demo_obj.hhincq50 = int(row[9])
                demo_obj.hhincq70 = int(row[10])
                demo_obj.hhincq90 = int(row[11])
                demo_obj.hhincq95 = int(row[12])
                demo_obj.pctheatelec = float(row[13])

                session.add(demo_obj)
                session.commit()
            except:
                print "Error for row data:", row
                f = open('log_file.txt','a')
                f.write("\nError. Failure for row:"+str(row))
                f.close

def load_geographics():
    with open('seed_data/zip_code_database.csv', 'rb') as csvfile:
        reader = csv.reader(csvfile, dialect='excel')
        for row in reader:
            print ("row:", row)
            # row = troubleshooting.  If issue, see where.
            try:
                session=model.connect()
                geo_obj = model.Geographic()

                geo_obj.zipcode = row[0]
                geo_obj.type_addy = row[1]
                geo_obj.primary_city = row[2]
                geo_obj.acceptable_cities = row[3]
                geo_obj.unacceptable_cities = row[4]
                geo_obj.state = row[5]
                geo_obj.county = row[6]
                geo_obj.timezone = row[7]
                geo_obj.area_codes = row[8]
                geo_obj.latitude = float(row[9])
                geo_obj.longitude = float(row[10])
                geo_obj.world_region = row[11]
                geo_obj.country = row[12]
                geo_obj.decommissioned = row[13]
                geo_obj.estimated_population = int(row[14])
                geo_obj.notes = row[15]

                session.add(geo_obj)
                session.commit()
            except:
                print "Error for row data:", row
                f = open('log_file.txt','a')
                f.write("\nError.  failure for row:"+str(row))
                f.close

def query_CAISODemand_hrly_Series():
    """specifically gets demand data"""

    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    s = model.connect()

    demand_obj = s.execute('SELECT time_start, mw_demand FROM "HistoricCAISODemands" WHERE caiso_tac=\'CA ISO-TAC\' and time_start between \'2014-01-01 07:00:00.000000\' and \'2015-01-01 00:00:00.000000\' ')
    demand_entry = demand_obj.fetchall()
    demand_df = DataFrame(demand_entry)
    demand_df.columns = ['time_start','mw_demand']

    dict_with_datetime_keys = { }

    for idx,row in enumerate(demand_df.values):
        time_start = row[0]

        # check date, since logs show we're missing a few
        if check_if_bad_date(time_start)!=True:

            # turn dict into a series.  will auto-index on dict keys
            mw_demand = row[1]
            dict_with_datetime_keys[time_start] = mw_demand

    return Series(dict_with_datetime_keys)

def query_CAISOProdByFuel_Series(ea_fuel):
    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    s = model.connect()

    ea_fuel_obj = s.execute('SELECT time_start, fuel_type, mw_gen FROM "HistoricCAISOProdByFuels" WHERE fuel_type=\'%s\' and time_start between \'2014-01-01 07:00:00.000000\' and \'2015-01-01 00:00:00.000000\'  ' % ea_fuel)
    ea_fuel_entry = ea_fuel_obj.fetchall()
    ea_fuel_df = DataFrame(ea_fuel_entry)
    ea_fuel_df.columns = ['time_start', 'fuel_type', 'mw_gen']

    dict_with_datetime_keys = { }

    for idx,row in enumerate(ea_fuel_df.values):
        time_start = row[0]

        # check date, since logs show we're missing a few
        if check_if_bad_date(time_start)!=True:
            mw_gen = row[2]
            dict_with_datetime_keys[time_start] = mw_gen

    # turn dict into a series.  will auto-index on dict keys
    return Series(dict_with_datetime_keys)

def query_CAISONetImports_hrly_Series():
    """specifically gets import data"""

    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    s = model.connect()

    imports_obj = s.execute('SELECT time_start, sum(mw_imports) FROM "HistoricCAISONetImports" where time_start between \'2014-01-01 07:00:00.000000\' and \'2015-01-01 00:00:00.000000\' GROUP BY time_start ')
    imports_entry = imports_obj.fetchall()
    imports_df = DataFrame(imports_entry)
    imports_df.columns = ['time_start','mw_demand']

    dict_with_datetime_keys = { }

    for idx,row in enumerate(imports_df.values):
        time_start = row[0]

        # check date, since logs show we're missing a few
        if check_if_bad_date(time_start)!=True:

            # turn dict into a series.  will auto-index on dict keys
            mw_imports = row[1]
            dict_with_datetime_keys[time_start] = mw_imports

    return Series(dict_with_datetime_keys)

def insert_row_db(date, hr, adict):
    """imports model, and inserts web scraped data into the db"""

    # add parent directory to the path, so can import model.py
    #  need model in order to update the database when this task is activated by cron
    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    session = model.connect()


    for k,v in adict.items():
        fuel_obj = model.HistoricCAISOProdByFuel()

        fuel_obj.date = datetime.strptime(date,'%Y%m%d')
        fuel_obj.time_start = hr
        fuel_obj.fuel_type = k
        fuel_obj.mw_gen = v

        session.add(fuel_obj)
        print fuel_obj

    session.commit()

def retrieve_from_db_usa():
    """imports model, pulls mwh production data from db, and places into pandas df.
    Also pulls state for each plant_name, and places into dict."""

    # add parent directory to the path, so can import model.py
    #  need model in order to update the database when this task is activated by cron
    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    s = model.connect()

    # retrive DECEMBER production data, for all turbines at all power plants in California
    USA_gen_dec13_obj = s.execute('SELECT plant_name, state, fuel_type, dec_mwh_gen FROM "ProdGensDec2013" ')
    USA_gen_dec13_data = USA_gen_dec13_obj.fetchall()
    df_dec2013 = DataFrame(USA_gen_dec13_data)
    df_dec2013.columns = ['plant_name', 'state', 'fuel_type', 'dec_mwh_gen']

    # retrive JAN-NOV 2014 production data, for all turbines at all power plants in USA
    USA_gen_2014_obj = s.execute('SELECT plant_name, state, fuel_type, jan_mwh_gen, feb_mwh_gen, mar_mwh_gen, apr_mwh_gen, may_mwh_gen, jun_mwh_gen, jul_mwh_gen, aug_mwh_gen, sep_mwh_gen, oct_mwh_gen, nov_mwh_gen FROM "ProdGens" ')
    USA_gen_2014_data = USA_gen_2014_obj.fetchall()
    df_2014 = DataFrame(USA_gen_2014_data)
    df_2014.columns = ['plant_name', 'state', 'fuel_type', 'jan_mwh_gen', 'feb_mwh_gen', 'mar_mwh_gen', 'apr_mwh_gen', 'may_mwh_gen', 'jun_mwh_gen', 'jul_mwh_gen', 'aug_mwh_gen', 'sep_mwh_gen', 'oct_mwh_gen', 'nov_mwh_gen']

    return df_dec2013, df_2014

def insert_row_imports_db(date, list_of_dicts):
    """Takes in a list of dicts, with each list item equal to a timepoint. inserts into HistoricCAISODemand"""

    # add parent directory to the path, so can import model.py
    #  need model in order to update the database when this task is activated by cron

    import os
    parentdir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    os.sys.path.insert(0,parentdir)

    import model
    session = model.connect()

    from datetime import datetime

    for timept_dict in list_of_dicts:
        imports_obj = model.HistoricCAISONetImport()

        opr_date = timept_dict['opr_date']
        imports_obj.date = datetime.strptime(opr_date,'%Y-%m-%d')

        imports_obj.time_start = timept_dict['time_start']
        imports_obj.time_end = timept_dict['time_end']

        imports_obj.resource = (timept_dict['resource']).strip()

        imports_obj.mw_imports = float(timept_dict['mw_imports'])

        session.add(imports_obj)

    session.commit()

    def setServiceParent(self, parent):
        log.msg("Starting DB Status handler")
        self.orig_parent = parent
        base.StatusReceiverMultiService.setServiceParent(self, parent)

        # Skip doing anything if we're just doing a checkconfig.  We don't want to
        # potentially change the state of the database on a checkconfig.
        if isinstance(parent, checkconfig.ConfigLoader):
            return

        # Keep a local reference to the session maker On a buildbot reconfig,
        # model.Session will be reset to None, and we might get
        # stepStarted/stepFinished notifications while the reconfig is
        # happening.
        try:
            self.Session = model.connect(self.dburl, pool_recycle=60)

            # Let our subscribers know about the database connection
            # This gives them the opportunity to set up their own tables, etc.
            for sub in self.subscribers:
                if hasattr(sub, 'databaseConnected'):
                    try:
                        sub.databaseConnected(model.metadata.bind)
                    except:
                        log.msg("DBERROR: Couldn't notify subscriber %s of database connection" % sub)
                        log.err()

            self.setup()
        except:
            if sys.exc_info()[0] is not sqlalchemy.exc.OperationalError:
                log.msg("DBERROR: Couldn't connect to database")
                log.err()
            self.lostConnection()

def main(session):
    # You'll call each of the load_* functions with the session as an argument
    # load_users(session) # comment this out when seed users have been loaded
    # load_movies(session) # comment this out when seed movies have been loaded
    # load_ratings(session) # comment this out when seed ratings have been loaded

if __name__ == "__main__":
    s = model.connect()
    main(s)

 def __init__(self, src_path, output, ext_pool='.pdf', ignore_hidden=True):
     self.db = model.connect()
     if not os.path.exists(media_path):
         os.makedirs(media_path)
     self.ext_pool = ext_pool
     self.ignore_hidden = ignore_hidden
     self.src_path = src_path
     self.output = output
     self.flag = True

def main():
    # You'll call each of the load_* functions with the session as an argument
    session = model.connect()
    load_user(session)
    print "user loaded"
    load_item(session)
    print "movies loaded"
    load_data(session)
    print "ratings loaded"

def main(session):
    # You'll call each of the load_* functions with the session as an argument
    session = model.connect()
    # load_users(session)
    # print "loaded users"
    load_movies(session)
    print "loaded movies"
    load_ratings(session)
    print "loaded ratings"

def main(session):
    # when running for real, remove echo = true
    # You'll call each of the load_* functions with the session as an argument
     # load_movies(session, 'seed_data/u.item')
    #load_ratings(session, 'seed_data/u.data')
    # load_users(session,'seed_data/u.user')

if __name__ == "__main__":
    s= model.connect()
    main(s)

def load_users(session):
    # use u.user
    with open ('seed_data/u.user', 'rb') as csvfile:
        reader = csv.reader(csvfile)
        session = model.connect()
        for row in reader: 
            row = row.pop().split("|")
            user = model.User (None, None, row[1], row[4])
            user.id = row[0]
            session.add(user)
        session.commit()

def display_provider():
	"""Given a unique identifier for a doctor, display a page with the doctor's 
	contact information"""

	npi = request.args.get("id")

	session = model.connect()

	provider = session.query(model.Provider).get(npi)

	return render_template('provider_page.html', provider = provider)

def before_request():
	engine, DB = model.connect()
	g.db = DB()

	id = session.get('id', None)
	if id:
		g.me = g.db.query(Person).filter_by(id = id).first()
	else:
		g.me = Person()
		g.db.add(g.me)
		g.db.commit()
		session['id'] = g.me.id