Handling NULL values when using sqlalchemy-python

Question

I am trying to write data from a dataframe to a sandbox table in a sql database. Some example data from the df is below.

rank type id lp olp
0 A 823L02X 24.95 NULL 
1 B 823M00B 39.95 NULL
2 C 823M00B 39.95 12.95

The following code works fine so long as all data in the dataframe conforms to the dtype specified in its destination column.

connect_string = r'<user>:<password>@<host>/<databasename>'
db = create_engine('mysql+mysqldb://' + connect_string)
df.to_sql(name=<table_name>, con=db, if_exists='append', index=True)

In the table I am writing too, columns lp and olp have the dtype of decimal - however, there are some values in the dataframe that contain NULL values. These NULL values are throwing errors whenever I try to write to the table - I think sql treats them as strings instead of recognizing NULL.

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1366, "Incorrect decimal value: 'NULL' for column 'olp' at row 1")

If I were to export this dataframe to as csv, then manually upload it to the table using the import tool in HeidiSQL, the program automatically recognises NULL values, and there are no problems.

How can I achieve the same result using sqlalchemy?

Answer 1

SQLAlchemy normally expects you to use the Python value None for DB values of null. I expect all you need to is make sure your input values are correctly marshalled to Python's None before writing and it should "just work".