sqlalchemy 实现 Python 与 MySQL 交互

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, VARCHAR, NVARCHAR, DateTime
from sqlalchemy.orm import sessionmaker
import uuid
import datetime

import warnings

warnings.filterwarnings("ignore")

# 创建对象的基类:
Base = declarative_base()
# 初始化数据库连接:
engine = create_engine('mysql://[username]:[password]@[ip]:[port]/catalog?charset=utf8mb4')


class ArticleInformation(Base):
    __tablename__ = 'ARTICLE_INFORMATION'

    id = Column(name='ID', type_=NVARCHAR(36), primary_key=True)
    code = Column(name='CODE', type_=NVARCHAR(8), unique=True, nullable=False)
    title = Column(name='TITLE', type_=VARCHAR(255), nullable=False)
    github = Column(name='GITHUB', type_=VARCHAR(255), nullable=True)
    csdn = Column(name='CSDN', type_=VARCHAR(255), nullable=True)
    zhihu = Column(name='ZHIHU', type_=VARCHAR(255), nullable=True)
    wechat = Column(name='WECHAT', type_=VARCHAR(255), nullable=True)
    time = Column(name='TIME', type_=DateTime, nullable=False)
    reserved_field_1 = Column(name='RESERVED_FIELD_1', type_=VARCHAR(255), nullable=True)
    reserved_field_2 = Column(name='RESERVED_FIELD_2', type_=VARCHAR(255), nullable=True)
    reserved_field_3 = Column(name='RESERVED_FIELD_3', type_=VARCHAR(255), nullable=True)

    def __init__(self, _id=uuid.uuid4(), _code=None, _title=None, _time=datetime.datetime.now()):
        self.id = _id
        self.code = _code
        self.title = _title
        self.time = _time

    def __repr__(self):
        return 'id={}, code={}, title={}, time={}'.format(self.id, self.code, self.title, self.time)

    @classmethod
    def create_table(cls):
        Base.metadata.create_all(engine)

    @classmethod
    def get_session(cls):
        # 创建DBSession类型:
        DBSession = sessionmaker(bind=engine)
        # 创建session对象:
        session = DBSession()
        return session

    @classmethod
    def close_session(cls, session):
        session.close()

    @classmethod
    def insert(cls, obj):
        session = cls.get_session()
        session.add(obj)
        session.commit()
        session.close()

    @classmethod
    def select(cls, obj):
        session = cls.get_session()
        result = session.query(ArticleInformation).filter(ArticleInformation.id == obj.id).all()
        session.close()
        return result

    @classmethod
    def select_all(cls):
        session = cls.get_session()
        result = session.query(ArticleInformation).all()
        session.close()
        return result

    @classmethod
    def update(cls, obj):
        session = cls.get_session()
        session.query(ArticleInformation).filter(ArticleInformation.id == obj.id).update({'code': '00010003'})
        session.commit()
        session.close()

    @classmethod
    def delete(cls, _id):
        session = cls.get_session()
        obj = session.query(ArticleInformation).get(_id)
        if obj is not None:
            session.delete(obj)
            session.commit()
        session.close()


if __name__ == '__main__':
    # create table
    ArticleInformation.create_table()

    # insert
    ArticleInformation.insert(ArticleInformation(_code='0003', _title='标题三'))

    # select all
    article_list = ArticleInformation.select_all()
    for article in article_list:
        print(article)

    # select
    article = ArticleInformation()
    article.id = '454d338c-7133-43eb-8724-88ef634da2a0'
    article = ArticleInformation.select(article)
    print(article)

    # update
    article = ArticleInformation()
    article.id = '454d338c-7133-43eb-8724-88ef634da2a0'
    ArticleInformation.update(article)

    # delete
    ArticleInformation.delete(_id='454d338c-7133-43eb-8724-88ef634da2a0')

    pass