Python使用SQLAlchemy

Python使用SQLAlchemy

1 安装SQLAlchemy

备注：本文适用于SQLAlchemy>2.0

# 安装SQLAlchemy
pip install SQLAlchemy

# 安装pymysql
pip install pymysql

参考文档（SQLAlchemy>2.0）

https://docs.sqlalchemy.org/en/20/

创建数据库

# 创建数据库
CREATE DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 切换数据库
USE mytest;

# 创建数据
CREATE TABLE article (
    a_id INT PRIMARY KEY AUTO_INCREMENT,
	a_title VARCHAR(50) NOT NULL,
	a_keyword VARCHAR(100) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2 源代码

2.1 项目目录

2.2 config_db.py

from sqlalchemy import create_engine, MetaData, URL
from sqlalchemy.orm import Session, sessionmaker, scoped_session


from model.base_model import Base
from model.article import Article


class ConfigDB:

    acc = "root"
    pwd = "123456"
    host = "192.168.108.200"
    port = "3306"
    db_name = "mytest"

    def __init__(self):
        # 配置MySQL，也可以使用URL.create()方法
        db_url = "mysql+pymysql://" + self.acc + ":" + self.pwd + "@" +self.host + ":" +self.port + "/" + self.db_name
        self.engine = create_engine(db_url)

        # 创建session，不建议使用此方法
        self.session = Session(self.engine)

        """
        # 获取sessionmaker
        session_factory = sessionmaker(engine)
        # scoped_session是线程安全的
        # 注意，此session不能使用Query对象
        session = scoped_session(session_factory)
        """


    # 如果设计表可以不执行
    def init_tables(self):
        # 创建全部的表
        Article.metadata.create_all(bind=self.engine)


session = ConfigDB().session

2.3 article.py

from sqlalchemy import Integer, String
from sqlalchemy.orm import mapped_column

from model.base_model import Base


class Article(Base):
    __tablename__ = "article"
    id = mapped_column("a_id", Integer, primary_key=True)
    title = mapped_column("a_title", String(50))
    keyword = mapped_column("a_keyword", String(100))

2.4 base_model.py

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    """
    声明MySQL基类
    """
    pass

2.5 service_article.py

from sqlalchemy.orm import Query

from config.config_db import session
from model.article import Article

"""
使用orm实现增删改查，也可以用Statements
"""

def add_data_one() -> int:
    # 创建对象
    article = Article()
    article.title = "Mason"
    article.keyword = "Mason"

    # 添加成功的数量
    success_number = session.add(article)
    # 添加数据
    session.commit()
    return success_number


def query_data_all():
    # 构建查询
    query = Query([Article], session=session)
    
    #  推荐，基于scoped_session构建查询
    # query = session.query(Article)

    # 获取单个数据
    data_list:list = query.all()

    # 遍历数据
    for item in data_list:
        item:Article = item
        print(item.id, item.title)

    return data_list

def query_data_one():
    # 构建查询
    query = Query([Article], session=session)
    
    #  推荐，基于scoped_session构建查询
    # query = session.query(Article)
     
    # 过滤数据
    query_filter:Query = query.filter(
        Article.id == 1
    )
    
    # 查询单个数据
    return query_filter.one_or_none

def update_data_one() -> int:
    #  构建查询
    query = Query([Article], session=session)
    #  推荐，基于scoped_session构建查询
    # query = session.query(Article)

    # 查询数据
    query_filter:Query = query.filter(
        Article.id == 1
    )

    # 更新数据
    success_number = query_filter.update({
        Article.title: "河南大学"
    })

    session.commit()
    return success_number


def delete_data_one() -> int:
    # 构建查询
    query = Query([Article], session=session)
    #  推荐，基于scoped_session构建查询
    # query = session.query(Article)

    # 查询数据
    query_filter: Query = query.filter(
        Article.id == 7
    )

    # 删除数据
    success_number = query_filter.delete()
    session.commit()

    return success_number

2.6 main.py

from config.config_db import ConfigDB
from service.service_article import add_data_one, query_data_all, update_data_one, delete_data_one

ConfigDB().init_tables()


add_data_one()
query_data_all()
update_data_one()
delete_data_one()

3 执行结果