How to use SQLAlchemy - 10 common examples

account = relationship(
        DummyAccount,
        backref=backref(
            "data", order_by="AccountRelated.id", cascade="all, delete-orphan"
        ),
    )

    text = Column(UnicodeText, default="")


class CommentRelated(db.Model):
    __tablename__ = "account_related_comment"
    __auditable_entity__ = ("related.account", "data.comments", ("related.id", "id"))
    id = Column(Integer, primary_key=True)

    related_id = Column(Integer, ForeignKey(AccountRelated.id), nullable=False)
    related = relationship(
        AccountRelated,
        backref=backref(
            "comments", order_by="CommentRelated.id", cascade="all, delete-orphan"
        ),
    )
    text = Column(UnicodeText, default="")


def test_audit(app, session):
    create_root_user()
    audit_service.start()
    assert len(AuditEntry.query.all()) == 0

    # Creation of system user(0) should have created one entry.
    # We clear it for this test.

Manager, Engineer = self.classes("Manager", "Engineer")

        self._mapping_fixture(False, passive_updates)

        sess = sa.orm.sessionmaker()()

        m1 = Manager(name="dogbert", paperwork="lots")
        e1, e2 = (
            Engineer(name="dilbert", primary_language="java", boss=m1),
            Engineer(name="wally", primary_language="c++", boss=m1),
        )
        sess.add_all([e1, e2, m1])
        sess.commit()

        eq_(e1.boss_name, "dogbert")
        eq_(e2.boss_name, "dogbert")

        eq_(
            sess.execute(
                self.tables.engineer.select().order_by(Engineer.name)
            ).fetchall(),
            [("dilbert", "java", "dogbert"), ("wally", "c++", "dogbert")],
        )

        sess.expire_all()

        m1.name = "pointy haired"
        e1.primary_language = "scala"
        e2.primary_language = "cobol"
        sess.commit()

        eq_(e1.boss_name, "pointy haired")

sess.add(o1)
        sess.commit()
        o1.name = "pointy haired"
        o1.owner_name = "pointy"
        sess.commit()

        eq_(
            sess.execute(self.tables.manager.select()).fetchall(),
            [("pointy haired", None)],
        )
        eq_(
            sess.execute(self.tables.owner.select()).fetchall(),
            [("pointy haired", "pointy")],
        )

        eq_(o1.name, "pointy haired")

        o1.name = "catbert"
        sess.commit()

        eq_(o1.name, "catbert")

        eq_(
            sess.execute(self.tables.manager.select()).fetchall(),
            [("catbert", None)],
        )
        eq_(
            sess.execute(self.tables.owner.select()).fetchall(),
            [("catbert", "pointy")],
        )

def test_clear(self):
        sess = Session()

        f1 = Foo(data={"a": "b"})
        sess.add(f1)
        sess.commit()

        f1.data.clear()
        sess.commit()

        eq_(f1.data, {})

def setUp(self):
        """
        Sets up the test fixture for the RAMSTKIncidentAction class.
        """

        engine = create_engine('sqlite:////tmp/TestDB.ramstk', echo=False)
        session = scoped_session(sessionmaker())

        session.remove()
        session.configure(bind=engine, autoflush=False, expire_on_commit=False)

        self.DUT = session.query(RAMSTKIncidentAction).first()
        self.DUT.action_prescribed = self._attributes[3]

        session.commit()

import datetime

import sqlalchemy as sa
import sqlalchemy.orm as orm
from pypi.data.modelbase import SqlAlchemyBase
from pypi.data.releases import Release


class Package(SqlAlchemyBase):
    __tablename__ = 'packages'

    id = sa.Column(sa.String, primary_key=True)
    created_date = sa.Column(sa.DateTime, default=datetime.datetime.now)
    last_updated = sa.Column(sa.DateTime, default=datetime.datetime.now)
    summary = sa.Column(sa.String, nullable=True)
    description = sa.Column(sa.String, nullable=True)

    home_page = sa.Column(sa.String, nullable=True)
    docs_url = sa.Column(sa.String, nullable=True)
    package_url = sa.Column(sa.String, nullable=True)

    author_name = sa.Column(sa.String, nullable=True)
    author_email = sa.Column(sa.String, nullable=True, index=True)

    license = sa.Column(sa.String, index=True)

    releases = orm.relation("Release", order_by=[
        Release.major_ver.desc(),
        Release.minor_ver.desc(),
        Release.build_ver.desc(),
    ], back_populates='package')

def setup(self):
        self.engine = engines.mock_engine()
        self.metadata = MetaData(self.engine)
        self.users = Table(
            "users",
            self.metadata,
            Column("user_id", Integer, primary_key=True),
            Column("user_name", String(40)),
        )

def test_model_out_of_context(self):
        class Foo(self.db.Model):
            __tablename__ = 'foo'
            id = Column(Integer, primary_key=True)
            name = Column(String)

        with self.db.session() as session:
            session.execute('create table foo(id int primary key, name text);')
            session.execute("insert into foo values(1, 'foo')")
            foo = session.query(Foo).first()

        self.assertEqual(1, foo.id)

),
                    )
                }

            else:
                addresses_rel = {
                    "addresses": relationship(
                        Address,
                        cascade=o2m_cascade and "save-update" or "",
                        cascade_backrefs=o2m_cascade_backrefs,
                    )
                }
            user_rel = {}
        elif m2o:
            user_rel = {
                "user": relationship(
                    User,
                    cascade=m2o_cascade and "save-update" or "",
                    cascade_backrefs=m2o_cascade_backrefs,
                )
            }
            addresses_rel = {}
        else:
            addresses_rel = {}
            user_rel = {}

        mapper(User, users, properties=addresses_rel)
        mapper(Address, addresses, properties=user_rel)

def test_column_accessor_string_no_target_column(self):
        fk = ForeignKey("sometable.somecol")
        c1 = Column('x', fk)
        m = MetaData()
        Table('t', m, c1)
        Table("sometable", m, Column('notsomecol', Integer))
        assert_raises_message(
            exc.NoReferencedColumnError,
            "Could not initialize target column for ForeignKey "
            "'sometable.somecol' on table 't': "
            "table 'sometable' has no column named 'somecol'",
            getattr, fk, "column"
        )