sqlalchemy使用心得

导入必要包

import os
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

DIR_EXT = 'ext'
DB_NAME = 'TEST.DB'

初始化引擎

def init_db_engine():
    db_dir_location = os.path.join(os.getcwd(), DIR_EXT)
    os.makedirs(db_dir_location, exist_ok=True)
    db_f_location = os.path.join(db_dir_location, DB_NAME)
    engine = create_engine('sqlite:///' + db_f_location, convert_unicode=True, connect_args={
        'check_same_thread': False})
    return engine

获取与数据库交互的会话

def fetch_session(engine):
    db_session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    return db_session()

自定义数据表结构：

class User(Base):
    __tablename__ = 'user_account'
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    department = Column(String(30))

    def __repr__(self):
        return f"User(id={self.id!r}, name={self.name!r}, department={self.department!r})"

应用：

if __name__ == "__main__":
    # 初始化引擎
    engine = init_db_engine()
    # 建立类与数据表的映射，即User类对应user_account表
    User.metadata.create_all(engine, checkfirst=True)
    
    # 获取会话
    db_session = fetch_session(engine)
    # 打印表名称
    print(User.__tablename__)
    # 写数据
    db_session.add(User(id=1, name='Lee', department='a'))
    db_session.add(User(id=2, name='Wang', department='a'))
    db_session.add(User(id=3, name='Zhang', department='b'))
    # 提交数据
    db_session.commit()
    # 查询User类对用的表的department为a的所有行
    our_user = db_session.query(User).filter_by(department='a').all()
    print(our_user)
    # 查询User类对用的表的所有数据
    print(db_session.query(User).filter().all())
    # 关闭会话
    db_session.close()