arundhaj

all that is technology

Using SQLAlchemy Expression for Insert

 

In this article, I'll show how to build SQLAlchemy expressions for various cases of inserting data to the table

For simple insert,

# Prepare the statement
insert_stmt = insert(TableName).values(Column1='Column1Value', Column2='Column2Value')

# Execute the statement
insert_result = db.session.execute(insert_stmt)

# Get the Primary Key of the newly inserted record
inserted_id = insert_result.inserted_primary_key[0]

At times, passing the values directly in values method would get complex whenever the columns we need use are dynamic. In those cases,

# Build the data dictionary
insert_data = dict()
insert_data['Column1'] = 'Column1Value'

if Column2:
    insert_data['Column2'] = 'Column2Value'

# Prepare the statement
insert_stmt = insert(TableName).values(insert_data)

# Execute the statement
insert_result = db.session.execute(insert_stmt)

# Get the Primary Key of the newly inserted record
inserted_id = insert_result.inserted_primary_key[0]

To insert bulk records in a single statement using a bindparam. In this case, all bindparam columns should be present and cannot be skipped. So, need to specify None explicitly.

# Build the list of data dictionary
insert_data_list = []
for input in input_list:
    insert_data = dict()
    insert_data['Column1'] = input['Column1']
    insert_data['Column2'] = input['Column2'] if 'Column2' in input else None

    insert_data_list.append(insert_data)

# Prepare the statement
insert_stmt = insert(TableName).values(
    Column1=bindparam('Column1'), Column2=bindparam('Column2'))

# Execute the statement
insert_result = db.session.execute(insert_stmt, insert_data_list)

While inserting, if there is a duplicate record by Primary Key, we can ignore by specifying prefix_with('IGNORE')

# Build the data dictionary
insert_data = dict()
insert_data['TableNameId'] = 1
insert_data['Column1'] = 'Column1Value'

if Column2:
    insert_data['Column2'] = 'Column2Value'

# Prepare the statement
insert_stmt = insert(TableName).values(insert_data).prefix_with('IGNORE')

# Execute the statement
insert_result = db.session.execute(insert_stmt)

# Get the Primary Key of the newly inserted record, would be 0 if the insert was ignored
inserted_id = insert_result.inserted_primary_key[0]

Hope this helps!

  Python

Comments