Team members have produced a list of know database tables.

I'm going to try to represent those in machine-readable format, and run tests against the API for existence and row-count

Table Names Document

https://docs.google.com/spreadsheets/d/1LDDH-qxJunBqqkS1EfG2mhwgwFi7PylXtz3GYsGjDzA/edit#gid=933879858



In [5]:

    
import requests
import io
import pandas
from itertools import chain



In [ ]:

    
def makeurl(tablename,start,end):
    return "https://iaspub.epa.gov/enviro/efservice/{tablename}/JSON/rows/{start}:{end}".format_map(locals())



In [13]:

    
def table_count(tablename):
    url= "https://iaspub.epa.gov/enviro/efservice/{tablename}/COUNT/JSON".format_map(locals())
    out=requests.get(url)
    try:
        return out.json()[0]['TOTALQUERYRESULTS']
    except Exception as e:
        print(e)
        print(out.text)
        return -1



In [1]:

    
table_names=[
    "BREPORT_CYCLE",
    "RCR_HHANDLER",
    "RCR_BGM_BASIC",
    "PUB_DIM_FACILITY",
    "PUB_FACTS_SUBP_GHG_EMISSION",
    "PUB_FACTS_SECTOR_GHG_EMISSION",
    "PUB_DIM_SUBPART",
    "PUB_DIM_GHG",
    "PUB_DIM_SECTOR",
    "PUB_DIM_SUBSECTOR",
    "PUB_DIM_FACILITY",
    "AA_MAKEUP_CHEMICAL_INFO",
    "AA_SUBPART_LEVEL_INFORMATION",
    "AA_SPENT_LIQUOR_INFORMATION",
    "AA_FOSSIL_FUEL_INFORMATION",
    "AA_FOSSIL_FUEL_TIER_2_INFO",
    "AA_CEMS_DETAILS",
    "AA_TIER_4_CEMS_QUARTERLY_CO2",
    "PUB_DIM_FACILITY",
    "EE_CEMS_DETAILS",
    "EE_CEMS_INFO",
    "EE_FACILITY_INFO",
    "EE_NOCEMS_MONTHLYDETAILS",
    "EE_NOCEMSTIO2DETAILS",
    "EE_SUBPART_LEVEL_INFORMATION",
    "EE_TIER4CEMS_QTRDTLS",
    "PUB_DIM_FACILITY",
    "GG_FACILITY_INFO",
    "GG_NOCEMS_ZINC_DETAILS",
    "GG_SUBPART_LEVEL_INFORMATION",
    "PUB_DIM_FACILITY",
    "II_BIOGAS_REC_PROC",
    "II_CH4_GEN_PROCESS",
    "II_EQU_II1_OR_II2",
    "II_EQU_II4_INPUT",
    "II_EQUATION_II3",
    "II_EQUATION_II6",
    "II_EQUATION_II7",
    "II_SUBPART_LEVEL_INFORMATION",
    "II_PROCESS_DETAILS",
    "PUB_DIM_FACILITY",
    "NN_SUBPART_LEVEL_INFORMATION",
    "NN_NGL_FRACTIONATOR_METHODS",
    "NN_LDC_NAT_GAS_DELIVERIES",
    "NN_LDC_DETAILS",
    "PUB_DIM_FACILITY",
    "R_SUBPART_LEVEL_INFORMATION",
    "R_FACILITY_INFO",
    "R_SMELTING_FURNACE_INFO",
    "R_FEEDSTOCK_INFO",
    "PUB_DIM_FACILITY",
    "TT_SUBPART_GHG_INFO",
    "TT_LANDFILL_DETAILS",
    "TT_LF_GAS_COLL_DETAILS",
    "TT_WASTE_DEPTH_DETAILS",
    "TT_WASTESTREAM_DETLS",
    "TT_HIST_WASTE_METHOD",
    "PUB_DIM_FACILITY",
    "W_SUBPART_LEVEL_INFORMATION",
    "W_LIQUIDS_UNLOADING",
    "W_TRANSMISSION_TANKS",
    "W_PNEUMATIC_DEVICES",
    "W_WELL_COMPLETION_HYDRAULIC",
    "W_WELL_TESTING",
]

For each table, I want to

assert that it actually exists
get a rowcount



In [6]:

    
table_count(table_names[0])









    



https://iaspub.epa.gov/enviro/efservice/BREPORT_CYCLE/COUNT/JSON






    Out[6]:





7



In [15]:

    
%%time
table_counts={
    table_name:table_count(table_name)
    for table_name in table_names
}









    



CPU times: user 816 ms, sys: 172 ms, total: 988 ms
Wall time: 25.2 s



In [16]:

    
pandas.Series(table_counts)









    Out[16]:





AA_CEMS_DETAILS                        0
AA_FOSSIL_FUEL_INFORMATION          2560
AA_FOSSIL_FUEL_TIER_2_INFO         11100
AA_MAKEUP_CHEMICAL_INFO              659
AA_SPENT_LIQUOR_INFORMATION         1738
AA_SUBPART_LEVEL_INFORMATION        2636
AA_TIER_4_CEMS_QUARTERLY_CO2           0
BREPORT_CYCLE                          7
EE_CEMS_DETAILS                        0
EE_CEMS_INFO                           0
EE_FACILITY_INFO                      42
EE_NOCEMSTIO2DETAILS                  30
EE_NOCEMS_MONTHLYDETAILS             360
EE_SUBPART_LEVEL_INFORMATION         105
EE_TIER4CEMS_QTRDTLS                   0
GG_FACILITY_INFO                      35
GG_NOCEMS_ZINC_DETAILS               101
GG_SUBPART_LEVEL_INFORMATION          86
II_BIOGAS_REC_PROC                 29537
II_CH4_GEN_PROCESS                 51168
II_EQUATION_II3                      263
II_EQUATION_II6                      673
II_EQUATION_II7                      275
II_EQU_II1_OR_II2                    340
II_EQU_II4_INPUT                     275
II_PROCESS_DETAILS                   945
II_SUBPART_LEVEL_INFORMATION         788
NN_LDC_DETAILS                      2282
NN_LDC_NAT_GAS_DELIVERIES           9124
NN_NGL_FRACTIONATOR_METHODS         2970
NN_SUBPART_LEVEL_INFORMATION        3005
PUB_DIM_FACILITY                   50640
PUB_DIM_GHG                           14
PUB_DIM_SECTOR                        16
PUB_DIM_SUBPART                       42
PUB_DIM_SUBSECTOR                     69
PUB_FACTS_SECTOR_GHG_EMISSION     140318
PUB_FACTS_SUBP_GHG_EMISSION       167037
RCR_BGM_BASIC                    1801605
RCR_HHANDLER                     2391179
R_FACILITY_INFO                       68
R_FEEDSTOCK_INFO                     604
R_SMELTING_FURNACE_INFO              158
R_SUBPART_LEVEL_INFORMATION           77
TT_HIST_WASTE_METHOD              112136
TT_LANDFILL_DETAILS                 1215
TT_LF_GAS_COLL_DETAILS                36
TT_SUBPART_GHG_INFO                 3475
TT_WASTESTREAM_DETLS                3930
TT_WASTE_DEPTH_DETAILS                45
W_LIQUIDS_UNLOADING                 2040
W_PNEUMATIC_DEVICES                 4134
W_SUBPART_LEVEL_INFORMATION        21019
W_TRANSMISSION_TANKS                1888
W_WELL_COMPLETION_HYDRAULIC         2608
W_WELL_TESTING                      2040
dtype: int64



In [17]:

    
len(table_counts)









    Out[17]:





56

So all of these tables exist.



In [ ]: