SQLAlchemy使用教程(以SQLite为例) 深碍√TFBOYSˉ_ 2024-04-01 17:31 27阅读 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
还没有评论,来说两句吧...