CODEWITHSUNDEEP
pythonmysqlsqlsqlalchemy
MarcelKlockman
MarcelKlockman

Reputation: 117

Handling NULL values when using sqlalchemy-python

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?

Upvotes: 0

Views: 1671

Answers (1)

Iain Duncan
Iain Duncan

Reputation: 1772

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".

Upvotes: 1

Related Questions