How to insert 'NULL' values for 'int' column tupes in Aurora PostgreSQL db using Python boto3 client

Question

I have a CSV file (MS SQL server table export) and I would like to import it to Aurora Serverless PostgreSQL database table. I did a basic preprocessing of the CSV file to replace all of the NULL values in it (i.e. '') to "NULL". The file looks like that:

CSV file:

ID,DRAW_WORKS
10000002,NULL
10000005,NULL
10000004,FLEXRIG3
10000003,FLEXRIG3

The PostgreSQL table has the following schema:

CREATE TABLE T_RIG_ACTIVITY_STATUS_DATE (
    ID varchar(20) NOT NULL,
    DRAW_WORKS_RATING int NULL
)

The code I am using to read and insert the CSV file is the following:

import boto3
import csv
rds_client = boto3.client('rds-data')

...

def batch_execute_statement(sql, sql_parameter_sets, transaction_id=None):
    parameters = {
        'secretArn': db_credentials_secrets_store_arn,
        'database': database_name,
        'resourceArn': db_cluster_arn,
        'sql': sql,
        'parameterSets': sql_parameter_sets
    }
    if transaction_id is not None:
        parameters['transactionId'] = transaction_id
    response = rds_client.batch_execute_statement(**parameters)
    return response

transaction = rds_client.begin_transaction(
    secretArn=db_credentials_secrets_store_arn,
    resourceArn=db_cluster_arn,
    database=database_name)


sql = 'INSERT INTO T_RIG_ACTIVITY_STATUS_DATE VALUES (:ID, :DRAW_WORKS);'
parameter_set = []


with open('test.csv', 'r') as file:
    reader = csv.DictReader(file, delimiter=',')
    for row in reader:
        entry = [
            {'name': 'ID','value': {'stringValue': row['RIG_ID']}},          
            {'name': 'DRAW_WORKS', 'value': {'longValue': row['DRAW_WORKS']}}
        ]
        parameter_set.append(entry)

response = batch_execute_statement(
    sql, parameter_set, transaction['transactionId'])

However, there is an error that gets returned suggests that there is a type mismatch:

Invalid type for parameter parameterSets[0][5].value.longValue, 
value: NULL, type: <class 'str'>, valid types: <class 'int'>"

Is there a way to configure Aurora to accept NULL values for types such as int?

Answer 1

Reading the boto3 documentation more carefully I found that we can use isNull value set to True in case a field is NULL. The bellow code snippet shows how to insert null value to the database:

...
entry = [
            {'name': 'ID','value': {'stringValue': row['ID']}}
        ]
    
if row['DRAW_WORKS'] == 'NULL':
                entry.append({'name': 'DRAW_WORKS', 'value': {'isNull': True}})
            else:
                entry.append({'name': 'DRAW_WORKS_RATING', 'value': {'longValue': int(row['DRAW_WORKS'])}})
parameter_set.append(entry)