10 Reasons to fall in love with SQLAlchemy (turn)

Original: http://python.jobbole.com/82453/

This article by Bole Online-Namco translation, Tang Yuhua School Draft. without permission, no reprint!
English Source: Paul Johnston. Welcome to join the translation team.

Recently, I have seen a lot of attacks against ORM, but I think some of the criticisms are groundless. I am a loyal supporter of SQLAlchemy. SQLAlchemy has been used in many places in my project, and I have contributed some code to the SQLAlchemy project. In this article , I will explain 10 reasons why you should fall in love with SQLAlchemy. To be honest, there are a lot of good ORM besides SQLAlchemy, and most of the reasons I've explained apply to them as well . but SQLAlchemy is my favorite.

1. Define the database schema with application code

SQLAlchemy allows you to use Python code to define a database schema (schema). Here is an example of an e-commerce site where an order represents a record.

Python

12345

class orderitem(Base): ID = Column(Integer, primary_key=True) order = many_to_one(' order ') product = many_to_one(' product ') quantity = Column(Integer)

The Python code that defines the schema of the database is called the model in SQLAlchemy. Because these models are all implemented in Python classes, you can add your own class methods. This allows the related functions to be put together for easy maintenance.

Python

12345

class Order(Base):     .. . def update_stock(self): For item in self . Items: item. Product. Stock -= Item. Quantity

The above example shows that the schema of the database in SQLAlchemy can also be maintained through versioning. So while using SQLAlchemy, you can also enjoy the many conveniences of versioning: version tracking, tagging, traceability (blame) , and more.

2. Auto-sync model to database mode

Alembic is a database management plug-in for SQLAlchemy. When you modify the model, Alembic can automatically update the database schema. Using Alembic to do some small changes to add a table or column is very quick and easy.

Python

1234	$ alembic upgrade head INFO [alembic. Context] context class postgresqlcontext. INFO [alembic. Context] would assume transactional DDL. INFO [alembic. Context] Running upgrade None, 1975ea83b712

Although it is convenient to automate synchronization in a development environment, most people want to adopt a more secure approach in a production environment. It is also Alembic that it can automatically generate modified scripts that the database administrator can then apply to the database on the production environment after viewing the script.

3. Pythonic code style makes your code easier to read

SQLAlchemy uses a more pythonic way of representing database relationships, which is very handy for reading and writing code. Let's take a look at the example below, which can print out all the products in an order.

Python

12	for item in order. Items     print (itemproduct. Name, item.quantity)

The code is very simple and easy to read, but has opened two databases and executed a join connection query. order.itemsis a one-to-many relationship, SQLAlchemy will automatically load OrderItem and the object associated with this order. The item.product store is a many-to-one relationship, SQLAlchemy will automatically load the corresponding product.

SQLAlchemy can also use classes. If the app modifies a mapped field, the object automatically requests a write to the database. This feature allows you to write application logic without having to worry about it.

4. Build query statements with Python

A simple query like retrieving objects with a primary key requires very little code:

Python

1	Order = session. Query(Order). Get(order_id)

Using Python's query syntax, we can implement more complex queries. For example, I want to find a valid order for two days ago:

Python

12	overdue_orders = Span class= "crayon-v" >session. Query (order) .< Span class= "crayon-k" >filter (order.status == ' active ' && Order. Time < datetime. Now() - timedelta(days=2))

The syntax of SQLAlchemy allows you to combine SQL statements with Python variables and to eliminate SQL injection attacks. SQLAlchemy will overload the various comparison operators internally and then convert them to SQL statements.

When you execute a very complex query, it is also possible to use the SQLAlchemy syntax to define the query. But I think the query complexity that SQLAlchemy can do is limited, and it may be easier to write SQL statements directly at some point. In this case, you can define the database view to complete the complex query, and SQLAlchemy can map the view to a Python object.

5. Seamless integration with WEB frameworks

Some frameworks support SQLAlchemy by default, such as Pyramid. for Other web frameworks, you need to install an integrated library to support SQLAlchemy, such as the flask-sqlalchemy for Flask or the aldjemy for Django.

SQLAlchemy maintains a connection pool and provides a database connection available for each Web request. Those support libraries can handle common exceptions, improve the robustness of the application, and keep the application from crashing in some exceptional situations, such as when the runtime restarts the database.

Each request is wrapped in a transaction, and the transaction is committed if the request succeeds, or it is rolled back. This design allows external methods to interact correctly with the database without needing to be concerned with the specific database processing code.

6. Preload to improve performance

Most ORM use a lazy-load strategy. The first time the relationship is called, the SQL query executes and loads the data. As in the example above, order.items the call actually executes an SQL query, and each subsequent use item.product initiates an additional query. Because item.product it is called in a loop, a large number of SQL queries are generated, resulting in degraded performance. This situation is called "n+1 choice Problem".

SQLAlchemy has a solution for the above problem: preload (eager loading). When we load this object for the first time, Order we can notify SQLAlchemy that we will use those relationships, and then SQLAlchemy can load all the data in a single query, the syntax is as follows:

Python

1	Session. Query(Order). Options(joinedload_all(' items.product ')). Get(order_id)

7. Transparent multi-state support

Object-oriented languages such as Python are encouraged to use polymorphism. If there is a Person base class, we can create subclasses based on it Person , such as adding new fields Employee or Customer  classes. But traditional SQL databases do not support polymorphism, so ORM wants to support polymorphism as well as powerless.

But SQLAlchemy perfectly simulates polymorphism in SQL. We can use polymorphism very naturally in Python code, and the data in the database can be easily accessed via SQL. In the application code we can easily use polymorphic classes without needing to care how they are stored.

8. Compatible with existing databases

Some ORM require that your database structure meet established criteria, forcing each table to have a single primary key column, or even the primary key name must be "id". If you create a database from scratch, these limitations are not a problem. But if you want to use a previous database, these restrictions will prevent you from accessing tables that do not meet the criteria.

SQLAlchemy does not assume your database structure, so you can perfectly support the previous database. There is also a tool called Sqlacodegen to generate SQLAlchemy models from existing databases. SQLAlchemy allows you to interact with the previous database through a simple Python script.

9, provide many hooks function lets you customize the library

SQLAlchemy has a clear layered architecture. Almost any SQLAlchemy library can be rewritten to meet specific needs.

When working on a cloud application with multiple users, I found a very useful feature. For example, most queries in the application contain filters, returning only the results of the current consumer. As in the following example:

Python

1	products = session. Queryproductfilter (productmerchant == current_user. Merchant . All ()

But I found that if you accidentally forget to add the filter, you might let a user see other reseller's information, which is not allowed. To be cautious, we can create a custom SQLAlchemy session factory function that automatically applies filters to all queries in the session. It's just a little bit of control code that can make your application more secure.

10. Perfect Documentation

Some open source documents are really flawed, but SQLAlchemy is not. SQLAlchemy's documentation is very detailed and also has a learning guide from simple examples to advanced features, as well as comprehensive API reference documentation. This is very helpful for developers to learn and use SQLAlchemy.

10 Reasons to fall in love with SQLAlchemy (turn)