SQLAlchemy使用教程（以SQLite为例）

深碍√TFBOYSˉ_ 2024-04-01 17:31 89阅读 0赞

1.  #### 安装依赖 ####
    
        pip3 install sqlalchemy==1.4.46
    
        pip install sqlacodegen
    
    如果是mysql则多安装一个依赖
    
        pip3 install pymysql
2.  #### 在项目中新建一个db文件夹（后来：推荐将xxx.db文件放在启动文件同级目录，不要再新建文件夹了，后面就可以使用相对路径了，比较方便）   ![c2085b3863df4df8b47a773d2f9e79a4.png][] ####
3.  #### 在db文件夹中新建SQLite数据库（即新建一个以.db为后缀的空文件）【mysql跳过这一步】   ![3533b9ff96424f448241a4b88237cf8d.png][] ####
4.  #### 新建配置文件db\_config.py   其他数据连接的写法参考：[SQLAlchemy创建各种数据库连接的\_苍穹之跃的博客-CSDN博客][SQLAlchemy_-CSDN] ####
    
        import os
        import platform
        
        from sqlalchemy import create_engine
        from sqlalchemy.orm import declarative_base, sessionmaker
        from contextlib import contextmanager
        
        """
        Mysql连接
        """
        # MYSQL_URL = f'mysql+pymysql://root:123456@localhost:3306/fast'
        
        """
        Sqlite连接：注意注意注意：这个URI连接的相对地址，指的是相对于最外层调用的文件的相对位置，而不是此文件的相对位置。所以最好是使用绝对路径。
        """
        # 获取当前文件的绝对路径
        SQLITE_URI = None
        if str(platform.system().lower()) == 'windows':
            path = __file__.replace(fr"\{os.path.basename(__file__)}", "").replace("\\\\", "\\")
            SQLITE_URI = fr'sqlite:///{path}\fast.db''?check_same_thread=False'
            print(f'数据库路径：{SQLITE_URI}')
        elif str(platform.system().lower()) == 'linux':
            path = __file__.replace(fr"/{os.path.basename(__file__)}", "").replace("//", "/")
            SQLITE_URI = fr'sqlite:///{path}/fast.db''?check_same_thread=False'
            print(f'数据库路径：{SQLITE_URI}')
        else:
            print(f"未知系统：{platform.system().lower()}")
        
        # 操作数据句柄
        engine = create_engine(SQLITE_URI)
        
        Base = declarative_base(engine)
        
        DbSession = sessionmaker(bind=engine)
        # 这里一定要用上下文去管理session,否则会出现很多诡异的情况！！！切记
        db_session = DbSession()
        
        
        @contextmanager
        def session_maker(session=db_session):
            try:
                yield session
                session.commit()
            except:
                session.rollback()
                raise
            finally:
                session.close()
        
        
        # 逆向工程 自动生成模型文件
        if __name__ == '__main__':
            os.system(f'sqlacodegen {SQLITE_URI} > models.py')
5.  #### 用Navicat连接第三步创建的SQLite数据库，并新建业务表   ![3a1442c79a484b17ba81527c8bc28f03.png][] ####
6.  #### 执行db\_config文件中的逆向工程   ![c6b8f37c6ae344dd8337276ac07c5b1f.png][]   此时在db文件夹下会自动生成相应的模型文件：models.py，此文件最好不要手动修改。如果后面改动了表结构，再执行一次逆向工程即可。   ![d24b6ff56adc49b5a4830b82e42aa4ca.png][]![1914f808bbaf4bb58ccccf0f975e3edd.png][] ####
7.  #### ORM操作 ####
    
        from sqlalchemy import func
        
        from db.db_config import session
        from db.models import DatabaseConnection
        
        if __name__ == '__main__':
        with session_maker() as session:
            # ADD
            session.add(DatabaseConnection(link_name='代码生成器', type='DAO', host='localhost', port='3306', username='admin',password='123456'))
            session.commit()
        
            # ADD_BATCH
            session.add_all([
                DatabaseConnection(link_name='代码生成器1', type='DAO', host='localhost', port='3306', username='admin',password='123456'),
                DatabaseConnection(link_name='代码生成器2', type='DAO', host='localhost', port='3306', username='admin',password='123456'),
                DatabaseConnection(link_name='代码生成器3', type='DAO', host='localhost', port='3306', username='admin',password='123456')
            ])
            session.commit()
        
            # DELETE
            session.query(DatabaseConnection).filter(DatabaseConnection.id == 6).delete()
            session.commit()
        
            # UPDATE
            session.query(DatabaseConnection).filter(DatabaseConnection.id == 5).update({DatabaseConnection.link_name: '安特磁材2'})
            session.commit()
        
            # SELECT 精确查询
            session.query(DatabaseConnection).filter(DatabaseConnection.host == 'localhost').all()
            # SELECT 获取部分字段
            session.query(DatabaseConnection.link_name).filter(DatabaseConnection.host == 'localhost').all()
            # SELECT 模糊查询
            session.query(DatabaseConnection).filter(DatabaseConnection.port.like('%33%')).all()
            # SELECT 正则查询
            session.query(DatabaseConnection).filter(DatabaseConnection.port.op("regexp")("[\d\D]*")).all()
            # SELECT 排序
            session.query(CodeTemplateGroup).order_by(CodeTemplateGroup.is_default.desc()).all()
            # SELECT 统计查询
            session.query(DatabaseConnection).filter(DatabaseConnection.port.op("regexp")("[\d\D]*")).count()
            # SELECT 调用数据库内置函数
            session.query(func.count(DatabaseConnection.link_name)).one()
            session.query(func.count(DatabaseConnection.link_name)).one_or_none()
            
            # 执行原生SQL
            records = session.execute('select * from database_connection')
            # 逻辑操作 not_取反，or_取或，and_取与
            session.query(OperatingInstructionLog) \
                    .filter(OperatingInstructionLog.type == 2) \
                    .filter(
                    not_(or_(OperatingInstructionLog.open_time >= datetime(date.year, date.month, date.day + 1, 0, 0, 0),
                             OperatingInstructionLog.close_time <= datetime(date.year, date.month, date.day, 0, 0, 0)))) \
                    .order_by(OperatingInstructionLog.open_time.asc())\
                    .all()

#### 8.擦除对象 ####

![7ff8d5288cb643b4bf7f633bcefd29f4.png][]

![d8a5c15299f64a0197b76ff45d64db3b.png][]

[c2085b3863df4df8b47a773d2f9e79a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/09e0ee4a0fa94ebf8a8ba6e73468d8a9.png
[3533b9ff96424f448241a4b88237cf8d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b0e1759f78cf4e2cb5b63763482df53d.png
[SQLAlchemy_-CSDN]: https://blog.csdn.net/wenxingchen/article/details/127805222
[3a1442c79a484b17ba81527c8bc28f03.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d9ab847c06134da2be4b84950bd8eee5.png
[c6b8f37c6ae344dd8337276ac07c5b1f.png]: https://img-blog.csdnimg.cn/c6b8f37c6ae344dd8337276ac07c5b1f.png
[d24b6ff56adc49b5a4830b82e42aa4ca.png]: https://img-blog.csdnimg.cn/d24b6ff56adc49b5a4830b82e42aa4ca.png
[1914f808bbaf4bb58ccccf0f975e3edd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4a49c852acd647fbabcc05b0bde19bd4.png
[7ff8d5288cb643b4bf7f633bcefd29f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c4d72783ccee479e80e523330263107f.png
[d8a5c15299f64a0197b76ff45d64db3b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0a4b65c69d6d470e9cef65078e81280a.png