ORM framework Databases in Python in practice

In recent years, Python has become an efficient, easy-to-use, and flexible programming language, and in the field of Python development, the application of Databases ORM framework is becoming more and more common. Databases is a simple, intuitive, lightweight ORM framework that supports a variety of databases, including but not limited to MySQL, PostgreSQL, SQLite and Microsoft SQL Server. This article will introduce you to the Databases framework in detail and its specific application in Python development.

1. The concept of ORM framework

ORM stands for Object Relational Mapping, which is a mapping between relational database tables and Python objects, thereby enabling developers to The technology of operating SQL statements is no longer needed in the programming process. The application of the ORM framework in Python provides developers with more choices and makes program development more convenient.

2. Databases framework

Databases is a simple ORM framework developed by Django ORM developer Andrew Goodwin. Its biggest feature is that it is lightweight, convenient and easy to use. It also supports a variety of databases, including MySQL, PostgreSQL, SQLite and Microsoft SQL Server.

1. Install the Databases framework

For the installation of the Databases framework, you can use pip to install it. The command is as follows:

pip install databases==0.4.*

2. Connect to the database

Before using the Databases framework for development, you need to connect to the database first. The parameters required to connect to the database include database type, host name, database name, user name and password, etc. In this development, we try to connect to the MySQL database. The code is as follows:

import databases

DATABASE_URL = "mysql://user:password@hostname/database_name"

database = databases.Database(DATABASE_URL)

Here, QAQsaaspassword is the password to connect to the database, askdnsadn312as is the username to connect to the database, and localhost is the host name or IP address where the database is located. , my_database is the name of the database.

3. Create tables and columns

The Databases framework supports SQL expression language to create complex query statements, allowing developers to more flexibly control the SQL execution process. In the Databases framework, we can use the Table class to create tables and the Column class to create columns.

The table creation code is as follows:

import sqlalchemy

metadata = sqlalchemy.MetaData()

users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("username", sqlalchemy.String),
    sqlalchemy.Column("password", sqlalchemy.String),
    sqlalchemy.Column("email", sqlalchemy.String),
)

Here, we use the MetaData object in SQLAlchemy and create four fields (id, username, password and email) by defining a users table.

4. Execute SQL statements

The Databases framework also provides a method of directly using SQL statements for operations, which is more flexible. SQL statements can be executed through the execute() method. The code is as follows:

result = await database.execute(query)

query represents the SQL statement code, and result is the execution result. When the execution is successful, it will return a dict type Composed of a list, each dict represents a record in the SQL query result.

5. Use ORM in Databases

The Databases framework also supports ORM to operate SQL statements, which is more convenient for developers. The ORM method helps map object relationships to the database, making the code more readable and maintainable.

Using ORM, we can operate the database by defining a model. The code is as follows:

import sqlalchemy

metadata = sqlalchemy.MetaData()

users = sqlalchemy.Table(
    "users",
    metadata,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("username", sqlalchemy.String),
    sqlalchemy.Column("password", sqlalchemy.String),
    sqlalchemy.Column("email", sqlalchemy.String),
)

class UserModel:
    id: int
    username: str
    password: str
    email: str

    __tablename__ = "users"

    query: sq.Select

    def __init__(self, **kwargs):
        for key, value in kwargs.items():
            setattr(self, key, value)

Here, we define a UserModel model class to represent our data table object. At the same time Through simple definitions, we can complete the definition of tables and columns in SQLAlchemy, so that its ORM can be mapped to the database, which is convenient, fast, and easy to maintain.

3. Application Example

Here, we take a simple blog application as an example to demonstrate the specific operation of the Databases framework.

1. Connect to the database

The information required to connect to the database is also very simple, as shown in the following code:

import databases

DATABASE_URL = "mysql://user:password@hostname/database_name"

database = databases.Database(DATABASE_URL)

The DATABASE_URL here specifies the connection to MySQL. parameters to connect to our database system.

2. Define the model

In this blog example, we need to define two data models. One is the Blog model, which represents the blog post object. The generated code is as follows:

class Blog:
    id: int
    title: str
    description: str
    content: str

    __tablename__ = "blog"

    query: sq.Select

    def __init__(self, **kwargs):
        for key, value in kwargs.items():
            setattr(self, key, value)

The other is the User model, which represents the user object. The generated code is as follows:

class User:
    id: int
    name: str
    email: str
    password: str

    __tablename__ = "user"

    query: sq.Select

    def __init__(self, **kwargs):
        for key, value in kwargs.items():
            setattr(self, key, value)

Similarly, we use a simple definition to map its ORM to the MySQL database we connect to.

3. Create a table

Since the database system we operate is MySQL, we need to create the corresponding table. The generated code is as follows:

CREATE TABLE `blog` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Insert data

We can use ORM to input data very conveniently. The generated code is as follows:

async def create_blog_post(title: str, description: str, content: str):
    query = blog.insert().values(title=title, description=description, content=content)
    return await database.execute(query)

async def create_user(name: str, email: str, password: str):
    query = user.insert().values(name=name, email=email, password=password)
    return await database.execute(query)

Here we use the insert() method to complete the data insertion operation, which is very readable. , to facilitate developers to better maintain code.

5. Query data

The Databases framework also supports query operations, as shown in the following code:

async def get_blog_post(id: int):
    query = blog.select().where(blog.c.id == id)
    return await database.fetch_one(query)

async def get_all_users():
    query = user.select()
    return await database.fetch_all(query)

async def get_users_by_email(email: str):
    query = user.select().where(user.c.email == email)
    return await database.fetch_all(query)

Here we use the select() method to splice the conditions to obtain a generated A device that returns a number of pairs when traversing. We can also filter and sort data by using query data.

Summarize

Databases framework is a flexible, easy-to-use, lightweight ORM framework for Python developers. It can easily support a variety of databases and facilitate developers to store, operate, filter and sort data, etc. . This article demonstrates the flexibility and ease of use of the Databases framework from connecting to the database to defining model operations, to data insertion and query operations, making it easier for developers to develop Python programs more efficiently.

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