Python sqlalchemy.ForeignKey() Examples

def upgrade():
    op.create_table(
        'error_logs',
        IDColumn(),
        sa.Column('created_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), index=True),
        sa.Column('severity', sa.Enum('critical', 'error', 'warning', 'info', 'debug', name='errorlog_severity'),
                  index=True),
        sa.Column('source', sa.String),
        sa.Column('user', GUID, sa.ForeignKey('users.uuid'), nullable=True, index=True),
        sa.Column('is_read', sa.Boolean, default=False, index=True),
        sa.Column('is_cleared', sa.Boolean, default=False, index=True),
        sa.Column('message', sa.Text),
        sa.Column('context_lang', sa.String),
        sa.Column('context_env', postgresql.JSONB()),
        sa.Column('request_url', sa.String, nullable=True),
        sa.Column('request_status', sa.Integer, nullable=True),
        sa.Column('traceback', sa.Text, nullable=True),
    ) 

def create_followers_to_followings_table() -> None:
    op.create_table(
        "followers_to_followings",
        sa.Column(
            "follower_id",
            sa.Integer,
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "following_id",
            sa.Integer,
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
    )
    op.create_primary_key(
        "pk_followers_to_followings",
        "followers_to_followings",
        ["follower_id", "following_id"],
    ) 

def upgrade():
    op.create_table(
        'session_templates',
        IDColumn('id'),
        sa.Column('created_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), index=True),
        sa.Column('is_active', sa.Boolean, default=True),
        sa.Column('type',
                  sa.Enum('TASK', 'CLUSTER', name='templatetypes'),
                  nullable=False,
                  server_default='TASK'
                  ),
        sa.Column('domain_name', sa.String(length=64), sa.ForeignKey('domains.name'), nullable=False),
        sa.Column('group_id', GUID, sa.ForeignKey('groups.id'), nullable=True),
        sa.Column('user_uuid', GUID, sa.ForeignKey('users.uuid'), nullable=False),

        sa.Column('name', sa.String(length=128), nullable=True),
        sa.Column('template', sa.String(length=16 * 1024), nullable=False)
    )
    op.add_column(
        'kernels',
        sa.Column('bootstrap_script', sa.String(length=4 * 1024), nullable=True)
    ) 

def upgrade():
    ''' Create the pr_to_issue table. '''

    op.create_table(
        'pr_to_issue',
        sa.Column(
            'pull_request_uid',
            sa.String(32),
            sa.ForeignKey(
                'pull_requests.uid', ondelete='CASCADE', onupdate='CASCADE',
            ),
            primary_key=True),
        sa.Column(
            'issue_uid',
            sa.String(32),
            sa.ForeignKey(
                'issues.uid', ondelete='CASCADE', onupdate='CASCADE',
            ),
            primary_key=True)
    ) 

def upgrade():
    ''' Add the columns editor_id and edited_on to the table issue_comments.
    '''

    op.add_column(
        'issue_comments',
        sa.Column(
            'editor_id',
            sa.Integer,
            sa.ForeignKey('users.id', onupdate='CASCADE'),
            nullable=True)
    )

    op.add_column(
        'issue_comments',
        sa.Column(
            'edited_on',
            sa.DateTime,
            nullable=True)
    ) 

def revision(cls):
        tablename_compact = cls.__tablename__
        if tablename_compact.endswith("_history"):
            tablename_compact = tablename_compact[:-6]
        return sqlalchemy.Column(
            sqlalchemy.Integer,
            sqlalchemy.ForeignKey(
                'resource_history.revision',
                ondelete="CASCADE",
                name="fk_%s_revision_rh_revision"
                % tablename_compact,
                # NOTE(sileht): We use to ensure that postgresql
                # does not use AccessExclusiveLock on destination table
                use_alter=True),
            primary_key=True
        ) 

def upgrade():
    """ Create the tags_pull_requests to store the tags of pull-requests.
    """
    op.create_table(
        'tags_pull_requests',
        sa.Column(
            'tag_id',
            sa.Integer,
            sa.ForeignKey(
                'tags_colored.id', ondelete='CASCADE', onupdate='CASCADE',
            ),
            primary_key=True),
        sa.Column(
            'request_uid',
            sa.String(32),
            sa.ForeignKey(
                'pull_requests.uid', ondelete='CASCADE', onupdate='CASCADE',
            ),
            primary_key=True),
        sa.Column(
            'date_created',
            sa.DateTime,
            nullable=False,
            default=datetime.datetime.utcnow),
    ) 

def test_drop_foreign_key(self):
        bar = Table(
            "bar",
            self.metadata,
            Column("id", Integer, primary_key=True),
            Column("foo_id", Integer, ForeignKey("foo.id")),
            mysql_engine="InnoDB",
        )
        bar.create(self.conn)
        self.conn.execute(bar.insert(), {"id": 1, "foo_id": 3})

        naming_convention = {
            "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s"
        }
        with self.op.batch_alter_table(
            "bar", naming_convention=naming_convention
        ) as batch_op:
            batch_op.drop_constraint("fk_bar_foo_id_foo", type_="foreignkey")
        eq_(inspect(self.conn).get_foreign_keys("bar"), []) 

def upgrade():
    ''' Add the columns editor_id and edited_on to the table
    pull_request_comments.
    '''

    op.add_column(
        'pull_request_comments',
        sa.Column(
            'editor_id',
            sa.Integer,
            sa.ForeignKey('users.id', onupdate='CASCADE'),
            nullable=True)
    )

    op.add_column(
        'pull_request_comments',
        sa.Column(
            'edited_on',
            sa.DateTime,
            nullable=True)
    ) 

def _multi_fk_fixture(self, table_args=(), table_kwargs={}, schema=None):
        m = MetaData()
        if schema:
            schemaarg = "%s." % schema
        else:
            schemaarg = ""

        t = Table(
            "tname",
            m,
            Column("id", Integer, primary_key=True),
            Column("email", String()),
            Column("user_id_1", Integer, ForeignKey("%suser.id" % schemaarg)),
            Column("user_id_2", Integer, ForeignKey("%suser.id" % schemaarg)),
            Column("user_id_3", Integer),
            Column("user_id_version", Integer),
            ForeignKeyConstraint(
                ["user_id_3", "user_id_version"],
                ["%suser.id" % schemaarg, "%suser.id_version" % schemaarg],
            ),
            schema=schema,
        )
        return ApplyBatchImpl(self.impl, t, table_args, table_kwargs, False) 

def test_foreign_key_options(metadata):
    Table(
        "simple_items",
        metadata,
        Column(
            "name", VARCHAR, ForeignKey("simple_items.name", ondelete="CASCADE", onupdate="CASCADE", deferrable=True, initially="DEFERRED")
        ),
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, MetaData, String, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('name', String, ForeignKey('simple_items.name', ondelete='CASCADE', \
onupdate='CASCADE', deferrable=True, initially='DEFERRED'))
)
"""
    ) 

def upgrade():
    op.add_column(
        u'amphora',
        sa.Column(u'load_balancer_id', sa.String(36),
                  sa.ForeignKey(u'load_balancer.id',
                                name=u'fk_amphora_load_balancer_id'),
                  nullable=True)
    )
    op.drop_table(u'load_balancer_amphora')
    op.drop_constraint(
        u'fk_container_provisioning_status_name', u'amphora',
        type_=u'foreignkey'
    )
    op.create_foreign_key(
        u'fk_amphora_provisioning_status_name', u'amphora',
        u'provisioning_status', [u'status'], [u'name']
    ) 

def __iter__(self):
        for column in self.reflected_parent_columns:
            yield column

        # Only yield internal version columns if parent model is not using
        # single table inheritance
        if not self.model or not sa.inspect(self.model).single:

            yield self.transaction_column
            if self.option('strategy') == 'validity':
                yield self.end_transaction_column
            yield self.operation_type_column

            yield sa.Column(
                "data_hash",
                UUID,
                index=True,
                unique=True
            )
            yield sa.Column(
                "parent_hash",
                UUID,
                sa.ForeignKey("{}.data_hash".format(self.table_name)),
                index=True
            ) 

def test_create_table_two_fk(self):
        context = op_fixture()
        op.create_table(
            "some_table",
            Column("id", Integer, primary_key=True),
            Column("foo_id", Integer, ForeignKey("foo.id")),
            Column("foo_bar", Integer, ForeignKey("foo.bar")),
        )
        context.assert_(
            "CREATE TABLE some_table ("
            "id INTEGER NOT NULL, "
            "foo_id INTEGER, "
            "foo_bar INTEGER, "
            "PRIMARY KEY (id), "
            "FOREIGN KEY(foo_id) REFERENCES foo (id), "
            "FOREIGN KEY(foo_bar) REFERENCES foo (bar))"
        ) 

def create_favorites_table() -> None:
    op.create_table(
        "favorites",
        sa.Column(
            "user_id",
            sa.Integer,
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "article_id",
            sa.Integer,
            sa.ForeignKey("articles.id", ondelete="CASCADE"),
            nullable=False,
        ),
    )
    op.create_primary_key("pk_favorites", "favorites", ["user_id", "article_id"]) 

def create_articles_to_tags_table() -> None:
    op.create_table(
        "articles_to_tags",
        sa.Column(
            "article_id",
            sa.Integer,
            sa.ForeignKey("articles.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "tag",
            sa.Text,
            sa.ForeignKey("tags.tag", ondelete="CASCADE"),
            nullable=False,
        ),
    )
    op.create_primary_key(
        "pk_articles_to_tags", "articles_to_tags", ["article_id", "tag"]
    ) 

def upgrade():
    op.create_table(
        "a10_scaling_group_member_virtual_server_ports",
        sa.Column('id',
                  sa.String(36),
                  primary_key=True,
                  nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
        sa.Column('updated_at', sa.DateTime, nullable=False),
        sa.Column('virtual_server_id',
                  sa.String(36),
                  sa.ForeignKey(u'a10_scaling_group_member_virtual_servers.id'),
                  nullable=False),
        sa.Column('port',
                  sa.Integer,
                  nullable=False),
        sa.Column('protocol',
                  sa.String(255),
                  nullable=False),
        sa.Column('sflow_uuid',
                  sa.String(36),
                  nullable=False),
    ) 

def create_articles_table() -> None:
    op.create_table(
        "articles",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("slug", sa.Text, unique=True, nullable=False, index=True),
        sa.Column("title", sa.Text, nullable=False),
        sa.Column("description", sa.Text, nullable=False),
        sa.Column("body", sa.Text, nullable=False),
        sa.Column(
            "author_id", sa.Integer, sa.ForeignKey("users.id", ondelete="SET NULL")
        ),
        *timestamps(),
    )
    op.execute(
        """
        CREATE TRIGGER update_article_modtime
            BEFORE UPDATE
            ON articles
            FOR EACH ROW
        EXECUTE PROCEDURE update_updated_at_column();
        """
    ) 

def _test_fk_points_to_me(self, recreate):
        bar = Table(
            "bar",
            self.metadata,
            Column("id", Integer, primary_key=True),
            Column("foo_id", Integer, ForeignKey("foo.id")),
            mysql_engine="InnoDB",
        )
        bar.create(self.conn)
        self.conn.execute(bar.insert(), {"id": 1, "foo_id": 3})

        with self.op.batch_alter_table("foo", recreate=recreate) as batch_op:
            batch_op.alter_column(
                "data", new_column_name="newdata", existing_type=String(50)
            )

        insp = inspect(self.conn)
        eq_(
            [
                (
                    key["referred_table"],
                    key["referred_columns"],
                    key["constrained_columns"],
                )
                for key in insp.get_foreign_keys("bar")
            ],
            [("foo", ["id"], ["foo_id"])],
        ) 

def test_onetoone(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("other_item_id", INTEGER),
        ForeignKeyConstraint(["other_item_id"], ["other_items.id"]),
        UniqueConstraint("other_item_id"),
    )
    Table("other_items", metadata, Column("id", INTEGER, primary_key=True))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class OtherItem(Base):
    __tablename__ = 'other_items'

    id = Column(Integer, primary_key=True)


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    other_item_id = Column(ForeignKey('other_items.id'), unique=True)

    other_item = relationship('OtherItem', uselist=False)
"""
    ) 

def test_onetomany_selfref_multi(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("parent_item_id", INTEGER),
        Column("top_item_id", INTEGER),
        ForeignKeyConstraint(["parent_item_id"], ["simple_items.id"]),
        ForeignKeyConstraint(["top_item_id"], ["simple_items.id"]),
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    parent_item_id = Column(ForeignKey('simple_items.id'))
    top_item_id = Column(ForeignKey('simple_items.id'))

    parent_item = relationship('SimpleItem', remote_side=[id], \
primaryjoin='SimpleItem.parent_item_id == SimpleItem.id')
    top_item = relationship('SimpleItem', remote_side=[id], \
primaryjoin='SimpleItem.top_item_id == SimpleItem.id')
"""
    ) 

def test_onetomany_selfref(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("parent_item_id", INTEGER),
        ForeignKeyConstraint(["parent_item_id"], ["simple_items.id"]),
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    parent_item_id = Column(ForeignKey('simple_items.id'))

    parent_item = relationship('SimpleItem', remote_side=[id])
"""
    ) 

def test_onetomany(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("container_id", INTEGER),
        ForeignKeyConstraint(["container_id"], ["simple_containers.id"]),
    )
    Table("simple_containers", metadata, Column("id", INTEGER, primary_key=True))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class SimpleContainer(Base):
    __tablename__ = 'simple_containers'

    id = Column(Integer, primary_key=True)


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    container_id = Column(ForeignKey('simple_containers.id'))

    container = relationship('SimpleContainer')
"""
    ) 

def test_add_column_schema_fk_self_referential(self):
        context = op_fixture()
        op.add_column(
            "t1",
            Column("c1", Integer, ForeignKey("foo.t1.c2"), nullable=False),
            schema="foo",
        )
        context.assert_(
            "ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL",
            "ALTER TABLE foo.t1 ADD FOREIGN KEY(c1) REFERENCES foo.t1 (c2)",
        ) 

def create_commentaries_table() -> None:
    op.create_table(
        "commentaries",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("body", sa.Text, nullable=False),
        sa.Column(
            "author_id",
            sa.Integer,
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "article_id",
            sa.Integer,
            sa.ForeignKey("articles.id", ondelete="CASCADE"),
            nullable=False,
        ),
        *timestamps(),
    )
    op.execute(
        """
        CREATE TRIGGER update_comment_modtime
            BEFORE UPDATE
            ON commentaries 
            FOR EACH ROW
        EXECUTE PROCEDURE update_updated_at_column();
        """
    ) 

def test_add_column_fk_schema(self):
        context = op_fixture()
        op.add_column(
            "t1",
            Column("c1", Integer, ForeignKey("remote.t2.c2"), nullable=False),
        )
        context.assert_(
            "ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL",
            "ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES remote.t2 (c2)",
        ) 

def test_add_column_schema_fk_schema(self):
        context = op_fixture()
        op.add_column(
            "t1",
            Column("c1", Integer, ForeignKey("remote.t2.c2"), nullable=False),
            schema="foo",
        )
        context.assert_(
            "ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL",
            "ALTER TABLE foo.t1 ADD FOREIGN KEY(c1) REFERENCES remote.t2 (c2)",
        ) 

def test_create_table_selfref(self):
        context = op_fixture()
        op.create_table(
            "some_table",
            Column("id", Integer, primary_key=True),
            Column("st_id", Integer, ForeignKey("some_table.id")),
        )
        context.assert_(
            "CREATE TABLE some_table ("
            "id INTEGER NOT NULL, "
            "st_id INTEGER, "
            "PRIMARY KEY (id), "
            "FOREIGN KEY(st_id) REFERENCES some_table (id))"
        ) 

def upgrade():
    op.create_table(
        "a10_scaling_group_tenant_bindings",
        sa.Column('id',
                  sa.String(36),
                  primary_key=True,
                  nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
        sa.Column('updated_at', sa.DateTime, nullable=False),
        sa.Column('scaling_group_id', sa.String(36),
                  sa.ForeignKey('a10_scaling_groups.id'),
                  nullable=False),
        sa.Column('tenant_id', sa.String(255),
                  nullable=False),
    ) 

def changeActivity(old, new):
    Base = declarative_base()
    Session = sessionmaker(bind=op.get_bind())

    class Move(Base):
        __tablename__ = 'move'
        id = sa.Column(sa.Integer, name="id", primary_key=True)
        activity = sa.Column(sa.String, name="activity")
        strokeCount = sa.Column(sa.Integer, name='stroke_count')

    class Sample(Base):
        __tablename__ = 'sample'
        id = sa.Column(sa.Integer, name="id", primary_key=True)

        moveId = sa.Column(sa.Integer, sa.ForeignKey(Move.id), name="move_id", nullable=False)
        move = sa.orm.relationship(Move, backref=sa.orm.backref('samples', lazy='dynamic'))

        events = sa.Column(sa.String, name='events')

    session = Session()
    for move in session.query(Move):
        strokeCount = 0
        for eventData, in session.query(Sample.events).filter(Sample.move == move, Sample.events != None):
            events = json.loads(eventData)
            if 'swimming' in events and events['swimming']['type'] == 'Stroke':
                strokeCount += 1

        if 'swimming' in move.activity:
            assert strokeCount > 0

        if strokeCount > 0:
            move.strokeCount = strokeCount

    session.commit()