When using SQLAlchemy ORM to query data, if the required records are filtered according to the selected conditions, you can refer to the relevant methods described in this article. There's no skill, just familiarity. Use the data source I often use Sample Data . It is recommended to use jupyter notebook for interactive testing.

Using SQLAlchemy ORM involves engine, session and other objects. Before querying, prepare the following codes:

from sqlalchemy import create_engine
from sqlalchemy import Column, String, Integer, and_, or_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+pymysql://root:pwd@localhost:3306/stonetestdb?charset=utf8')

Base = declarative_base(engine)

Session = sessionmaker(engine)
session = Session()

class EmpMaster(Base):
    __tablename__ = 'emp_master'

    EMP_ID = Column(Integer, primary_key=True)
    GENDER = Column(String(10))
    AGE = Column(Integer)
    EMAIL = Column(String(50))
    PHONE_NR = Column(String(20))
    EDUCATION = Column(String(20))
    MARITAL_STAT = Column(String(20))
    NR_OF_CHILDREN = Column(Integer)
    
    def __str__(self):
        return '<Employee({}, {}, {}, {}, {}, {}, {}, {}, {})>'.format(
            self.EMP_ID,
            self.GENDER,
            self.AGE,
            self.EMAIL,
            self.EMAIL,
            self.PHONE_NR,
            self.EDUCATION,
            self.MARITAL_STAT,
            self.NR_OF_CHILDREN
        )
    
def print_emps(emps):
    for emp in emps:
        print(emp)

In the EmpMaster class, the function of defining the str method is to facilitate calling the output display of the code. The print_emps() method reduces the amount of code when printing multiple empmasters.

Filter by primary key

The first method uses the get() method of the Query object.

# filter by primary key
emp = session.query(EmpMaster).get(1001)
print(emp)

The session.query() method returns the sqlalchemy.orm.query.Query object instance. Call the get() method of the query object and press the primary key to get the record.

The second method uses the filter_by() method of the Query object. The parameter of the filter_by() method is a keyword, which is relatively simple to use, but not powerful enough.

# fiter by primary key method 2
emp = session.query(EmpMaster).filter_by(EMP_ID = 1001).first()
print(emp)

Method 3, use the fitter() method of the Query object. The parameter of this method is SQL Expression of sqlalchemy ORM, which is relatively complex, but very powerful. Why it is powerful? I will explain it with examples later. For example, just now, filter by primary key. The filter by () method is as follows:

emp = session.query(EmpMaster).filter(
    EmpMaster.EMP_ID == 1001
).first()
print(emp)

The parameter of the filter() method is sql expression. For example, in this example, EmpMaster.EMP_ID is a Column. By looking at the source code of the Column, we can see that the Column object has like, in_, contains and other methods, so it can express complex filter conditions.

Equal and not equal

Later, we use the filter() method to learn how to express the filter conditions. Just now we have demonstrated equal (= =), if you want to express not equal, use the! = operator:

emps = session.query(EmpMaster).filter(
    EmpMaster.EMP_ID != 1001
).limit(3).all()

print_emps(emps)

AND condition

If there are multiple filter conditions, and the relationship between these conditions is and, there are two expression methods. One is that the conditions are separated by commas. The default relationship is and. The second method is to use and

emps = session.query(EmpMaster).filter(
    EmpMaster.EMP_ID <= 1009, EmpMaster.GENDER == 'Female'
).all()

print_emps(emps)

Or:

emps = session.query(EmpMaster).filter(
    and_(EmpMaster.EMP_ID <= 1009, EmpMaster.GENDER == 'Female')
).all()
print_emps(emps)

Note that and? Needs to be imported.

OR condition

Or the condition needs to use or and needs to be imported.

emps = session.query(EmpMaster).filter(
    or_(EmpMaster.GENDER == 'Female', EmpMaster.NR_OF_CHILDREN == 0)
).all()
print_emps(emps)

fuzzy search

There are two methods of fuzzy search: contains or like. For example, we want to query the people with west string in EMAIL:

emps = session.query(EmpMaster).filter(
    EmpMaster.EMAIL.contains('west')
).all()
print_emps(emps)

Or:

emps = session.query(EmpMaster).filter(
    EmpMaster.EMAIL.like('%west%')
).all()
print_emps(emps)

IN

If you want to select from several strings, use the in U keyword as the query criteria, and the parameter is a list. For example, the following query means to find employees with master's degree or doctor's degree:

emps = session.query(EmpMaster).filter(
    EmpMaster.EDUCATION.in_(['Doctorial', 'Master'])
).limit(3).all()
print_emps(emps)