Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

SQLAlchemy has good syntax support for a variety of database queries. To better demonstrate the results of the query, add a few records to the previous table employees to facilitate subsequent queries.

1. Two query methods

1.1 Querying Complete Records

This refers to querying the entire record (that is, the entire row of data, not certain columns). The specific code of this method is as follows:

DBsession = sessionmaker(bind=engine) session = DBsession( session.query(Employees).all() for data in data_list: print(data.name)Copy the code

result:

Outlaw fanatics Zhang Sanyao Renmin Shi ZhenxiangCopy the code

1.2 Querying Specific Fields

Here is a pointer to a column or a Gillette data query. The specific code of this method is as follows:

DBsession = sessionmaker(bind=engine) session = DBsession( session.query(Employees.name).all() for data in data_list: print(data)Copy the code

result:

(' Shi Zhenxiang ',) (' Outlaw fanatic Zhang SAN ',) (' Yao Renmin ',)Copy the code

2. Set filter criteria

2.1 the filter method

DBsession = sessionmaker(bind=engine) session = DBsession( session.query(Employees.name).filter(Employees.id==1).all() for data in data_list: print(data)Copy the code

result:

(' Outlaw Crazy Joe ',)Copy the code

2.2 filter_by method

DBsession = sessionmaker(bind=engine) session = DBsession( session.query(Employees.name).filter_by(id=1).all() for data in data_list: print(data)Copy the code

result:

(' Outlaw Crazy Joe ',)Copy the code

Difference between filter and filter_by

(1) filter_by filters a field with a class name, while filter_by filters a field with a class name.

(2) Filter is equal to filter_by

(3) Scope: Filter can be used for single or multiple table queries, while filter_BY can only be used for single table queries

3. Multi-condition screening

3.1 the and relationship

DBsession = sessionmaker(bind=engine) session = DBsession( Session.query (Employees).filter(employees.sex == 'male ', employees.age >= 20).all() print(data_list[0].name)Copy the code

result:

John the outlawCopy the code

3.2 the or relationship

DBsession = sessionmaker(bind=engine) session = DBsession( Session.query (Employees).filter(or_(employees.sex == 'male ', employees.age >= 20)).all() for data in data_list: print(data.name)Copy the code

result:

Outlaw fanatics Zhang Sanyao Renmin Shi ZhenxiangCopy the code

4. Connection query

The contents of the worker table associated with the employees table are as follows:

4.1 in connection

DBsession = sessionmaker(bind=engine) session = DBsession() data_list = session.query(Employees).join(Worker, Employees.name == worker.name).filter(employees.sex == 'male ').all() for data in data_list: print(data.name)Copy the code

result:

John the outlawCopy the code

4.2 outside connection

DBsession = sessionmaker(bind=engine) session = DBsession() data_list = session.query(Employees).outerjoin(Worker, Employees.name== worker.name).filter(employees.sex == 'male ').all() for data in data_list: print(data.name)Copy the code

result:

Outlaw fanatic Zhang Sanyao RenminCopy the code

5. Execute the SQL statement directly

Session DBsession = sessionmaker(bind=engine) session = DBsession() SQL = 'select * from employees' result = session.execute(sql) print(list(result))Copy the code

result:

Date (1993, 10, 23), 'test ', datetime.date(2017, 9, 11), datetime.date(2018, 3, 7)), (2, Date (2014, 9, 5), date(2021, 9, 23), (3, 'shi Zhen ', 'female ', 24, datetime.date(1997, 6, 26), 'UI', datetime.date(2020, 9, 29), datetime.date(2021, 9, 6))]Copy the code