SQLAlchemy ORM Reference

OrmSession

From SQLAlchemy import create_enginefrom sqlalchemy.orm Import sessionmakerengine = Create_engine (' sqlite:///:memory: ' , echo=false) session = Sessionmaker (bind=engine) session = Session ()

echois responsible for setting whether to output the generated SQL statement.

create_engineReturns an Engine object that is responsible for docking the DBAPI. Sessionobjects will be used more directly.

Session in a sense similar to a cache, he has commit, rollback, close such methods. Therefore, the lifecycle maintenance of session instances should be located outside the functions that use them, separate from the specific application logic to avoid data contamination and ensure rollback/close calls. such as: – Reference

From contextlib import Contextmanager@contextmanagerdef session_scope (): "" "    provide a transactional scope around a Series of operations.    "" " Session = Session ()    try:        yield Session        session.commit ()    except:        session.rollback ()        raise    finally:        session.close () def run_my_program (): With    Session_scope () as session:        Thingone (). Go ( session)        Thingtwo (). Go (session)

Declaration of a mapped object

From sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, stringbase = declarative_ Base () class User (Base):    __tablename__ = ' user '    id = column (Integer, primary_key=true)    name = Column ( String)

From Python classes to SQL tables, a set of mapping logic is required, and SQLAlchemy provides a set of default solutions declarative_base .

A single line here Base=declarative_base() indicates that you can customize the base class in many ways, such as inheritance Base , or to a declarative_base(**kwargs) parameter. – Reference

As for the User class, since SQLAlchemy does not do any derivation by default, you need to display the specified __tablename__ parameters and at least one primary key.

IntegerThe type does not have a specified length, because this modifier parameter is only useful when the table is being constructed.

QueryNew

Tim = User (name= ' Tim ') Session.add (Tim) Session.flush ()

SQLAlchemy treats the request in a lazy manner, and he accesses the database only when necessary. One of the necessary definitions is query:

Session.query (User) filter_by (name= ' Tim ')

The way to synchronize to the database now is to use the .flush() method.

Another very interesting feature is that SQLAlchemy is able to separate the uniqueness of the table records in the same session. That

>>> Tim is Session.query (User) filter_by (name= ' Tim ') >>> True

For the same database record, whether you create a new one or take it out of the library multiple times, their Python ID is the same. This distinction is one of the benefits that the previously mentioned Mapping declaration must provide for a primary key .

Query Object

query()The parameters can be varied and can be a Model class, or a combination of some of his attributes:

Foo = session.query (User, user.id). Filter (user.name== ' Tim '). First ()

The tuple object returned by this query method is named after the keyword, and it supports attribute-type access, such as:

>>> foo. User.ID = = Foo.id>>> True

If you think of aliases, you can use the .label() method:

>>> foo = session.query (User.id.label (' user_id ')). Filter_by (Name= ' Tim ') >>> foo.user_id>> > 1

For the LIMIT and OFFSET parameters, you can use the Python slice operation directly on the Query object.

Session.query (User) [20:10]

Finally, the Query object is iterative. Although the above example has not used iterative methods in order to be lazy, it is supported!

Filter

filter()Compared to a filter_by() more general approach, all SQL expressions are supported. Filter () still returns a query object, so you can call the method in succession, filter() or the diagram is easy to put compound query statements into the same filter ()

Session.query (user). Filter (id>1, name== ' Tim ') session.query (user). Filter (id>1). Filter (name== ' Tim ')

Some commonly used expression statements:

• User.name == ‘tim‘
• User.name != ‘tim‘
• User.name.like(‘tim‘)
• User.name.in_([‘tim‘, ‘White‘])
• ~User.name.in_([‘tim‘, ‘white‘])
• User.name == NoneOrUser.name.is_(None)
• User.name != NoneOrUser.name.isnot(None)
• from sqlalchemy import or_ /// filter(or_(expr1, expr2))
• User.name.match(‘tim‘)

All (), one (), first ()

Although the Query object is iterative, we may prefer to take the required data directly to the most certain requirements.

all()And as the first() name implies, in particular one() .

It has one more constraint than first () that the result set of a query object must contain only one object, otherwise the error is:

Session.query (User). Filter (name== ' Tim '). One ()

Equivalent to:

Foo = Session.quert (User). Filter (Name= ' Tim ') Assert Len (Foo.all ()) = = 1return Foo.first ()

Sql

In a filter() method such as or order_by() , a conditional expression can also be the original SQL statement, which is simply wrapped with a text() statement:

From sqlalchemy Import textusers = Session.query (User). Filter (Text (' id<10 ')). Order_by (text (' ID '))

Another more thorough approach to SQL execution is to use the from_statement() method:

Session.query (user). From_statement (Text (' SELECT * from User where id<10 and Status=1 ')). All ()

You can also directly Query the column name at this point:

>>> session.query (' id ', ' name ', ' status '). From_statement (' SELECT * from user where class=3 '). All () >> > [(1, ' Jack ', 0), (2, ' Rose ', 1)]

Counting

A simple count is how the query object is used to count() return the number of rows of the query object.

Another more complex is func.count() that it supports subdivision counting:

From SQLAlchemy import funcsession.query (Func.count (User.class), User.class). Filter_by (user.class= ' a ') Session.query (Func.count (User.class), User.class). group_by (User.class). All ()

Relationship

Slightly

SQLAlchemy ORM Reference