Delaware Data, can't insert data. NOT NULL constraint fails

[tlyon3]

I'm working on the Delaware county scraper. I've got all the data ready to go, but I can't d.put into the test database. It's saying that NOT NULL constraint failed: covid_observations.variable_id.

The variables I have are:

  variables = {
        "FirstDose": CMU(
            category="total_vaccine_initiated",
            measurement="new",
            unit="doses"
        ),
        "SecondDose": CMU(
            category="total_vaccine_completed",
            measurement="new",
            unit="doses"
        ),
        "TotalDoses": CMU(
            category="total_vaccine_doses_administered",
            measurement="new",
            unit="doses"
        )
    }

Am I doing something wrong with the variables? Did I miss something?

[sglyon]

You should try to use the find_unknown_variable_id method on the scraper

What happens when you do?

from can_tools.scrapers import DelawareCountyVaccine
from can_tools.models import create_dev_engine

engine, Session = create_dev_engine()
d = DelawareCountyVaccine()
df = d.fetch_normalize()

d.find_unknown_variable_id(df, engine)

[tlyon3]

In [1]: from can_tools.scrapers import *

In [2]: from can_tools.models import create_dev_engine

In [3]: engine, Session = create_dev_engine(verbose=False)

In [4]: d = DelawareCountyVaccine()

In [5]: df = d.fetch_normalize()

In [6]: d.find_unknown_variable_id(df, engine)
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-6-eb3ad034a09b> in <module>
----> 1 d.find_unknown_variable_id(df, engine)

~/Work/Valorum/covid/can-scrapers/can_tools/scrapers/base.py in find_unknown_variable_id(self, engine, df)
    476
    477     def find_unknown_variable_id(self, engine: Engine, df: pd.DataFrame):
--> 478         variables = pd.read_sql("select * from covid_variables", engine)
    479         merged = df.merge(variables, on=["category", "measurement", "unit"], how="left")
    480         bad = merged["id"].isna()

~/anaconda3/envs/can-scrapers/lib/python3.8/site-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    481
    482     if isinstance(pandas_sql, SQLiteDatabase):
--> 483         return pandas_sql.read_query(
    484             sql,
    485             index_col=index_col,

~/anaconda3/envs/can-scrapers/lib/python3.8/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1725
   1726         args = _convert_params(sql, params)
-> 1727         cursor = self.execute(*args)
   1728         columns = [col_desc[0] for col_desc in cursor.description]
   1729

~/anaconda3/envs/can-scrapers/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1677             cur = self.con
   1678         else:
-> 1679             cur = self.con.cursor()
   1680         try:
   1681             cur.execute(*args, **kwargs)

~/anaconda3/envs/can-scrapers/lib/python3.8/site-packages/pandas/core/generic.py in __getattr__(self, name)
   5137             if self._info_axis._can_hold_identifiers_and_holds_name(name):
   5138                 return self[name]
-> 5139             return object.__getattribute__(self, name)
   5140
   5141     def __setattr__(self, name: str, value) -> None:

AttributeError: 'DataFrame' object has no attribute 'cursor'

In [7]:

[sglyon]

whoops -- seems like swapped order of args. Try passing engine first

[sglyon]

In general you need to make sure the category measurement unit (CMU) lines up with an entry in this file: https://github.com/covid-projections/can-scrapers/blob/e3a933ec7fb22ccdbb7c0ddd1a9545c967dfa213/can_tools/bootstrap_data/covid_variables.csv

[tlyon3]

added total_vaccine_completed,new,doses and total_vaccine_initiated,new,doses to covid_variables.csv and now works