Why is nullable field required with sqlalchemy

Question

I'm trying to use Alembic's bulk_insert in the migration file to get some data to my database for testing purposes. This is my database creation code:

import sqlalchemy as sa
from alembic import op

def upgrade():
    test_table = op.create_table(
        "test",
        sa.Column("id", sa.String, nullable=False),
        sa.Column("item_id", sa.String, nullable=True),
        sa.Column("object_id", sa.String, nullable=True),
        sa.Column("article_id", sa.String, nullable=True),
        sa.Column("active", sa.Boolean, nullable=False, default=True),
        sa.Column("name", sa.String, nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint("item_id"),
        sa.ForeignKeyConstraint("object_id"),
        sa.ForeignKeyConstraint("article_id"),
    )

The data can have either item_id, object_id, article_id, or all of them at the same time. If the id exists it is a Foreign Key pointing to another table.

This is how I try to insert some test data to the table. It works fine for the first row, but the next one causes an error e.g.: A value is required for bind parameter 'item_id', in parameter group 1, if my first object has item_id but no other FK's and the second object has no item_id. If the first row has an article_id and the second one doesn't, the error is A value is required for bind parameter 'article_id'....

op.bulk_insert(
test_table,
[
    {
        "id": "test1",
        "item_id": "item001",
        "active": True,
        "name" : "item 1",
        "created_at": datetime.now(tz=timezone.utc),
        "updated_at": datetime.now(tz=timezone.utc),
    },

    {
        "id": "test2",
        "article_id": "art001",
        "active": True,
        "name" : "article 1",
        "created_at": datetime.now(tz=timezone.utc),
        "updated_at": datetime.now(tz=timezone.utc),
    }
],

)

I can't figure out why this happens, why I can't insert two rows to the database where I have different FK columns given? What I would like to achieve is all FK fields not present would be Null.

score 2 · Accepted Answer · answered Apr 07 '22 at 21:52

I think the issue isn't that they are missing but that the dictionaries have different keys. Try setting the params that appear sometimes and sometimes not to None. I think the statement is compiled and then re-used over and over again, it is probably based on the first set of values in the list. So later calls fail when the dictionary differs. I think this is explained here executing-multiple-statements

When executing multiple sets of parameters, each dictionary must have the same set of keys; i.e. you cant have fewer keys in some dictionaries than others. This is because the Insert statement is compiled against the first dictionary in the list, and it’s assumed that all subsequent argument dictionaries are compatible with that statement.

Example

op.bulk_insert(
test_table,
[
    {
        "id": "test1",
        "article_id": None,
        "item_id": "item001",
        "active": True,
        "name" : "item 1",
        "created_at": datetime.now(tz=timezone.utc),
        "updated_at": datetime.now(tz=timezone.utc),
    },

    {
        "id": "test2",
        "item_id": None,
        "article_id": "art001",
        "active": True,
        "name" : "article 1",
        "created_at": datetime.now(tz=timezone.utc),
        "updated_at": datetime.now(tz=timezone.utc),
    }
])

Why is nullable field required with sqlalchemy

1 Answers1

Example