OrmSession
From SQLAlchemy import create_enginefrom sqlalchemy.orm Import sessionmakerengine = Create_engine (' sqlite:///:memory: ' , echo=false) session = Sessionmaker (bind=engine) session = Session ()
echo
is responsible for setting whether to output the generated SQL statement.
create_engine
Returns an Engine object that is responsible for docking the DBAPI. Session
objects 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.
Integer
The 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 == None
OrUser.name.is_(None)
User.name != None
OrUser.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