为什么SQLalchemy需要无效的字段

发布于 01-20 04:43 字数 1961 浏览 3 评论 0原文

我正在尝试在“迁移文件”中使用Alembic的bulk_insert将一些数据获取到我的数据库中以进行测试。这是我的数据库创建代码:

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"),
    )

数据可以具有item_idobject_idarticle_id,也可以同时使用。如果存在ID,则指向另一个表的外键。

这就是我尝试将一些测试数据插入表的方式。它适用于第一行,但是下一个会导致错误:bind参数'item_id'需要一个值,在参数组1中,如果我的第一个对象具有item_id < /代码>,但没有其他FK和第二个对象没有item_id。如果第一行具有action_id而没有,则错误是bind参数'abtric_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),
    }
],

我不知道为什么会发生这种情况,为什么我不能将两个行插入给出不同的FK列的数据库?我想实现的是所有不存在的FK字段都是null

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

隔岸观火2025-01-27 04:43:53

我认为问题不是他们缺少,而是词典有不同的键。尝试设置有时会出现的参数,有时不会。我认为该语句是编译,然后一遍又一遍地重复使用的,它可能基于列表中的第一组值。因此,当字典有所不同时,后来的呼叫失败。我认为这在这里进行了解释。 /a>

执行多组参数时,每个字典都必须具有
相同的键;即,您的钥匙不能更少
词典比其他人。这是因为插入语句是
根据列表中的第一个字典编译,并假定
所有随后的参数字典都与之兼容
语句。

例子

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),
    }
])

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),
    }
])
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文