Detailed explanation of ORM framework Databases in Python

In recent years, Python has become increasingly popular in the development field as an easy-to-learn, easy-to-use, and extremely flexible programming language. In Python applications, the ORM (Object-Relational Mapping) framework plays a crucial role, which not only greatly improves development efficiency, but also makes it easier for developers to manage data and objects.

In the ORM framework, the Databases library is an emerging Python library that is often used to access relational databases during the development process. Although this library is still relatively new in the world of Python development, it has already shown great potential. Therefore, in this article, we will introduce the Databases library in detail to help developers better understand and learn this powerful Python ORM framework.

1. Databases development background

The Databases library is a lightweight Python library used to simplify the development of asynchronous SQL databases. It uses asyncio for asynchronous programming and can work with a variety of SQL database engines, such as PostgreSQL, MySQL, MariaDB, SQLite, and more. Moreover, the Databases library also provides a highly concise and easy-to-use API.

The goal of the Databases library is to allow developers to build applications faster and eliminate a lot of repetitive code. It performs very well in terms of performance and provides advanced features such as connection pooling, transaction management, and many asynchronous driver enhancements and help documentation. Therefore, the Databases library is undoubtedly a very good choice for complex microservices or large web applications that require collaborative access to SQL databases.

2. Core functions of Databases

The design goal of the Databases library is to allow developers to use simple APIs to achieve rapid interaction with SQL databases. Below we will introduce some core functions of the Databases library:

1. The code is concise and clear

The Databases library uses Python’s concise syntax to maximize developers’ development efficiency. It can implement Python All advanced features such as asynchronous and concurrent operations.

For example, the following code creates a table named "users". Among them, several attributes of the table include ID, Name, Age and Email.

import databases

database = databases.Database("sqlite:///example.db")

query = "CREATE TABLE users (ID INT PRIMARY KEY, Name TEXT, Age INT, Email TEXT)"

await database.execute(query=query)

2. Collaborative client and server API

Databases library provides a high-level API, which includes asynchronous and synchronous operations, so we can unify these operations when using them .

import database

database = databases.Database("sqlite:///example.db")

# 异步操作（async）
async with database.transaction():
   query = "INSERT INTO users (ID, Name, Age, Email) VALUES (:id, :name, :age, :email)"
   values = {"id": 1, "name": "John Doe", "age": 23, "email": "[email protected]"}
   await database.execute(query=query, values=values)

# 同步操作
query = "SELECT * FROM users WHERE ID=:id"
values = {"id": 1}
result = database.fetch_one(query=query, values=values)

3. Connection pool

Databases library can easily manage connection pool issues and improve the efficiency of database access. When developers use the Databases library, they only need to specify the connection pool size to easily implement automated connections without having to manually manage connections, which is very convenient.

For example, in the following code, we can specify the maximum number of connections and the minimum number of connections, so that the Databases library can better manage the connection pool.

import databases

database = databases.Database("sqlite:///example.db", min_size=1, max_size=10)

4. Transaction management

The Databases library provides integrated transaction management to ensure consistency and reliability when performing operations. Transactions are a very important DBMS feature that allow developers to perform operations in batches.

For example, in the following code, we can collaboratively process data by starting and committing transactions asynchronously.

import databases

database = databases.Database("sqlite:///example.db")

async with database.transaction():
   query_1 = "INSERT INTO users (ID, Name, Age, Email) VALUES (:id, :name, :age, :email)"
   values_1 = {"id": 1, "name": "John Doe", "age": 23, "email": "[email protected]"}
   await database.execute(query=query_1, values=values_1)

   query_2 = "UPDATE users SET Age=:age WHERE ID=:id"
   values_2 = {"age": 25, "id": 1}
   await database.execute(query=query_2, values=values_2)

await database.commit()

5. Support multiple database engines

The Databases library supports a variety of common database engines, including MySQL, PostgreSQL, MariaDB, etc. This makes it easy for us to build the database environment we want.

For example, if we have the following code snippet, we can choose our commonly used database engine according to our needs.

import databases

database = databases.Database("postgresql://user:[email protected]:5432/example")

三、使用Databases库

在学习使用Databases库之前，我们需要先进行库的安装工作。

1.安装Databases库

我们可以通过以下命令安装Databases库：

pip install databases

2.创建数据库连接

要使用Databases库，我们首先需要通过URL字符串为我们的PHP应用程序创建一个与数据库引擎的连接。

import databases
from databases import Database

database = Database("sqlite:///example.db")

3.查询数据

Databases库提供了8个异步和同步函数，可用于查询数据库： `fetch_all()`,`fetch_one()`, `fetch_val()`, `fetch_many()`, `fetch_optional()`, `execute()`, `execute_many()`和`iterate()`

Query all rows

result = database.fetch_all("SELECT * FROM users")

Query the first row of data

result = database.fetch_one(" SELECT * FROM users WHERE ID=:id", value = {"id": 1})

Get cell data

result = database.fetch_val("SELECT Name FROM users WHERE ID =:id", value = {"id": 1})

Get multiple rows of data

result = database.fetch_many(query="SELECT * FROM users", values=[ {'id':1}, {'id':2})

If the data does not exist, return None

result = database.fetch_optional(query="SELECT * FROM users WHERE ID= :id", value={"id": 3})

Execute any query (return the number of inserted rows)

result = await database.execute("INSERT INTO users (ID, Name,Age,Email) VALUES (:id,:name,:age,:email)",value={"id":5, "name":"John","age":25,"email":" [email protected]"})

Execute any query in batch mode (returns the number of inserted rows)

result = await database.execute_many("INSERT INTO users (ID,Name,Age, Email) VALUES (:id,:name,:age,:email)",values=[{"id":5, "name":"John","age":25,"email":"[email protected ]"},{"id":6,"name":"Jack", "age":26,"email":"[email protected]"}])

Use asynchronous iterators to process data

async with database.connection() as connection:
async with connection.transaction():

   async for row in connection.iterate(query="SELECT * FROM users"):
      print(row)

4.使用事务

Databases库还提供了事务管理的机制。我们可以使用异步with语句来绑定事务。如果with语句内的所有操作成功完成，则会提交事务。否则，事务将被回滚。

async with database.transaction():
query = "INSERT INTO users (ID,Name,Age,Email) VALUES (:id,:name,:age,:email)"
value = {"id": 5, "name": "Hassen", "age": 26 , "email": "[email protected]"}
await database.execute(query, values)

query = "INSERT INTO user1 (ID,Name,Age,Email) VALUES (:id,:name,:age,:email)"
value = {"id": 6, "name": " Fatima", "age": 22, "email": "[email protected]"}
await database.execute(query, values)

The above is the detailed introduction of the Databases library in the Python ORM framework . The Databases library is a new generation Python ORM framework. It has many excellent features, such as concise code optimization, asynchronous communication operations, connection pools, etc., which can greatly improve developers' development efficiency and promote its excellent performance. Although the Databases library is still relatively new in the world of Python development, it is increasingly gaining attention and recognition from the community. We believe that this library will play an increasingly important role in the future work of Python developers.

The above is the detailed content of Detailed explanation of ORM framework Databases in Python. For more information, please follow other related articles on the PHP Chinese website!