[Python] Validating Pandas DataFrame columns and cell values

I have the following table cmf_data (here's the DDL I used to create it):

CREATE TABLE mydb.cmf_data (
cmf_data_id int IDENTITY(1,1) NOT NULL,
cmf_data_field_name varchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
cmf_data_field_data_type varchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT PK__client_m__BC8D4A1B625F6F4F PRIMARY KEY (cmf_data_id)
);


As you can see, this table stores field names and expected types. Here's an example output from running SELECT * FROM cmf_data;:

cmf_data_id|cmf_data_field_name|cmf_data_field_data_type|
-----------+-------------------+------------------------+
1|Foo |float |
2|Baz |float |
3|Fizz |datetime |
4|Buzz |string |


Here is my Excel spreadsheet, in CSV format:

Foo,Baz,Fizz,Buzz
23.5,44.18,'2022-06-18','Hello there',
24.621,7.3,'2023-01-07','How is it going',
149.0,712.0,'2022-02-09','What a nice day',
11.74,101.03,'2021-10-26','Thank you so much'


The idea is that the cmf_data dictates the expectations my code has on the column types found in the Excel spreadsheet. So if, say, the spreadsheet's Baz column contains a value that Pandas decides is a string, since the cmf_data table has Baz as a float, that should fail validation and raise an appropriate validation-related (or custom) error.

Here's what I am trying to do:

1. Use Pandas to read the cmf_data table into a DataFrame (say, cmf_data_df)
2. Use Pandas to read the spreadsheet into another DataFrame (say, excel_df)
3. If the columns in excel_df do not match the rows (based on cmf_field_name) defined in cmf_data_df, raise a validation exception (hence the Excel can't contain any columns not defined in the cmf_data table; and it can't omit any columns either; they must be exact)
4. Enforce the column types, defined in the cmf_data_df, upon the rows and cells of the excel_df; if any particular row contains a value that cannot be cast/converted or conform to the type expectations on its column, raise a validation exception

Hence, if my Excel/CSV looks like it does above, it should validate just fine; the Excel's columns match the expectations of the CMF Data table exactly, and all the rows' cell values match the expectations of the CMF Data table as well.

But if the Excel/CSV looks like:

Foo,Baz,Buzz
23.5,44.18,'Hello there',
24.621,7.3,'How is it going',
149.0,712.0,'What a nice day',
11.74,101.03,'Thank you so much'


We should get a validation exception because the column names do not match what is expected. Similarly, if the Excel/CSV looks like:

Foo,Baz,Fizz,Buzz
23.5,44.18,'2022-06-18','Hello there',
24.621,7.3,'2023-01-07','How is it going',
149.0,'I should fail the validation','2022-02-09','What a nice day',
11.74,101.03,'2021-10-26','Thank you so much'


We should also get a validation exception, because on row 3, the Baz value is a string, which violates its definition in the CMF Data table (expecting it to be a float).

My best attempt thus far is:

# read cmf_data_df from DB
params = urllib.parse.quote_plus("Driver={ODBC Driver 17 for SQL Server};"
f"Server={host};"
f"Database={database};"
f"uid={uid};pwd={pwd}")
engine = create_engine("mssql+pyodbc:///?odbc_connect={}".format(params), fast_executemany=True)

query = "SELECT * FROM mydb.cmf_data"
result = engine.execute(query)
data = result.fetchall()
col = list(result.keys())
cmf_data_df = pd.DataFrame(columns=col, data=data)

# read excel -- I'm actually pulling the file from S3
# which is what the 'obj['Body'].read()' is for
data = pd.read_excel(io.BytesIO(obj['Body'].read()), engine="openpyxl")
excel_df = pd.DataFrame(data)

# how to validate that the columns in 'excel_df' match rows definitions in 'cmf_data_df' ?

# how to perform row-level validation on all the types in 'excel_df'?


Can anyone point me in the right direction to piece the whole thing together correctly?

Continue reading...