Use Python SQLAlchemy skillfully to easily conquer the world of relational databases

python sqlAlchemy is a powerful object-relational mapping tool library that allows development Personnel use Python objects to manipulate relational databases . This allows developers to easily create, query, and update data in the database without having to write complex SQL code.

Install SQLAlchemy

To use SQLAlchemy, you first need to install it. You can use the pip command to install:

from sqlalchemy import Column, Integer, String

class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String(50), unique=True)
email = Column(String(120), unique=True)

In this class, the __tablename__ attribute specifies the name of the table in the database. The id, name, and email attributes are respectively columns in the database. id is the primary key and is automatically incremented. name and email are columns of string type and are unique.

Create database engine

To map the ORM model to the database, you need to create a database engine. The database engine is an object that is responsible for interacting with the database.

For example, to create a database engine that connects to a SQLite database, you can write the following code:

from sqlalchemy import create_engine

engine = create_engine("sqlite:///database.sqlite")

In this code, "sqlite:///database.sqlite" is the connection string of the database.

Create session

To operate the data in the database, you need to create a session. A session is an object that represents an interaction with the database.

For example, to create a session, you can write the following code:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

In this code, Session is a session class, which is bound to engine. session is a session object, which can use methods such as add(), delete() and commit() to operate in the database The data.

adding data

To add data to the database, you can use the add() method. For example, to add a piece of data to the users table, you can write the following code:

user = User(name="John Doe", email="johndoe@example.com")
session.add(user)

In this code, user is a User object that contains the data to be added. session.add(user) Adds the user object to the session.

submit data

To submit data to the database, you can use the commit() method. For example, to submit data, you can write the following code:

session.commit()

In this code, session.commit() commits the data in the session to the database.

Query data

To query data in the database, you can use the query() method. For example, to query all User objects, you can write the following code:

users = session.query(User).all()

In this code, session.query(User).all() queries all User objects and stores them in the users variable.

update data

To update data in the database, you can use the update() method. For example, to update the email address of John Doe, you would write the following code:

session.query(User).filter_by(name="John Doe").update({User.email: "johndoe@example.com"})

In this code, session.query(User).filter_by(name="John Doe") queries the user named John Doe and updates the email address for johndoe@example.com.

delete data

To delete data in the database, you can use the delete() method. For example, to delete a user named John Doe, you would write the following code:

session.query(User).filter_by(name="John Doe").delete()

In this code, session.query(User).filter_by(name="John Doe") queries users named John Doe and deletes them.

Summarize

SQLAlchemy is a powerful ORM tool library that helps developers easily operate relational databases. Using SQLAlchemy, developers can quickly create, query, and update data in a database without writing complex SQL code.

The above is the detailed content of Use Python SQLAlchemy skillfully to easily conquer the world of relational databases. For more information, please follow other related articles on the PHP Chinese website!