Practical examples of working with a database in Python

Databases are an important component of any modern application. Databases store and process a huge amount of data. At the same time, a large number of users can work with the application simultaneously, and it is important to ensure reliable and efficient interaction between the database and the application code.

In order to provide such interaction with the database, we need a library for working with databases, as well as a library for asynchronous work in Python.

Python has several libraries for working with DBMS. In this article, we will look at working with SQLAlchemy, which is one of the most popular and widely used libraries for working with databases.

And to ensure asynchronous work, we will use Asyncio. This is a popular library for writing asynchronous code in Python, which provides an efficient way to work with databases.

Setting up the environment

First, we need to set up the environment. We will use the following commands to install the necessary libraries:

pip install sqlalchemy[asyncio]   # Или `pip install 'sqlalchemy[asyncio]'` для zsh

pip install aiohttp

pip install asyncpg

As a result, we should have a web server and modules for asynchronous work with the DBMS installed. Next, we will talk in more detail about the components used.

Working with SQLAlchemy

SQLAlchemy is a Python-based software library for working with relational DBMSs using Object Relational Mapping (ORM) technology, a suite of programs that allow you to work with databases as if they were objects in a programming language.

The library is used to synchronize Python objects and relational database records. SQLAlchemy allows you to describe database structures and methods of interacting with them in Python without using the SQL query language.

SQLAlchemy can be used as a back-end for databases: MySQL, PostgreSQL, SQLite, Oracle and others. Moreover, you can switch between databases simply by changing the configurations.

Using SQLAlchemy is fairly simple: import the library and create a database connection. The following code shows how to create a connection to a PostgreSQL database using SQLAlchemy:

from sqlalchemy import create_engine

from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@host:port/database')

# Пример строки подключения `postgresql://user:myawesomepassword@localhost:5432/mydb`

session = Session(engine, future=True)

In the code above, we create a connection to the PostgreSQL database using the create_engine function. We then create a Session object that can be used to interact with the database.

Creating a data model

Once we have successfully connected to the database, we can create a model to work with the data you want to store in the database:

from sqlalchemy.orm import declarative_base

from sqlalchemy import Column, Integer, String

 Base = declarative_base()

 class User(Base):

    __tablename__ = 'users'

     id = Column(Integer, primary_key=True)

    name = Column(String)

    email = Column(String)

In the above code snippet, we are creating a model for the user. We are defining the table name and columns using the __tablename__ and Column values ​​respectively.

Create and request data

Now that we have a model, we can create records in the database and query the data using SQLAlchemy. The following code shows an example of how to create a new user and query all users:

 from sqlalchemy import create_engine

from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@host:port/database')

session = Session(engine, future=True)

# Создание нового пользователя

new_user = User(name="John Doe", email="john@gmail.com")

session.add(new_user)

session.commit()

# Запрос всех пользователей

users = session.execute(select(User)).all()

for user in users:

    print(user.name, user.email)

In the code above, we create a new user and add it to the database using the add function. Then we commit the changes using the commit function. Finally, we query all the users using the query function and print their names and email addresses.

As you can see, we have performed all these operations with data without using SQL language commands (SELECT, INSERT, etc.) That is, the application developer does not need to know SQL and write queries in this language. Instead, it is enough to have a good understanding of SQLAlchemy functions.

Let's join forces

Now that we've seen how to work with SQLAlchemy, let's look at how we can use asyncio with SQLAlchemy. To query for users, we can use the following code:

import asyncio

from sqlalchemy import MetaData

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession

 

meta = MetaData()

 

 

async def main():

    engine = create_async_engine('postgresql+asyncpg://user:password@host:port/database')

    async with engine.begin() as conn:

        await conn.run_sync(meta.create_all)

 

    async with AsyncSession(engine) as session:

        async with session.begin():

            # Создание нового пользователя

            new_user = User(name="John Doe", email="john@gmail.com")

            session.add(new_user)

 

        # Запрашиваем всех пользователей

        async with session.begin():

            users = await session.execute(select(User))

            async for user in users:

                print(user.name, user.email)

 

loop = asyncio.get_event_loop()

loop.run_until_complete(main())

 

In the code above, we create the async engine using the create_async_engine function from the SQLAlchemy library. Then we create the tables themselves using the create_all function.

To create a new user, we use the add function session.add(new_user) inside the transaction. Then we query all the users using the execute function inside the transaction and print their names and email addresses.

Conclusion

In this short article, we looked at a demo example of creating and retrieving data from a database in asynchronous mode using the SQLAlchemy and asyncio libraries. In this example, we did not use SQL commands, limiting ourselves to the functions of Python language libraries.

All the necessary skills and knowledge for programming in Python can be obtained specialization “Python Developer”.