The easiest usage of sqlalchemy

Django provides us with an excellent object-relational model (ORM). Through ORM , object operations can be easily mapped to database operations.

But Django of ORM can only be used in the Django project, in order to use ORM in other projects, you can choose sqlalchemy

Python there are many ORM library, but do best is Django of ORM , but you can not escape Django use it alone, in addition to sqlalchemy , SQLObject , Strom , peewee etc.

So this tutorial next introduces how to use ORM Python project, including basic operations on databases, data tables, data rows, advanced indexes, various constraints, and advanced transactions, locks, and concurrency Advanced content such as version control.

This tutorial uses Mysql as the target database. The version of sqlalchemy used is 1.4.15. If you want to use other databases, please check Dialects More

Name: SQLAlchemy
Version: 1.4.15
Summary: Database Abstraction Library
Home-page: http://www.sqlalchemy.org
Author: Mike Bayer
Author-email: [e-mail]
License: MIT
Location: c:\users\17293\.virtualenvs\project_jd-wbhajpan\lib\site-packages
Requires: greenlet
Required-by:

Execute the command to install

pip install sqlalchemy

---

Connect to Mysql server

The code that uses sqlalchemy acts as a Mysql client.

Drivers are required to connect to the database, and sqlalchemy supports a lot of Mysql drivers.

sqlalchemy officially supported driver list: DBAPI Support

This tutorial uses mysqlclient as the driver. The following two programs are required to use this driver, execute commands to install them.

pip install mysql-connector-python-rf
pip install mysqlclient

Different drivers are required to connect to different databases. For example, there is a pymssql driver for connecting to SQL Server.

Okay, we now have a driver to connect to Mysql

class DB:
    Base = declarative_base()

    class ProductModel(Base):
        # 表的名字:
        __tablename__ = 'product'

        # 表的结构:
        id = Column(Integer(), primary_key=True)
        url = Column(String(256), unique=True)
        name = Column(String(512))
        brand = Column(String(64))
        price = Column(Float())
        sales_volume = Column(Integer())
        score1Count = Column(Integer())
        score2Count = Column(Integer())
        score3Count = Column(Integer())
        score4Count = Column(Integer())
        score5Count = Column(Integer())

    db_config = {
        'db_username': 'root',
        'db_password': 'yjcyjc',
        'db_host': 'localhost',
        'db_port': 3306,
        'db_database_name': 'd_test',
    }

    # 初始化数据库连接:
    link_sql = 'mysql+mysqlconnector://{db_username}:{db_password}@{db_host}:' \
               '{db_port}/{db_database_name}?' \
               'auth_plugin=mysql_native_password'.format(**db_config)

    engine = create_engine(link_sql, echo=True)
    # 创建DBSession类型:

    DBSession = sessionmaker(bind=engine)

    Base.metadata.create_all(engine)

Operational database

Create database

Delete database

Modify the database

Operating Data Sheet

Create table

Define the model

Perform migration

Delete table

Modify table

Operation data record (record line)

Add data

delete data

change the data

Find data