Python pandas.read_sql() Examples

def export_filing_document_search(search_query_id: int, output_file_path: str):
    """
    Export a filing document search to a CSV file.
    :param search_query_id:
    :param output_file_path:
    :return:
    """
    # Local imports
    import django.db
    import pandas

    # Create query string
    query_string = """SELECT f.accession_number, f.date_filed, f.company_id, ci.name, ci.sic, ci.state_location, 
f.form_type, fd.sequence, fd.description, fd.sha1, sqt.term, sqr.count
FROM sec_edgar_searchqueryresult sqr
JOIN sec_edgar_searchqueryterm sqt ON sqt.id = sqr.term_id
JOIN sec_edgar_filingdocument fd ON fd.id = sqr.filing_document_id
JOIN sec_edgar_filing f ON f.id = fd.filing_id
JOIN sec_edgar_companyinfo ci ON ci.company_id = f.company_id AND ci.date = f.date_filed 
WHERE sqr.search_query_id = {0}
ORDER BY f.date_filed, f.company_id
""".format(search_query_id)
    query_df = pandas.read_sql(query_string, django.db.connection)
    query_df.to_csv(output_file_path, encoding="utf-8", index=False) 

def load_sqlite_table(database_path, table_name):
    """Returns (table, connection). table is a pandas DataFrame."""
    conn = sqlite3.connect(database_path)
    try:
        df = pd.read_sql("SELECT * FROM %s" % table_name, conn)
        #  print("\nLoading %s table from SQLite3 database." % table_name)
    except DatabaseError as e:
        if 'no such table' in e.args[0]:
            print("\nNo such table: %s" % table_name)
            print("Create the table before loading it. " +
                  "Consider using the create_sqlite_table function")
            raise DatabaseError
        else:
            print(e)
            raise Exception("Failed to create %s table. Unknown error." %
                            table_name)
    return df, conn 

def plants_utils_ferc1(pudl_engine):
    """
    Build a dataframe of useful FERC Plant & Utility information.

    Args:
        pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
            PUDL database.

    Returns:
        pandas.DataFrame: A DataFrame containing useful FERC Form 1 Plant and
        Utility information.

    """
    pu_df = pd.merge(
        pd.read_sql("plants_ferc1", pudl_engine),
        pd.read_sql("utilities_ferc1", pudl_engine),
        on="utility_id_ferc1")
    return pu_df 

def __execute_sqla(self, exec_str, repl=None):
        if repl is not None:
            exec_str = exec_str.replace('?', '{}').format(
                    *[self.__insert_sanitize(item) for item in repl])

        # DEBUG
        engine = self.__engine
        try:
            res = engine.execute('SELECT * FROM rg_complex_dates;')
        except Exception:
            pass

        try:
            df =  pd.read_sql(exec_str, self.__engine, 
                              parse_dates=self.__parse_datetimes)
            if self.__parse_datetimes:
                utils.fix_pandas_datetimes(df, self.__parse_datetimes)
            return df.to_records(index=False)
        except sqla.exc.ResourceClosedError:
            # Query didn't return results
            return None 

def fileshare():
    connect_db ()
    try:
        FS_list = dp.read_sql ( 'select "Home Directory", "Profile Path" from UserTB', connection ).drop_duplicates()
        if not FS_list.values.any:
            pass
        else:
            FS_list = FS_list.drop_duplicates()
            FS_list = FS_list.to_string(header=False, index=False)
            FS_list = FS_list.upper()
            FS_list = FS_list.replace("\n", "").replace(" ", "")
            FS_list = FS_list.split("\\")
            uname_list = dp.read_sql ( 'select Name from ComputerTB', connection )
            uname_list = uname_list.to_string ( header=False, index=False )
            uname_list = uname_list.split ()
            l3 = [x for x in FS_list if x in uname_list]
            l3 = filter ( None, l3 )
            final = dp.DataFrame ( l3 )
            final = final.drop_duplicates ()
            final.to_sql("FileServer", connection, index=False, if_exists="replace")
    except ValueError:
        pass 

def fetch_backend(key):
    prefix = ioconf.get("prefix", "")
    key = prefix + key
    backend = ioconf.get("backend")
    path = ioconf.get("path")
    if backend == "csv":
        key = key + ".csv"

    try:
        if backend == "csv":
            df0 = pd.read_csv(os.path.join(path, key))
        elif backend == "sql":
            df0 = pd.read_sql(key, path)
        else:
            raise ValueError("no %s option for backend" % backend)

        return df0

    except (FileNotFoundError, exc.ProgrammingError, KeyError):
        return None 

def plant_in_service(ferc1_meta, ferc1_table, ferc1_years):
    """Creates a DataFrame of the fields of plant_in_service_ferc1.

    Args:
        ferc1_meta (sa.MetaData): a MetaData object describing the cloned FERC
            Form 1 database
        ferc1_table (str): The name of the FERC 1 database table to read, in
            this case, the plant_in_service_ferc1 table.
        ferc1_years (list): The range of years from which to read data.

    Returns:
        pandas.DataFrame: A DataFrame containing all plant_in_service_ferc1
        records.

    """
    f1_plant_in_srvce = ferc1_meta.tables[ferc1_table]
    f1_plant_in_srvce_select = (
        sa.sql.select([f1_plant_in_srvce])
        .where(f1_plant_in_srvce.c.report_year.in_(ferc1_years))
    )

    return pd.read_sql(f1_plant_in_srvce_select, ferc1_meta.bind) 

def _fetch_sql(self, path):
        """
        fetch the information and pricetable from sql, not recommend to use manually,
        just set the fetch label to be true when init the object

        :param path:  engine object from sqlalchemy
        """
        try:
            content = pd.read_sql("xa" + self.code, path)
            pricetable = content.iloc[1:]
            commentl = [float(com) for com in pricetable.comment]
            self.price = pricetable[["date", "netvalue", "totvalue"]]
            self.price["comment"] = commentl
            self.name = json.loads(content.iloc[0].comment)["name"]
        except exc.ProgrammingError as e:
            # print('no saved copy of %s' % self.code)
            raise e 

def show_inscope_MACs(self):
			qr = dp.read_sql('select BSSID from inscope_accessPoints', con)
			result = qr.to_string(formatters={'BSSID':'{{:<{}s}}'.format(qr['BSSID'].str.len().max()).format}, header=False, index=False)
			return str(result) 

def get_db_utils_eia(pudl_engine):
    """Get a list of all EIA Utilities appearing in the PUDL DB."""
    db_utils_eia = (
        pd.read_sql("utilities_entity_eia", pudl_engine).
        loc[:, ["utility_id_eia", "utility_name_eia"]].
        pipe(pudl.helpers.strip_lower, columns=["utility_name_eia"]).
        astype({"utility_id_eia": int}).
        drop_duplicates("utility_id_eia").
        sort_values("utility_id_eia").
        set_index("utility_id_eia")
    )
    return db_utils_eia 

def read_data_from_mysql(sql):
    """
    根据sql语句查询数据，并以pandas.DataFrame格式返回
    """
    conn = None
    try:
        conn = mysql.connector.connect(host=const.HOST, user=const.USER, password=const.PASSWORD, database=const.DATABASE,
                                       use_unicode=True, charset='utf8')
        df = pd.read_sql(sql, conn)
        return df
    except Exception as e:
        print(e)
    finally:
        conn.close() 

def load_data_from_postgresql(mes='',client=cilent_pg()):
    res=pd.read_sql(mes,client)
    return res 

def survey_df(ab_config, city, start_date):
    sql_survey_ids = """
        select survey_id, survey_date, comment
        from survey s, search_area sa
        where s.search_area_id = sa.search_area_id
        and sa.name = %(city)s
        and s.survey_date > '{start_date}'
        and s.status = 1
        order by survey_id
    """.format(start_date=start_date)
    conn = ab_config.connect()
    df = pd.read_sql(sql_survey_ids, conn,
                     params={"city": city})
    conn.close()
    return(df) 

def total_listings(ab_config, city_view):
    sql = """select s.survey_id "Survey",
    survey_date "Date", count(*) "Listings"
    from {city_view} r join survey s
    on r.survey_id = s.survey_id
    group by 1, 2
    order by 1
    """.format(city_view=city_view)
    conn = ab_config.connect()
    df = pd.read_sql(sql, conn)
    conn.close()
    return df 

def plants_steam_ferc1(pudl_engine):
    """Select and joins some useful fields from the FERC Form 1 steam table.

    Select the FERC Form 1 steam plant table entries, add in the reporting
    utility's name, and the PUDL ID for the plant and utility for readability
    and integration with other tables that have PUDL IDs.

    Also calculates ``capacity_factor`` (based on ``net_generation_mwh`` &
    ``capacity_mw``)

    Args:
        pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
            PUDL database.

    Returns:
        pandas.DataFrame: A DataFrame containing useful fields from the FERC
        Form 1 steam table.

    """
    steam_df = (
        pd.read_sql("plants_steam_ferc1", pudl_engine)
        .drop('id', axis="columns")
        .merge(plants_utils_ferc1(pudl_engine),
               on=['utility_id_ferc1', 'plant_name_ferc1'])
        .assign(capacity_factor=lambda x: x.net_generation_mwh / (8760 * x.capacity_mw),
                opex_fuel_per_mwh=lambda x: x.opex_fuel / x.net_generation_mwh,
                opex_nonfuel_per_mwh=lambda x: (x.opex_production_total - x.opex_fuel) / x.net_generation_mwh)
        .pipe(pudl.helpers.organize_cols, ['report_year',
                                           'utility_id_ferc1',
                                           'utility_id_pudl',
                                           'utility_name_ferc1',
                                           'plant_id_pudl',
                                           'plant_id_ferc1',
                                           'plant_name_ferc1'])
    )
    return steam_df 

def plants_pumped_storage(ferc1_meta, ferc1_table, ferc1_years):
    """Creates a DataFrame of f1_plants_pumped_storage records with plant names.

    Args:
        ferc1_meta (sa.MetaData): a MetaData object describing the cloned FERC
            Form 1 database
        ferc1_table (str): The name of the FERC 1 database table to read, in
            this case, the f1_plants_pumped_storage table.
        ferc1_years (list): The range of years from which to read data.

    Returns:
        pandas.DataFrame: A DataFrame containing f1_plants_pumped_storage
        records that have plant names.

    """
    f1_pumped_storage = ferc1_meta.tables[ferc1_table]

    # Removing the empty records.
    # This reduces the entries for 2015 from 272 records to 27.
    f1_pumped_storage_select = (
        sa.sql.select([f1_pumped_storage])
        .where(f1_pumped_storage.c.plant_name != '')
        .where(f1_pumped_storage.c.report_year.in_(ferc1_years))
    )

    return pd.read_sql(f1_pumped_storage_select, ferc1_meta.bind) 

def __init__(self, db_path):
        assert os.path.isfile(db_path), \
            "ISDi db path {!r} was not found.".format(db_path)
        self.app_info_conn = sqlite3.connect(db_path, check_same_thread=False)
        self.df = pd.read_sql('select * from clients_notes', self.app_info_conn) #, params=(,)) 

def app_details(self, serialno, appid):
        try:
            d = pd.read_sql('select * from apps where appid=?',
                            self.app_info_conn,
                            params=(appid,))
            if not isinstance(d.get('permissions', ''), list):
                d['permissions'] = d.get('permissions', pd.Series([]))
                d['permissions'] = d['permissions'].fillna('').str.split(', ')
            if 'descriptionHTML' not in d:
                d['descriptionHTML'] = d['description']
            dfname = self.dump_path(serialno)

            if self.device_type == 'ios':
                ddump = self.parse_dump
                if not ddump:
                    ddump = parse_dump.IosDump(dfname)
            else:
                ddump = parse_dump.AndroidDump(dfname)

            info = ddump.info(appid)

            print('BEGIN INFO')
            print("info={}".format(info))
            print('END INFO')
            # FIXME: sloppy iOS hack but should fix later, just add these to DF
            # directly.
            if self.device_type == 'ios':
                # TODO: add extra info about iOS? Like idevicediagnostics
                # ioregentry AppleARMPMUCharger or IOPMPowerSource or
                # AppleSmartBattery.
                d['permissions'] = pd.Series(info.get('permissions',''))
                #d['permissions'] = [info.get('permissions','')]
                d['title'] = pd.Series(info.get('title',''))
                del info['permissions']
            print("AppInfo: ", info, appid, dfname, ddump)
            return d.fillna(''), info
        except KeyError as ex:
            print("Exception:::", ex)
            return pd.DataFrame([]), dict() 

def get_unmapped_plants_eia(pudl_engine):
    """Identify any as-of-yet unmapped EIA Plants."""
    plants_utils_eia = (
        pd.read_sql("""SELECT DISTINCT plant_id_eia, utility_id_eia
                       FROM plants_eia860;""", pudl_engine).
        dropna().
        astype({"plant_id_eia": int,
                "utility_id_eia": int}).
        drop_duplicates().
        # Need to get the name of the utility, to merge with the ID
        merge(get_db_utils_eia(pudl_engine).reset_index(),
              on="utility_id_eia")
    )
    plant_capacity_mw = (
        pd.read_sql("SELECT * FROM generators_eia860;", pudl_engine).
        groupby(["plant_id_eia"])[["capacity_mw"]].agg(sum).
        reset_index()
    )
    db_plants_eia = get_db_plants_eia(pudl_engine).set_index("plant_id_eia")
    mapped_plants_eia = get_mapped_plants_eia().set_index("plant_id_eia")
    unmapped_plants_idx = (
        db_plants_eia.index.
        difference(mapped_plants_eia.index)
    )
    unmapped_plants_eia = (
        db_plants_eia.loc[unmapped_plants_idx].
        merge(plants_utils_eia, how="left", on="plant_id_eia").
        merge(plant_capacity_mw, how="left", on="plant_id_eia").
        loc[:, ["plant_id_eia", "plant_name_eia",
                "utility_id_eia", "utility_name_eia",
                "state", "capacity_mw"]].
        astype({"utility_id_eia": "Int32"})  # Woo! Nullable Integers FTW!
    )

    return unmapped_plants_eia 

def show_inscope_ssids(self):
			qr = dp.read_sql('select ESSID from INSCOPE_SSIDS', con)
			result = qr.to_string(formatters={'ESSID':'{{:<{}s}}'.format(qr['ESSID'].str.len().max()).format}, header=False, index=False)
			return str(result) 

def main(self, t2, where):
			global result
			result = dp.DataFrame()
			for tb in tables:
				try:
					qr = dp.read_sql('' + t2 +' '+ tb + where +'', con)
					result = result.append(qr)
				except pandas.io.sql.DatabaseError:
					continue

			result = result.drop_duplicates()
			result = tabulate(result, showindex=False) 

def show_table(self, option):
			try:
				if option in ["AP"]:
					qr = dp.read_sql('select * from accessPoints', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["proberequests"]:
					qr = dp.read_sql('select * from ProbeRequests', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["proberesponses"]:
					qr = dp.read_sql('select * from ProbeResponses', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["inscope_AP"]:
					qr = dp.read_sql('select * from inscope_accessPoints', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["inscope_proberequests"]:
					qr = dp.read_sql('select * from inscope_proberequests', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["inscope_proberesponses"]:
					qr = dp.read_sql('select * from inscope_proberesponses', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["hiddenssids"]:
					qr = dp.read_sql('select * from Hidden_SSID', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["EAP"]:
					qr = dp.read_sql('select * from EAP', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				elif option in ["LOOT"]:
					qr = dp.read_sql('select * from LOOT', con)
					print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
				else:
					print colors.RD + "Error: Invalid query, please try again.\n" + colors.NRM
			except pandas.io.sql.DatabaseError:
				print colors.RD + "Error: Table does not exist or is empty, please try again.\n" + colors.NRM 

def Custom_Queries(self, option):
			try:
				CQ = option
				if 'AP' in CQ:
					CQ = CQ.replace("AP","accessPoints")
				if 'proberequests' in CQ:
					CQ = CQ.replace("proberequests","ProbeRequests")
				if 'proberesponses' in CQ:
					CQ = CQ.replace("proberesponses","ProbeResponses")
				qr = dp.read_sql(CQ, con)
				print (tabulate(qr.drop_duplicates(), showindex=False, headers=qr.columns, tablefmt="psql"))
			except pandas.io.sql.DatabaseError:
				pass 

def main(workspace):
	varibles = ['CHAN', 'VENDOR', 'ENC']
	msg = ['Channel', 'Vendor', 'Encryption']
	title = ['Channel - SniffAir noticed that the following APs are sitting on channels that are outside the norm from the rest of the APs in the network.', 'Vendor - SniffAir has discovered the following APs broadcasting an inscope ESSID but is from a different vendor then the rest of the network.', 'Encryption - Whoa! SniffAir discovered an AP running with a different type of encryption then the rest of the APs in that network!']
	ws = workspace
	q = queries()
	ws1 = q.db_connect(ws)
	inscope=dp.read_sql('select * from INSCOPE_SSIDS', ws1)
	result = inscope.to_string(formatters={'ESSID':'{{:<{}s}}'.format(inscope['ESSID'].str.len().max()).format}, header=False, index=False)
	ssidresult = str(q.show_inscope_ssids())
	ssidresult = ssidresult.split('\n')
	j = 0
	for v in varibles:
		try:
			result1 = dp.DataFrame()
			for SSID in ssidresult:
				query = dp.read_sql("select * from accessPoints where ESSID = '"+ SSID +"'", ws1)
				RA = query[v].value_counts()
				RA_result = RA.reset_index(name="count").query("count <2")["index"].tolist()
				for r in RA_result:
					y = query.loc[query[v].isin([str(r)])]
					result1 = y.append(result1)
			if result1.empty:
					print "[*] Nothing Suspicious regarding the " + msg[j] + "information"
					pass
			else:
				print title[j]
				print (tabulate(result1,showindex=False, headers=query.columns, tablefmt="psql"))+"\n"
			j +=1
		except ValueError:
			continue 

def main(workspace, path, name):
	table_name = ['accessPoints', 'ProbeRequests', 'ProbeRequests', 'EAP', 'Hidden_SSID', 'inscope_accessPoints', 'inscope_ProbeRequests', 'inscope_ProbeResponses']
	sheet_name = ['AccessPoints', 'ProbeRequests', 'ProbeRequests', 'EAP', 'Hidden_SSID', 'Inscope_AccessPoints', 'Inscope_ProbeRequests', 'Inscope_ProbeResponses']
	ws = workspace
	q = queries()
	ws1 = q.db_connect(ws)
	writer = dp.ExcelWriter(path+name+'.xlsx', engine='xlsxwriter')
	j = 0
	print "Exporting: "+path+name+'.xlsx'
	for tbn in table_name:
		try:
			td = dp.read_sql('select * from '+tbn+'', ws1)
			if td.empty:
				pass
				j +=1
				print colors.RD + "[-]" + colors.NRM + " Skipping: " + sheet_name[j] + ". No Data in table."
			else:
				td.to_excel(writer, sheet_name=''+sheet_name[j]+'', index=False)
				j +=1
				print colors.GRN + "[+]" + colors.NRM + " Exporting: " + sheet_name[j] + "."
		except ValueError:
			continue
		except pandas.io.sql.DatabaseError:
			continue
	writer.save()
	print "Export Completed" 

def boiler_generator_assn_eia860(pudl_engine, start_date=None, end_date=None):
    """Pull all fields from the EIA 860 boiler generator association table.

    Args:
        pudl_engine (sqlalchemy.engine.Engine): SQLAlchemy connection engine
            for the PUDL DB.
        start_date (date-like): date-like object, including a string of the
            form 'YYYY-MM-DD' which will be used to specify the date range of
            records to be pulled.  Dates are inclusive.
        end_date (date-like): date-like object, including a string of the
            form 'YYYY-MM-DD' which will be used to specify the date range of
            records to be pulled.  Dates are inclusive.

    Returns:
        pandas.DataFrame: A DataFrame containing all the fields from the EIA
        860 boiler generator association table.

    """
    pt = pudl.output.pudltabl.get_table_meta(pudl_engine)
    bga_eia860_tbl = pt['boiler_generator_assn_eia860']
    bga_eia860_select = sa.sql.select([bga_eia860_tbl])

    if start_date is not None:
        start_date = pd.to_datetime(start_date)
        bga_eia860_select = bga_eia860_select.where(
            bga_eia860_tbl.c.report_date >= start_date
        )
    if end_date is not None:
        end_date = pd.to_datetime(end_date)
        bga_eia860_select = bga_eia860_select.where(
            bga_eia860_tbl.c.report_date <= end_date
        )
    out_df = (
        pd.read_sql(bga_eia860_select, pudl_engine)
        .assign(report_date=lambda x: pd.to_datetime(x.report_date))
        .drop(['id'], axis='columns')
    )
    return out_df 

def boiler_generator_assn(pudl_engine, start_date=None, end_date=None):
    """Pulls the more complete PUDL/EIA boiler generator associations.

    Args:
        pudl_engine (sqlalchemy.engine.Engine): SQLAlchemy connection engine
            for the PUDL DB.
        start_date (date): Date to begin retrieving data.
        end_date (date): Date to end retrieving data.

    Returns:
        pandas.DataFrame: A DataFrame containing the more complete PUDL/EIA
        boiler generator associations.

    """
    pt = pudl.output.pudltabl.get_table_meta(pudl_engine)
    bga_eia_tbl = pt['boiler_generator_assn_eia860']
    bga_eia_select = sa.sql.select([bga_eia_tbl])

    if start_date is not None:
        start_date = pd.to_datetime(start_date)
        bga_eia_select = bga_eia_select.where(
            bga_eia_tbl.c.report_date >= start_date
        )
    if end_date is not None:
        end_date = pd.to_datetime(end_date)
        bga_eia_select = bga_eia_select.where(
            bga_eia_tbl.c.report_date <= end_date
        )
    out_df = (
        pd.read_sql(bga_eia_select, pudl_engine)
        .assign(report_date=lambda x: pd.to_datetime(x.report_date))
    )
    return out_df 

def main(workspace):
	ws = workspace
	q = queries()
	ws1 = q.db_connect(ws)
	query=dp.read_sql('select * from accessPoints', ws1)
	inscope=dp.read_sql('select * from INSCOPE_SSIDS', ws1)
	result = inscope.to_string(formatters={'ESSID':'{{:<{}s}}'.format(inscope['ESSID'].str.len().max()).format}, header=False, index=False)
	ENC = "OPEN"
	PWR = "-100"
	notssids = result.replace('\n','\' and ESSID not like \'')
	count = 1
	print "AP Hunter - Displays Access Points within a specific range, using a specific encrpytion type. These may be benign."
	print "-------------------------------------------------------------------------------------------------------------------------------------------"
	while count <=2:
		try:
			print "ENC currently set to: " + ENC + " and PWR currently set to: " + PWR +". Press Enter to see these results or to set the values either type ENC or PWR and then the value. Note that when setting the PWR you must include a \'-\'"
			input = raw_input(" >>")
			varible = input.split(' ')		
			if varible[0] == 'ENC':
				ENC = varible[1]
			if varible[0] == 'PWR':
				PWR = varible[1]
			if ENC and PWR:
				AP_HT = dp.read_sql("select * from accessPoints where ESSID not like '"+ notssids +"' and  ENC = '"+ENC+"' and  PWR >='"+PWR+"'", ws1)
				if AP_HT.empty:
					print colors.RD + "Query returned no  valid results, please try again.\n" + colors.NRM
				else:
					print (tabulate (AP_HT, showindex=False, headers=query.columns, tablefmt="psql"))
				print "To exit press Ctl+C"
		except KeyboardInterrupt:
			count = 3
			print "\033[1A" 

def plants_small(ferc1_meta, ferc1_table, ferc1_years):
    """Creates a DataFrame of f1_small for records with minimum data criteria.

    Args:
        ferc1_meta (sa.MetaData): a MetaData object describing the cloned FERC
            Form 1 database
        ferc1_table (str): The name of the FERC 1 database table to read, in
            this case, the f1_small table.
        ferc1_years (list): The range of years from which to read data.

    Returns:
        pandas.DataFrame: A DataFrame containing f1_small records that have
        plant names and non zero demand, generation, operations,
        maintenance, and fuel costs.
    """
    from sqlalchemy import or_

    f1_small = ferc1_meta.tables[ferc1_table]
    f1_small_select = (
        sa.sql.select([f1_small, ])
        .where(f1_small.c.report_year.in_(ferc1_years))
        .where(f1_small.c.plant_name != '')
        .where(or_((f1_small.c.capacity_rating != 0),
                   (f1_small.c.net_demand != 0),
                   (f1_small.c.net_generation != 0),
                   (f1_small.c.plant_cost != 0),
                   (f1_small.c.plant_cost_mw != 0),
                   (f1_small.c.operation != 0),
                   (f1_small.c.expns_fuel != 0),
                   (f1_small.c.expns_maint != 0),
                   (f1_small.c.fuel_cost != 0)))
    )

    return pd.read_sql(f1_small_select, ferc1_meta.bind) 

def fuel(ferc1_meta, ferc1_table, ferc1_years):
    """Creates a DataFrame of f1_fuel table records with plant names, >0 fuel.

    Args:
        ferc1_meta (sa.MetaData): a MetaData object describing the cloned FERC
            Form 1 database
        ferc1_table (str): The name of the FERC 1 database table to read, in
            this case, the f1_fuel table.
        ferc1_years (list): The range of years from which to read data.

    Returns:
        :class:`pandas.DataFrame`: A DataFrame containing f1_fuel records that
        have plant_names and non-zero fuel amounts.

    """
    # Grab the f1_fuel SQLAlchemy Table object from the metadata object.
    f1_fuel = ferc1_meta.tables[ferc1_table]
    # Generate a SELECT statement that pulls all fields of the f1_fuel table,
    # but only gets records with plant names and non-zero fuel amounts:
    f1_fuel_select = (
        sa.sql.select([f1_fuel])
        .where(f1_fuel.c.fuel != '')
        .where(f1_fuel.c.fuel_quantity > 0)
        .where(f1_fuel.c.plant_name != '')
        .where(f1_fuel.c.report_year.in_(ferc1_years))
    )
    # Use the above SELECT to pull those records into a DataFrame:
    return pd.read_sql(f1_fuel_select, ferc1_meta.bind)