How to operate MySQL database with ORM model framework

What is ORM?

ORM stands for Object Relational Mapping, which means object-relational mapping. It can be generally understood as a virtual database of a programming language.

Understanding ORM

Mapping between user address information database table and objects

【Important features of ORM】

1. Object-oriented programming concept, easy to expand

2. Write less (almost no) SQL to improve development efficiency

3. Support various types of databases (commonly used mysql, pg, oracle, etc.), easy to switch

4. ORM technology is already quite mature and can solve most problems

[Choice of ORM model framework]

SQLAlchemy ORM Model

As we all know, there are many ORM framework models to choose from, so we chose the SQLAlchemy model framework here.

pip install SQLAlchemy install sql alchemy; you can also specify the version number pip install SQLAlchemy ==1.4.17

import sqlalcherm; sqlalchemy.__version__; Verify whether the installation is successful and the version number;

[Use of SQL Alchemy]

1. Start connecting to the database 2. Declare the ORM model base class 3. Implement the ORM model class 4. Synchronize the database table

Start connecting to the database

• Lazy Connecting - Connect to the database only when you actually operate the database
• Connection code example

from sqlalchemy import create_engine

create_engine("mysql://root:@127.0.0.1:3306/school?charset=utf8,echo=True,future=True")

create_engine Parameters Explanation

1. url (default first parameter) - what type of database to connect to, such as mysql; what database connector (driver) to use to connect to the database
2. echo——Whether to output logging information, all logs will be printed out
3. Future uses SQLAlchemy 2.0 API style

SQLAlchemy Configuration

What to do when the password contains special characters?

Without further ado, see the code below

from urllib.parse import quote_plus
If there are special characters in the password, you need to import a class to handle it password_formatted = quote.plus("mima%&&&mima")
Paste the processed password into the sqlalchemy configuration above.

Declare ORM model base class

from sqlalchemy.orm import declarative_base

Declare this base class Base = declarative_base()

Implementing ORM model class

How to achieve it? We need to write a class to inherit it.

Then you need to set up 1 attribute

from sqlalchemy import Column, Integer, String, DateTime


class Student(Base):
    """Student Information Form"""
    __tablename__ = 'student'
    id = Column(Integer, name='id', primary_key=True)
    stu_no = Column(Integer, nullable=False, comment='student number')
    stu_name = Column(String(16), nullable=False, comment='name')
    created_at = Column(DateTime)

1. You need to ensure that the database exists before synchronization. If not, you need to create it manually.

2 Create table, delete table

from orm_connect_example import Base ,engine

# Create table Base.metadata.create_all(engine)

#Delete table Base.metadata.drop_all(engine)

[Model field type corresponding to ORM]

Code example

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String, DateTime

# The first step is to prepare for connection engine = create_engine('mysql://root:@10.72.100.1:8081/test_database_1?charset=utf8',echo=True)

# Step 2: declare the base class of the ORM model Base = declarative_base()


# The third step is to implement the ORM model class class Student(Base):
    """Student Information Form"""
    __tablename__ = 'student'
    id = Column(Integer, name='id', primary_key=True)
    stu_no = Column(Integer, nullable=False, comment='student number')
    stu_name = Column(String(16), nullable=False, comment='name')
    created_at = Column(DateTime)
#The fourth step is to synchronize the database table def create_table()
    """Synchronize database tables"""
    # Create a new table Base.metadata.create_all(bind=engine)
    # Delete table Base.metadata.drop_all(bind=engine)

This is the end of this article about how to use the ORM model framework to operate the MySQL database. For more relevant ORM model framework content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!