I have a database that looks like this that I create with Alembic. The
relationships are trivial except between Revision and ObjectCount, there it
used two foreign keys
[image: aaa.png]
The code looks like this
############################
from datetime import datetime
from typing import Optional
from sqlalchemy import ForeignKey, create_engine, UniqueConstraint
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column,
relationship, sessionmaker # type: ignore
class Base(DeclarativeBase):
pass
class Blueprint(Base):
__tablename__ = "blueprints"
blueprint_id: Mapped[int] = mapped_column(primary_key=True)
filename: Mapped[str]
created: Mapped[datetime] = mapped_column(default=datetime.utcnow)
revisions: Mapped[list["Revision"]] =
relationship(back_populates="blueprint") # type: ignore
def get_new_revision_number(self) -> int:
if not self.revisions:
return 1
return max(revision.revision_number for revision in self.revisions)
+ 1
def __str__(self):
return f"{self.filename} : {self.blueprint_id}"
class Revision(Base):
__tablename__ = "revisions"
__table_args__ = (
UniqueConstraint("blueprint_id", "revision_number",
name="revision_blueprint_revision_number"),
)
blueprint_id: Mapped[int] =
mapped_column(ForeignKey("blueprints.blueprint_id"), primary_key=True)
revision_number: Mapped[int] = mapped_column(primary_key=True)
date: Mapped[datetime] = mapped_column(default=datetime.utcnow)
savedata: Mapped[str]
blueprint: Mapped["Blueprint"] =
relationship(back_populates="revisions") # type: ignore
object_count: Mapped[Optional[list["ObjectCount"]]] = relationship(
back_populates="revision",
primaryjoin="and_(Revision.blueprint_id==ObjectCount.blueprint_id,
Revision.revision_number==ObjectCount.revision_number)",
) # type: ignore
def __str__(self):
return f"{self.blueprint.filename} : {self.blueprint_id} :
{self.revision_number}"
class Object(Base):
__tablename__ = "objects"
object_id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
def __str__(self):
return self.name
# https://docs.sqlalchemy.org/en/20/orm/join_conditions.html
# https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html
#
https://docs.sqlalchemy.org/en/20/core/constraints.html#sqlalchemy.schema.UniqueConstraint
class ObjectCount(Base):
__tablename__ = "object_count"
__table_args__ = (
UniqueConstraint("blueprint_id", "revision_number", "object_id",
name="o_c_unique"),
)
blueprint_id: Mapped[int] =
mapped_column(ForeignKey("revisions.blueprint_id"), primary_key=True)
revision_number: Mapped[int] =
mapped_column(ForeignKey("revisions.revision_number"), primary_key=True)
object_id: Mapped[int] = mapped_column(ForeignKey("objects.object_id"),
primary_key=True)
count: Mapped[int]
object: Mapped["Object"] = relationship() # type: ignore
revision: Mapped["Revision"] = relationship(
back_populates="object_count",
primaryjoin="and_(Revision.blueprint_id==ObjectCount.blueprint_id,
Revision.revision_number==ObjectCount.revision_number)",
) # type: ignore
def __str__(self):
return f"{self.revision.blueprint.filename} {self.revision_number}
{self.object.name} {self.count}"
DATABASE =
"postgresql+psycopg://user:password@192.168.10.111:5432/mydatabase"
DATABASE =
"postgresql+psycopg2://user:password@192.168.10.111:5432/mydatabase"
#DATABASE = "sqlite:///database.db"
engine = create_engine(DATABASE)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
############################
I can create migrations with Alembic no problem. And I can migrate when I
use SQLite. But when I try to migrate with PostgreSQL I get this error:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there
is no unique constraint matching given keys for referenced table "revisions"
[SQL:
CREATE TABLE object_count (
blueprint_id INTEGER NOT NULL,
revision_number INTEGER NOT NULL,
object_id INTEGER NOT NULL,
count INTEGER NOT NULL,
PRIMARY KEY (blueprint_id, revision_number, object_id),
FOREIGN KEY(blueprint_id) REFERENCES revisions (blueprint_id),
FOREIGN KEY(object_id) REFERENCES objects (object_id),
FOREIGN KEY(revision_number) REFERENCES revisions (revision_number),
CONSTRAINT o_c_unique UNIQUE (blueprint_id, revision_number,
object_id)
)
]
I think I clearly see a unique constraint in the code. I add the
UniqueConstraint in __table_args__ but PostgreSQL still hits the wall then
I migrate. I get the same error with both psycopg2==2.9.6 and
psycopg==3.1.10. What frustrates me is that it works and all tests pass
when I use SQLite.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/ed00203b-d981-4f4f-9c7c-5f6c14660e74n%40googlegroups.com.
