Pydantic model inserts None values in Databricks Delta table as string type instead of null type

Question

I have the below pydantic model with 6 columns out of which 2 columns are nullable.

from pydantic import BaseModel
from typing import Optional

class Purchases(BaseModel):
    customer_id: int
    customer_name: str
    purchase_date: str
    city: str
    customer_nickname: Optional[str] = None
    customer_address_type: Optional[str] = None
 
    def insert_purchases(prchs: Purchases) -> None:
        insert_qry = f"""
        INSERT INTO cust_db.purchases(customer_id, customer_name, purchase_date, city, customer_nickname, customer_address_type)
        VALUES ({prchs['customer_id']}, 
        '{prchs['customer_name']}', 
        '{prchs['purchase_date']}', 
        '{prchs['city']}',
        '{prchs['customer_nickname']}',
        '{prchs['customer_address_type']}'
        )"""
        print(insert_qry)
        spark.sql(insert_qry)

My input is as follows:

purchase_input = {"customer_id": 3, "customer_name": "John Doe", "purchase_date": "2011-12-27 13:04:52", "city": "New York", "customer_nickname": None, "customer_address_type": None}

I performed the INSERT operation using the below lines:

prch_obj = Purchases
prch_obj.insert_purchases(purchase_input)

But both the columns customer_nickname and customer_address_type are getting a string type 'None' instead of null values:

INSERT INTO cust_db.purchases(customer_id, customer_name, purchase_date, city, customer_nickname, customer_address_type)
        VALUES (3, 
        'John Doe', 
        '2011-12-27 13:04:52', 
        'New York',
        'None',
        'None'
        )

The only way I'm able to load null values into these fields is by using CASE in my INSERT statement for those two fields:

def insert_purchases(prchs: Purchases) -> None:
    insert_qry = f"""
    INSERT INTO cust_db.purchases(customer_id, customer_name, purchase_date, city, customer_nickname, customer_address_type)
    VALUES ({prchs['customer_id']}, 
    '{prchs['customer_name']}', 
    '{prchs['purchase_date']}', 
    '{prchs['city']}',
    CASE WHEN '{prchs['customer_nickname']}' = 'None' THEN NULL else '{prchs['customer_nickname']}' END,
    CASE WHEN '{prchs['customer_address_type']}' = 'None' THEN NULL else '{prchs['customer_address_type']}' END
    )"""
    print(insert_qry)
    spark.sql(insert_qry)

which then correctly inserts null values:

But I do have many tables where I need handle the incoming None/NULL type values and I think using CASE is an overkill when I have to do it on hundreds of fields.

Is there a better way to achieve this?

sjors blom · Accepted Answer · 2025-09-19 14:07:09Z

0

When you use an f string you are converting everything to strings. Since str(None) == 'None' you will simply end up the string 'None'. There are more ways two handle this. (The best would in my opinion be some sort of orm like sqlalchemy to properly handle the data types). However if you just want a quick fix in python:

insert_qry = f"""
        INSERT INTO cust_db.purchases(customer_id, customer_name, purchase_date, city, customer_nickname, customer_address_type)
        VALUES ({purchase_input["customer_id"]}, 
        '{purchase_input["customer_name"]}', 
        '{purchase_input["purchase_date"]}', 
        '{purchase_input["city"]}',
        {f"'{purchase_input["customer_nickname"]}'" if purchase_input["customer_nickname"] else "NULL"},
        {f"'{purchase_input["customer_address_type"]}'" if purchase_input["customer_address_type"] else "NULL"},
        )"""

NOTE: In no means is this clean python code but it would work.

answered Sep 19 at 14:07

sjors blom

11 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

LearneR Sep 19 at 14:48

I get SyntaxError: f-string: f-string: unmatched '[' when I try this.

sjors blom Sep 20 at 16:18

You maybe have to take the nested f string outside and set the variables first. It also might handle the f string different depending on the python version.

Collectives™ on Stack Overflow

Pydantic model inserts None values in Databricks Delta table as string type instead of null type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related