SQLAlchemy ForeignKey和项目创建

Question

我正在尝试更好地理解SQL炼金术，以便开始将此ORM用于烧瓶。我有两个SQL表。这两个表之间有多对一的关系。我试图找到一种方法，使用SQL Alchemy，通过使用foreing键，但在父表中使用不同的字段，向子表添加一个条目。你知道吗

│   .dockerignore
│   .env
│   .env-template
│   .gitignore
│   docker-compose.yml
│   Dockerfile
│   manage.py
│   README.md
│   requirements.txt
├───app
│   │   config.py
│   │   routes.py
│   │   __init__.py
│   │
│   ├───sites
│   │       controller.py
│   │       interface.py
│   │       model.py
│   │       schema.py
│   │       service.py
│   │       __init__.py
│   │
│   └───devices
│           controller.py
│           interface.py
│           model.py
│           schema.py
│           service.py
│           __init__.py
├───config
│       gunicorn.py

app --> sites --> model.py

from sqlalchemy import Integer, Column, String
from app import db  # noqa
from .interface import SiteInterface


class Site(db.Model):  # type: ignore
    """Site"""

    __tablename__ = "sites"

    site_db_id = Column(Integer(), primary_key=True, autoincrement=True, nullable=False)
    dc_site_id = Column(Integer(), unique=True)
    dc_name = Column(String(100))

app --> sites --> interface.py

from mypy_extensions import TypedDict


class SiteInterface(TypedDict, total=False):
    site_id: int
    dc_site_id: int
    dc_name: str

app --> sites --> schema.py

from marshmallow import fields, Schema


class SiteSchema(Schema):
    """Site schema"""

    siteId = fields.Number(attribute="site_db_id")
    dcsiteId = fields.Number(attribute="dc_site_id")
    dcname = fields.String(attribute="dc_name")

app --> devices --> model.py

from sqlalchemy import Integer, Column, String, ForeignKey
from sqlalchemy.orm import relationship
from app import db  # noqa
from .interface import DeviceInterface

class Device(db.Model):  # type: ignore
    """Device"""

    __tablename__ = "devices"

    device_id = Column(Integer(), primary_key=True, autoincrement=True, nullable=False)
    hostname = Column(String(50), nullable=False)
    ip = Column(String(15), unique=True)
    site_db_id = Column(Integer, ForeignKey('sites.site_db_id'))
    sites = relationship("Site")

app --> devices --> interface.py

from mypy_extensions import TypedDict


class DeviceInterface(TypedDict, total=False):
    deviceId: int
    hostname: str
    ip: str
    sitedbId: int

app --> devices --> schema.py

from marshmallow import fields, Schema


class DeviceSchema(Schema):
    """Device schema"""

    deviceId = fields.Number(attribute="device_id")
    hostname = fields.String(attribute="hostname")
    ip = fields.String(attribute="ip")
    sitedbId = fields.Number(attribute="site_db_id")

我需要帮助来找到添加新设备并使用dc_site_id指定site_db_id的方法

像这样的。你知道吗

    @staticmethod
    def create(new_attrs: DeviceInterface) -> Device:
        new_device = Device(
            hostname=new_attrs["hostname"], ip=new_attrs["ip"],
            site_db_id=new_attrs["????"] *** select site_db_id FROM sites where dc_site_id=site_db_id ****
        )

        db.session.add(new_device)
        db.session.commit()

        return new_device

提前感谢您的帮助！！！你知道吗

Answer 1

如果我了解您的需求，您可以使用两步方法：首先使用dc\u Site\u id选择站点，然后使用找到的站点创建新设备。例如，代码可以如下所示：

dc_site_id = new_attrs["dc_site_id "]
sitefound = Site.query.filter_by(dc_site_id = dc_site_id ).first_or_404()
new_device = Device(...,...., relatedsite = sitefound)

其中relatedsite是为关系添加到Site类中的backref。你知道吗

devices= db.relationship('Device', backref='relatedsite', lazy=True)

这就是我最初在sqlalchemymanual中发现的一对多关系的方法：

class Person(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(50), nullable=False)
  addresses = db.relationship('Address', backref='person', lazy=True)

class Address(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  email = db.Column(db.String(120), nullable=False)
  person_id = db.Column(db.Integer, db.ForeignKey('person.id'), 
     nullable=False)