Posted in Python

Handling NULL values in dynamic SQL insert statements using Python

   September 29, 2023
python @ Freshers.in

In this we are dynamically creating and executing SQL insert statements to add rows from a DataFrame to a Snowflake table. The DataFrame df may contain None values, representing SQL NULL. We iterate over rows of the DataFrame, and for each value in a row, we determine whether it is None. If it’s None, we append ‘NULL’ (without quotes) to our values list; if it’s a numeric value, we convert it to a string and append it as-is; and if it’s another non-numeric value, we convert it to a string and append it enclosed in single quotes. After processing all values in a row, we join them using commas and insert them into the predefined SQL string. If an error occurs while executing the SQL statement, it gets caught and printed. The code can indeed be modified to handle None values and ensure that if a column value is None, it is represented as NULL without quotes in the SQL string.

for _, row in df.iterrows():
    cols = ','.join(df.columns)
    
    vals = []
    for col in df.columns:
        val = row[col]
        if val is None:
            vals.append('NULL')
        elif isinstance(val, (int, float)):  # if val is numeric type, keep it as it is.
            vals.append(str(val))
        else:  # if val is non-numeric type, convert it to string and surround with quotes.
            vals.append(repr(str(val)))
    
    vals_str = ','.join(vals)
    query = f"INSERT INTO freshers_schema.{table}({cols}) VALUES ({vals_str})"
    print(query)
    
    try:
        sf_cursor.execute(query)
    except Exception as e:
        print(f"Failed to execute query: {query}. Error: {e}")

Suppose you have a DataFrame df like this:

   name  age
0  Wilson   30
1  None     25
2  Babu   None

For the first row, the produced SQL string will be something like:

INSERT INTO dqm.table(name, age) VALUES ('Wilson', 30)

For the second row, it will be:

INSERT INTO dqm.table(name, age) VALUES (NULL, 25)

For the third row, it will be:

INSERT INTO dqm.table(name, age) VALUES ('Mike', NULL)

In each case, NULL values are not quoted, and string values are quoted, maintaining the integrity of other data types.

Refer more on python here :

Post Views: 11
Author: user
Website