Handling NULL Values and Data Types
What you'll learn: How to safely manage NULL values returned from databases and convert SQL data types into Python-compatible formats.
The NULL Challenge
When you fetch data from a database, SQL's NULL (representing missing or unknown values) translates to Python's None. This is usually seamless, but it can cause issues if you're not careful—trying to perform math on None will raise errors, and aggregate functions might behave unexpectedly.
Think of NULL as an empty box: SQL knows it's empty, and Python receives it as None, but your code needs to check before trying to use what's "inside."
Type Conversion Between SQL and Python
Different database systems map their types to Python types automatically:
- SQL
INTEGER→ Pythonint - SQL
REAL/FLOAT→ Pythonfloat - SQL
TEXT/VARCHAR→ Pythonstr - SQL
DATE/TIMESTAMP→ Pythondatetime(often, depending on driver) - SQL
NULL→ PythonNone
Example handling:
cursor.execute("SELECT name, age FROM users WHERE id = 1")
row = cursor.fetchone()
name, age = row # Unpack tuple
if age is None:
print(f"{name} has no age recorded")
else:
print(f"{name} is {age} years old")
When converting to DataFrames, pandas handles None by converting to NaN for numeric columns, making it easier to use methods like fillna() or dropna().
Defensive Practices
Always anticipate NULL values in your queries. Check if value is None before operations, or use default values during unpacking. When building DataFrames, leverage pandas' robust NULL-handling tools rather than manual iteration.
Key Takeaway: SQL NULL becomes Python None—always check for it before operations, and understand how your database driver maps SQL types to Python types to avoid runtime surprises.