Before doing the query has always felt that the direct spelling of SQL is more convenient, using SQLAlchemy ORM query, found that it is also possible, but also improve the readability.
This article focuses on SQLAlchemy common ORM query methods, biased to practice. After reading, to deal with the development of the query needs, I think can meet a lot.
For the sake of illustration, assume the following data
Book table books
+----+--------+--------------------------+-------+ | id | cat_id | name | price | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 1 | fatigue life and death 40.40 | | | | | 1 2 skins 31.80 | | | | 2 | 3 | Chinese cartoons in half an hour 33.60 | | | 2 | | Jerusalem three thousand 55.60 4 | | 2 | | 5 national treasure 52.80 | | | | 3 | 6 a brief history of time 31.10 | | | | 3 | 7 a brief history of the universe 22.10 | | | | 3 | 8 Natural history 26.10 | | | | 3 | 9 human history of 40.80 | | | | 3 | 10 things a brief history of | | + - + 33.20 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- +Classification categories
+ - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | 1 literature | 2 | | humanities social sciences | 3 | | technology + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +The ORM object is defined as follows
Note: The Python code in this article is tested in the following environment
- Python 3.6.0
- PyMySQL 0.8.1
- SQLAlchemy 1.2.8
# coding=utf-8 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Numeric from sqlalchemy.orm import sessionmaker Base = declarative_base() engine = Create_engine (' mysql + pymysql: / / username: password ' '@ 127.0.0.1:3306 / db_name? Charset = utf8) Session = sessionmaker(bind=engine) session = Session() def to_dict(self): return {c.name: getattr(self, c.name, None) for c in self.__table__.columns} Base.to_dict = to_dict class Book(Base): __tablename__ = 'books' id = Column(Integer, primary_key=True) cat_id = Column(Integer) name = Column('name', String(120)) price = Column('price', Numeric) class Category(Base): __tablename__ = 'categories' id = Column(Integer, primary_key=True) name = Column('name', String(30))All right, let’s get down to business.
1 Get records by primary key
It’s easy to use when we get the details of a book.
book_id = 1
book = session.query(Book).get(book_id)
print(book and book.to_dict())Get (primary_key) directly to get the result
{" id ": 1, 'cat_id' : 1, the 'name' : 'fatigue life and death', 'price' : a Decimal (' 40.40 ')}Of course, that’s fine
book_id = 1
book = session.query(Book) \
.filter(Book.id == book_id) \
.first()
print(book and book.to_dict())However, the former approach is a little cleaner.
2 AND the query
This is the type of query we use most often, for example, if I want to get a book whose cat_id is 1 and whose price is greater than 35
books = session.query(Book) \
.filter(Book.cat_id == 1,
Book.price > 35) \
.all()
print([v.to_dict() for v in books])After execution, get the result
[{" id ": 1, 'cat_id' : 1, the 'name' : 'fatigue life and death', 'price' : a Decimal (' 40.40 ')}]The default condition in filter() is to use AND for the connection, which is the most common. So it’s not a problem to use it this way
from sqlalchemy import and_
books = session.query(Book) \
.filter(and_(Book.cat_id == 1,
Book.price > 35)) \
.all()
print([v.to_dict() for v in books])In general, however, there is no need to explicitly use AND_ if conditions are all AND concatenated.
If the conditions are all equivalent comparisons, you can use the filter_by() method, passing in keyword arguments.
Select * from books where cat_id = 1 and price = 31.8
Books = session.query(Book) \.filter_by(cat_id=1, price=31.8) \.all() print([v.dict () for v in books])The results of
[{" id ": 2, 'cat_id' : 1, the 'name' : 'skins',' price ': a Decimal (' 31.80')}]This is a cleaner way of writing than filter(), but the conditions are limited to equivalence comparisons.
Choose the right one for each situation.
3 Common methods
In addition to the get(), first(), and all() methods used above, there are a few more commonly used methods.
- One () fetches only one record, and will report an error if no record is found or if more than one record is found
# can't find the record will throw the following error # sqlalchemy. The orm. Exc. NoResultFound: No row was found for one() book = session \ .query(Book).filter(Book.id > 10) \ .one() print(book and book.to_dict()) # Find multiple records are the following error # sqlalchemy. The orm. Exc. MultipleResultsFound: Multiple rows were found for one() book = session \ .query(Book).filter(Book.id < 10) \ .one() print(book and Book. To_dict # ()) is normal, get the following results # {" id ": 10, 'cat_id' : 3, 'name' : 'a brief history of all things, #' price ': Decimal('33.20')} book = Session \.query(book).filter(book.id == 10) \.one() print(book and book.to_dict())- Count () returns the number of records
count = session \
.query(Book) \
.filter(Book.cat_id == 3) \
.count()
print(count)The results of
5- Limit () limits the number of records returned
books = session \
.query(Book) \
.filter(Book.cat_id == 3) \
.limit(3) \
.all()
print([v.to_dict() for v in books])The results of
[{' id ': 6' cat_id ': 3,' name ':' a brief history of time ', 'price' : a Decimal (' 31.10 ')}, {" id ": 7, 'cat_id' : 3, 'name' : 'a brief history of the universe', 'price: A Decimal (' 22.10 ')}, {" id ": 8, 'cat_id' : 3, 'name' : 'natural', 'price' : a Decimal (' 26.10 ')}]- DISTINCT () behaves as a SQL DISTINCT statement
books = session \
.query(Book.cat_id) \
.distinct(Book.cat_id) \
.all()
print([dict(zip(v.keys(), v)) for v in books])The results of
[{'cat_id': 1}, {'cat_id': 2},
{'cat_id': 3}]order_by()Sorts records by a field
# Sort books in descending order by ID # Sort books in ascending order by ID Books = session.query (book.id, books = session.query (book.id), books = session.query (book.id), books = session.query (book.id) Book.name) \ .filter(Book.id > 8) \ .order_by(Book.id.desc()) \ .all() print([dict(zip(v.keys(), v)) for v in books])The results of
[{" id ": 10, 'name' : 'a brief history of all things'}, {" id" : 9,' name ':' a brief history of human '}]- Scalar () returns the first column value of the result of calling one()
book_name = session \
.query(Book.name) \
.filter(Book.id == 10)\
.scalar()
print(book_name)The results of
A brief history of all things- EXist () checks to see if the record exists
SQL import exists is_exist = session \.query(exists(). WHERE (book.id > 10)) \ .scalar() print(is_exist)The results of
False4 the OR query
There are also many cases where the OR is used to connect the condition, such as I want to get the book whose cat_id equals 1 OR whose price is greater than 35
from sqlalchemy import or_
books = session.query(Book) \
.filter(or_(Book.cat_id == 1,
Book.price > 35)) \
.all()
print([v.to_dict() for v in books])Execute, get the result
[{" id ": 1, 'cat_id' : 1, the 'name' : 'fatigue life and death', 'price' : a Decimal (' 40.40 ')}, {" id" : 2, 'cat_id' : 1, the 'name' : 'skins',' price: A Decimal (' 31.80 ')}, {" id ": 4, 'cat_id' : 2, 'name' : 'Jerusalem in three thousand', 'price' : a Decimal (' 55.60 ')}, {' id '5,' cat_id ': 2,' name ': 'national treasure' and 'price' : a Decimal (' 52.80 ')}, {" id ": 9, 'cat_id' : 3, 'name' : 'human history', 'price' : a Decimal (' 40.80 ')}]Use the same way as an AND query, import OR_ from SQLAlchemy, AND drop the condition in.
5 AND AND OR queries
In the real world, it is very easy to encounter queries that coexist with AND AND OR. For example, I’m now looking for books whose price is greater than 55 or less than 25 and whose cat_id does not equal 1
from sqlalchemy import or_ books = session.query(Book) \ .filter(or_(Book.price > 55, Book.price < 25), Book.cat_id ! = 1) \ .all() print([v.to_dict() for v in books])The results of
[{" id ": 4, 'cat_id' : 2, 'name' : 'Jerusalem in three thousand', 'price' : a Decimal (' 55.60 ')}, {" id" : 7, 'cat_id' : 3, 'name' : 'a brief history of the universe', 'price: A Decimal (' 22.10 ')}]If the cat_id is greater than 5, if the number of books meets one of two requirements. The cat_id is less than 2 and the price is greater than 40. It can be
from sqlalchemy import or_, and_
count = session.query(Book) \
.filter(or_(Book.cat_id > 5,
and_(Book.cat_id < 2,
Book.price > 40))) \
.count()
print(count)The results of
1Use list or dictionary unpacking to pass arguments to query methods
In development, we often encounter query conditions constructed according to the incoming parameters. Such as
- If I receive something that’s not zero
cat_id, need to limitcat_idEqual to zero - If you receive a non-zero price, you need to restrict the price to equal the passed price
- If I receive something that’s not zero
min_priceWe need to restrict price to be greater than or equal tomin_price - If I receive something that’s not zero
max_price, we need to restrict price to be less than or equal tomax_price
We can write code like this
Params = {'cat_id': 1} conditions = [] if params.get('cat_id', 0): conditions.append(Book.cat_id == params['cat_id']) if params.get('price', 0): conditions.append(Book.price == params['price']) if params.get('min_price', 0): conditions.append(Book.price >= params['min_price']) if params.get('max_price', 0): conditions.append(Book.price <= params['max_price']) books = session.query(Book).filter(*conditions).all() print([v.to_dict() for v in books])The results of
[{" id ": 1, 'cat_id' : 1, the 'name' : 'fatigue life and death', 'price' : a Decimal (' 40.40 ')}, {" id" : 2, 'cat_id' : 1, the 'name' : 'skins',' price: A Decimal (' 31.80 ')}]The OR query is similar, unpacking the list to OR_ ().
If the requirements are more complex AND both AND AND OR are likely to occur, then build several more list implementations as appropriate. I’m just going to give you the general idea, and I’m not going to give you specific examples.
Of course, if they’re all equivalent queries, like these are the only two cases
- If I receive something that’s not zero
cat_id, need to limitcat_idEqual to zero - If you receive a non-zero price, you need to restrict the price to equal the passed price
You can use the dictionary unpack to pass arguments to filter_by()
Params = {'price': 31.1} condition_dict = {} if params.get('cat_id', 0): condition_dict['cat_id'] = params['cat_id'] if params.get('price', 0): condition_dict['price'] = params['price'] books = session.query(Book) \ .filter_by(**condition_dict) \ .all() print([v.to_dict() for v in books])The results of
[{' id ': 6' cat_id ': 3,' name ':' a brief history of time ', 'price' : a Decimal (' 31.10 ')}]Other common operators
Except for the ==, >, >=, <, <=,! In addition to =, there are several more commonly used
- IN
Books = session.query(Book) \.filter(book.id.in_ ([1, 3, 5])) \.all()- INSTR()
Books = session.query(Book) \.filter(book.name. Contains (' Book ')) \.all()FIN_IN_SET()
# FIND_IN_SET() # FIND_IN_SET() # FIND_IN_SET() # FIND_IN_SET() SQLAlchemy import func books = session.query(Book) \. Filter (func.find_in_set(' Time history ', book.name)) \.all()- LIKE
Books = session.query(Book) \.filter(book.name.like ('% history ')) \.all()- NOT
In, INSTR, FIN_IN_SET, and LIKE can all be reversed using the ~ symbol. Such as
Books = session.query(Book) \. Filter (~ book.id.in_ (range(1, 10))) \.all()Query the specified column
The query name contains the ID and name of the book with “Brief History”. The following
Books = session.query(book.id, book.name) \.filter(book.name. Contains (' Contains ')) \.all() print([dict(zip(v.keYs ()), v)) for v in books])The results of
[{' id: 6, "name" : "a brief history of time"}, {" id ": 7, 'name' : 'a brief history of the universe}, {" id" : 9,' name ':' a brief history of human '}, {" id ": 10, 'name' : 'a brief history of all things'}]9 inner connection, outer connection
9.1 in connection
Get books classified as “Technology” with a price greater than 40
If a foreign key relationship is defined in an ORM object, join() with a foreign key relationship. Books = Session \.query(Book.id, Book.name.label('book_name'), Category.name.label('cat_name')) \.join(Category, Category) Book. Cat_id == Category.id) \.filter(Category.name == 'Tech ', Book. Price > 40) \.all() print([dict(zip(v.keys(),), v)) for v in books])The results of
[{' id ': 9,' book_name ':' human history ', 'cat_name' : 'technology'}]Count the number of books in each category
from sqlalchemy import func
books = session \
.query(Category.name.label('cat_name'),
func.count(Book.id).label('book_num')) \
.join(Book, Category.id == Book.cat_id) \
.group_by(Category.id) \
.all()
print([dict(zip(v.keys(), v)) for v in books])The results of
[{' cat_name ':' literature ', 'book_num: 2}, {' cat_name' : 'humanities social sciences',' book_num: 3}, {' cat_name ':' technology ', 'book_num: 5}]9.2 outside connection
For illustration purposes, we only add the following data to the books table in this section
+----+--------+-----------------+-------+ | id | cat_id | name | price | +----+--------+-----------------+-------+ | 11 5 | | | | + 54.40 human weaknesses - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- +View classification information for books with ID greater than or equal to 9
# outerjoin() is left join by default; # outerjoin() is left join by default; Books = session \.query(book.id. Label ('book_id'), book.name.label ('book_name'), Category.id. Label ('cat_id'), Category.name.label('cat_name')) \ .outerjoin(Category, Book.cat_id == Category.id) \ .filter(Book.id >= 9) \ .all() print([dict(zip(v.keys(), v)) for v in books])The results of
[{' book_id '9,' book_name ':' human history ', 'cat_id' : 3, 'cat_name' : 'technology'}, {' book_id ': 10,' book_name ':' a brief history of everything ', 'cat_id' : 3, 'cat_name' : 'technology'}, {' book_id ': 11,' book_name ':' human weaknesses', 'cat_id' : None, 'cat_name' : None}]Notice the last record.
10 printing SQL
When we encounter complex queries, such as AND, OR, AND join queries, sometimes we may not get the expected results, then we can print the final SQL to help us find the error.
How do you print the final SQL for the outer join example in the previous section
q = session \
.query(Book.id.label('book_id'),
Book.name.label('book_name'),
Category.id.label('cat_id'),
Category.name.label('cat_name')) \
.outerjoin(Category, Book.cat_id == Category.id) \
.filter(Book.id >= 9)
raw_sql = q.statement \
.compile(compile_kwargs={"literal_binds": True})
print(raw_sql)Among them, the q for sqlalchemy. Orm. Query. The query classes of objects.
The results of
SELECT books.id AS book_id, books.name AS book_name, categories.id AS cat_id, categories.name AS cat_name
FROM books LEFT OUTER JOIN categories ON books.cat_id = categories.id
WHERE books.id >= 9So far, SQLAlchemy ORM commonly used some query methods and techniques have been introduced, hope to help to the need of friends.
Original link:http://www.kevinbai.com/artic…
Follow the public number “small back end” to get the latest article push!