Do query before always feel directly spell SQL is more convenient, use SQLAlchemy ORM query, found also can, but also improve readability.

This article mainly talks about SQLAlchemy common ORM query methods, partial practice. After seeing, deal with the query demand in the development, I think can meet many.

For illustration purposes, 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 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- +Copy the code

Classification categories

+ - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | 1 literature | 2 | | humanities social sciences | 3 | | technology + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

ORM objects are defined as follows

---

Note: The Python code in this article passes tests 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))
Copy the code

All right, let’s get down to business.

1 Obtain records based on primary keys

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())
Copy the code

Get (primary_key) to get the result

{'id': 1, 'cat_id': 1, 'name': 'Life and Death are Wearing me out'.'price': Decimal('40.40')}
Copy the code

Of course, that’s ok

book_id = 1
book = session.query(Book) \
    .filter(Book.id == book_id) \
    .first()
print(book and book.to_dict())
Copy the code

However, the former method is a little more concise.

2 AND the query

This is the kind of query we most often use, for example, I want to get books with cat_ID 1 and price greater than 35

books = session.query(Book) \
    .filter(Book.cat_id == 1,
            Book.price > 35) \
    .all()
print([v.to_dict() for v in books])
Copy the code

After execution, results are obtained

[{'id': 1, 'cat_id': 1, 'name': 'Life and Death are Wearing me out'.'price': Decimal('40.40')}]
Copy the code

The default condition in filter() is to connect with AND, which is the most common. So it’s ok 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])
Copy the code

However, in general, there is no need to explicitly use and_ if the condition is all AND concatenated.

If the conditions are all equivalent, you can use the filter_by() method, passing in the keyword argument.

Query books with cat_id = 1 and price = 31.8

Books = session.query(Book) \.filter_by(cat_id=1, price=31.8) \.all()print([v.to_dict() for v in books])
Copy the code

The results of

[{'id': 2.'cat_id': 1, 'name': 'skins'.'price': Decimal('31.80')}]
Copy the code

This is more concise than filter(), but the conditions are limited to equivalence comparisons.

Just choose the right one for each situation.

3 Common Methods

In addition to the get(), first(), and all() methods used above, the following methods are more common.

• One () gets only one record, and an error is reported if no record is found or if more than one record is found

If no record is found, the following error will be thrown
# sqlalchemy.orm.exc.NoResultFound: No row was found for one()
book = session \
    .query(Book).filter(Book.id > 10) \
    .one()
print(book and book.to_dict())

The following error will be thrown if multiple records are found
# sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()
book = session \
    .query(Book).filter(Book.id < 10) \
    .one()
print(book and book.to_dict())

# normal, get the following result
# {'id': 10, 'cat_id': 3, 'name': 'brief History of Everything ',
# 'price' : a Decimal (' 33.20 ')}
book = session \
    .query(Book).filter(Book.id == 10) \
    .one()
print(book and book.to_dict())
Copy the code

• Count () Returns the number of records

count = session \
    .query(Book) \
    .filter(Book.cat_id == 3) \
    .count()
print(count)
Copy the code

The results of

5
Copy the code

• 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])
Copy the code

The results of

[{'id': 6, 'cat_id': 3.'name': 'A Brief History of Time'.'price': Decimal('31.10')},
 {'id': 7, 'cat_id': 3.'name': 'A Brief History of the Universe'.'price': Decimal('22.10')},
 {'id': 8, 'cat_id': 3.'name': 'Natural History'.'price': Decimal('26.10')}]
Copy the code

• Distinct () is consistent with the DISTINCT statement behavior of SQL

books = session \
    .query(Book.cat_id) \
    .distinct(Book.cat_id) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])
Copy the code

The results of

[{'cat_id': 1}, {'cat_id': 2},
 {'cat_id': 3}]
Copy the code

• order_by()Sort records by a field

# Books are sorted in descending order by ID
Remove. Desc () from the list
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])
Copy the code

The results of

[{'id': 10, 'name': 'A Brief History of Everything'},
 {'id': 9, 'name': 'A Brief History of Mankind'}]
Copy the code

• Scalar () returns the first column value of the result from calling one()

book_name = session \
    .query(Book.name) \
    .filter(Book.id == 10)\
    .scalar()
print(book_name)
Copy the code

The results of

A brief history of all thingsCopy the code

• Exist () checks whether the record exists

Check whether a book with ID greater than 10 exists
from sqlalchemy.sql import exists
is_exist = session \
    .query(exists().where(Book.id > 10)) \
    .scalar()
print(is_exist)
Copy the code

The results of

False
Copy the code

4 the OR query

There are also many cases where the OR connection condition is used, such as I want to get a book with cat_ID equal to 1 OR a price 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])
Copy the code

Execute and get results

[{'id': 1, 'cat_id': 1, 'name': 'Life and Death are Wearing me out'.'price': Decimal('40.40')},
 {'id': 2.'cat_id': 1, 'name': 'skins'.'price': Decimal('31.80')},
 {'id': 4.'cat_id': 2.'name': Jerusalem three Thousand years..'price': Decimal('55.60')},
 {'id': 5, 'cat_id': 2.'name': 'National Treasure'.'price': Decimal('52.80')},
 {'id': 9, 'cat_id': 3.'name': 'A Brief History of Mankind'.'price': Decimal('40.80')}]
Copy the code

It is used in a similar way to the AND query, importing or_ from SQLAlchemy AND then putting conditions into it.

5 Query both AND AND OR

In reality, it is very easy to encounter AND AND OR query. For example, I’m now looking for books whose prices are greater than 55 or less than 25 and whose cat_id is not equal to 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])
Copy the code

The results of

[{'id': 4.'cat_id': 2.'name': Jerusalem three Thousand years..'price': Decimal('55.60')},
 {'id': 7, 'cat_id': 3.'name': 'A Brief History of the Universe'.'price': Decimal('22.10')}]
Copy the code

If the number of books is greater than 5, the number of books is greater than 5. 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)
Copy the code

The results of

1
Copy the code

Use unpacking of lists or dictionaries to pass arguments to query methods

In development, we often encounter query conditions constructed based on passed parameters. Such as

• If you receive a non-zerocat_id, need to limitcat_idEqual to zero
• If you receive a non-zero price, you need to limit price to the price passed in
• If you receive a non-zeromin_price, we need to limit price to be greater than or equal tomin_price
• If you receive a non-zeromax_price, you need to limit price to less than or equal tomax_price

We can write code like this

Request parameters, which are placeholders, are determined by the request submitted by the user
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])
Copy the code

The results of

[{'id': 1, 'cat_id': 1, 'name': 'Life and Death are Wearing me out'.'price': Decimal('40.40')},
 {'id': 2.'cat_id': 1, 'name': 'skins'.'price': Decimal('31.80')}]
Copy the code

An OR query is similar, just unpack the list to or_().

If the requirements are more complex, both AND AND OR may occur, then build more list implementations depending on the situation. I’m just going to give you the general idea here, not the specific examples.

Of course, if they’re all equivalent queries, like these two are the only ones

• If you receive a non-zerocat_id, need to limitcat_idEqual to zero
• If you receive a non-zero price, you need to limit price to the price passed in

Filter_by () can be passed using dictionary unpacking

Request parameters, which are placeholders, are determined by the request submitted by the user
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])
Copy the code

The results of

[{'id': 6, 'cat_id': 3.'name': 'A Brief History of Time'.'price': Decimal('31.10')}]
Copy the code

7 Other common operators

Except for the ==, >, >=, <, <=,! = = = = = = =

• IN

Select * from ID where ID 1, 3, 5
books = session.query(Book) \
        .filter(Book.id.in_([1, 3, 5])) \
        .all()
Copy the code

• INSTR()

Select * from books whose name contains "A Brief History of Time"
books = session.query(Book) \
    .filter(Book.name.contains('A Brief History of Time')) \
    .all()
Copy the code

• FIN_IN_SET()

Select * from books whose name contains "A Brief History of Time"
# the use of INSTR() is obvious
# FIND_IN_SET() is generally used for comma-separated ID string lookups
# FIND_IN_SET() is used here to illustrate usage

from sqlalchemy import func
books = session.query(Book) \
    .filter(func.find_in_set('A Brief History of Time', Book.name)) \
    .all()
Copy the code

• LIKE

Search for books whose names end in "Brief History"
books = session.query(Book) \
        .filter(Book.name.like('% history')) \
        .all()
Copy the code

• NOT

IN, INSTR, FIN_IN_SET, and LIKE can all be reversed with the ~ sign. Such as

Select * from ID where ID is not between 1 and 9
books = session.query(Book) \
    .filter(~Book.id.in_(range(1, 10))) \
    .all()
Copy the code

8 Query the specified column

Query the ID and name of a book whose name contains Brief History. The following

books = session.query(Book.id, Book.name) \
    .filter(Book.name.contains('brief')) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])
Copy the code

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 Mankind'},
 {'id': 10, 'name': 'A Brief History of Everything'}]
Copy the code

9 Internal connection and external connection

9.1 in connection

Get books classified as “science” and priced over 40

If the ORM object defines a foreign key relationship
# Then join() can not specify the association relation
# Otherwise, you have to
books = session \
    .query(Book.id,
           Book.name.label('book_name'),
           Category.name.label('cat_name')) \
    .join(Category, Book.cat_id == Category.id) \
    .filter(Category.name == 'technology',
            Book.price > 40) \
    .all()
print([dict(zip(v.keys(), v)) for v in books])
Copy the code

The results of

[{'id': 9, 'book_name': 'A Brief History of Mankind'.'cat_name': 'technology'}]
Copy the code

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])
Copy the code

The results of

[{'cat_name': 'literature'.'book_num': 2},
 {'cat_name': 'Humanities and Social Sciences'.'book_num': 3},
 {'cat_name': 'technology'.'book_num': 5}]
Copy the code

9.2 outside connection

For illustration purposes, we just add the following data to the books table in this section

+----+--------+-----------------+-------+ | id | cat_id | name | price | +----+--------+-----------------+-------+ | 11 5 | | | | + 54.40 human weaknesses - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- +Copy the code

View the classification information of the books whose ID is greater than or equal to 9

# outerJoin is left join by default
If the ORM object defines a foreign key relationship
Outerjoin () does not specify an association
# Otherwise, you have to
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])
Copy the code

The results of

[{'book_id': 9, 'book_name': 'A Brief History of Mankind'.'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 Weakness'.'cat_id': None, 'cat_name': None}]

Copy the code

Notice the last entry.

10 printing SQL

When complex queries, such as AND, OR, AND join queries, do not yield the expected results, we can print the final SQL to help us find the errors.

How do I print the final SQL

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)
Copy the code

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 >= 9
Copy the code

So far, SQLAlchemy ORM commonly used some query methods and skills have been introduced, I hope to help friends in need.

Follow the public account “Small Back end” to read more great articles.