【对称加密和非对称加密】

SQLAlchemy是Python中最æµ�行的ORMå¯¹è±¡å…³ç³»æ˜ å°„æ¡†æ�¶ä¹‹ä¸€å®ƒæ��供了高效且ç�µæ´»çš„æ•°æ�®åº“æ“�作方å¼�。本文将介ç»�如何使用SQLAlchemy ORM进行数æ�®åº“æ“�作。目录安装SQLAlchemyæ ¸å¿ƒæ¦‚å¿µè¿�æ�¥æ•°æ�®åº“定义数æ�®æ¨¡å�‹åˆ›å»ºæ•°æ�®åº“表基本CRUDæ“�作查询数æ�®å…³ç³»æ“�作事务管ç�†æœ€ä½³å®�践安装bashpip install sqlalchemy如æ�œéœ€è¦�è¿�æ�¥ç‰¹å®šæ•°æ�®åº“还需安装相应的驱动程åº�bash# PostgreSQL pip install psycopg2-binary # MySQL pip install mysql-connector-python # SQLite (Pythonæ ‡å‡†åº“å·²åŒ…å�«æ— 需é¢�外安装)æ ¸å¿ƒæ¦‚å¿µEngineæ•°æ�®åº“è¿�æ�¥çš„引æ“�è´Ÿè´£ä¸�æ•°æ�®åº“通信Sessionæ•°æ�®åº“会è¯�管ç�†æ‰€æœ‰æŒ�久化æ“�作Modelæ•°æ�®æ¨¡å�‹ç±»å¯¹åº”æ•°æ�®åº“中的表Query查询对象用äº�æ�„建和执行数æ�®åº“查询è¿�æ�¥æ•°æ�®åº“pythonfrom sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker # 创建数æ�®åº“è¿�æ�¥å¼•æ“� # SQLite示例 engine create_engine(sqlite:///example.db, echoTrue) # PostgreSQL示例 # engine create_engine(postgresql://username:passwordlocalhost:5432/mydatabase) # MySQL示例 # engine create_engine(mysqlmysqlconnector://username:passwordlocalhost:3306/mydatabase) # 创建会è¯�å·¥å�‚ SessionLocal sessionmaker(autocommitFalse, autoflushFalse, bindengine) # 创建会è¯�å®�例 session SessionLocal()定义数æ�®æ¨¡å�‹pythonfrom sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.orm import relationship, declarative_base # 创建基类 Base declarative_base() class User(Base): __tablename__ users id Column(Integer, primary_keyTrue, indexTrue) name Column(String(

, nullableFalse) email Column(String(

, uniqueTrue, indexTrue) # 定义一对多关系 posts relationship(Post, back_populatesauthor) class Post(Base): __tablename__ posts id Column(Integer, primary_keyTrue, indexTrue) title Column(String(

, nullableFalse) content Column(String(

) author_id Column(Integer, ForeignKey(users.id)) # 定义多对一关系 author relationship(User, back_populatesposts) # 定义多对多关系通过关�表 tags relationship(Tag, secondarypost_tags, back_populatesposts) class Tag(Base): __tablename__ tags id Column(Integer, primary_keyTrue, indexTrue) name Column(String(

, uniqueTrue, nullableFalse) posts relationship(Post, secondarypost_tags, back_populatestags) # å…³è�”表用äº�多对多关系 class PostTag(Base): __tablename__ post_tags post_id Column(Integer, ForeignKey(posts.id), primary_keyTrue) tag_id Column(Integer, ForeignKey(tags.id), primary_keyTrue)创建数æ�®åº“表python# 创建所有表 Base.metadata.create_all(bindengine) # åˆ é™¤æ‰€æœ‰è¡¨ # Base.metadata.drop_all(bindengine)基本CRUDæ“�作创建数æ�®python# 创建新用户 new_user User(nameå¼ ä¸‰, emailzhangsanexample.com) session.add(new_user) session.commit() # 批é‡�创建 session.add_all([ User(nameæ��å››, emaillisiexample.com), User(nameç�‹äº”, emailwangwuexample.com) ]) session.commit()读å�–æ•°æ�®python# è�·å�–所有用户 users session.query(User).all() # è�·å�–第一个用户 first_user session.query(User).first() # æ ¹æ�®IDè�·å�–用户 user session.query(User).get(

更新数�python# 查询并更新 user session.query(User).get(

user.name å¼ ä¸‰å›› session.commit() # 批é‡�æ›´æ–° session.query(User).filter(User.name.like(å¼ %)).update({name: å¼ æ°�}, synchronize_sessionFalse) session.commit()åˆ é™¤æ•°æ�®python# æŸ¥è¯¢å¹¶åˆ é™¤ user session.query(User).get(

session.delete(user) session.commit() # 批é‡�åˆ é™¤ session.query(User).filter(User.name æ��å››).delete(synchronize_sessionFalse) session.commit()查询数æ�®åŸºæœ¬æŸ¥è¯¢python# è�·å�–所有记录 users session.query(User).all() # è�·å�–特定字段 names session.query(User.name).all() # æ�’åº� users session.query(User).order_by(User.name.desc()).all() # é™�制结æ�œæ•°é‡� users session.query(User).limit(

.all() # �移� users session.query(User).offset(

.limit(

.all()过滤查询pythonfrom sqlalchemy import or_ # 等值过滤 user session.query(User).filter(User.name å¼ ä¸‰).first() # 模糊查询 users session.query(User).filter(User.name.like(å¼ %)).all() # IN查询 users session.query(User).filter(User.name.in_([å¼ ä¸‰, æ��å››])).all() # 多æ�¡ä»¶æŸ¥è¯¢ users session.query(User).filter( User.name å¼ ä¸‰, User.email.like(%example.com) ).all() # 或æ�¡ä»¶ users session.query(User).filter( or_(User.name å¼ ä¸‰, User.name æ��å››) ).all() # ä¸�ç­‰äº� users session.query(User).filter(User.name ! å¼ ä¸‰).all()è�šå�ˆæŸ¥è¯¢pythonfrom sqlalchemy import func # 计数 count session.query(User).count() # 分组计数 user_post_count session.query( User.name, func.count(Post.id) ).join(Post).group_by(User.name).all() # 求和ã€�å¹³å�‡å€¼ç­‰ avg_id session.query(func.avg(User.id)).scalar()è¿�æ�¥æŸ¥è¯¢python# 内è¿�æ�¥ results session.query(User, Post).join(Post).filter(Post.title.like(%Python%)).all() # 左外è¿�æ�¥ results session.query(User, Post).outerjoin(Post).all() # 指定è¿�æ�¥æ�¡ä»¶ results session.query(User, Post).join(Post, User.id Post.author_id).all()关系æ“�作python# 创建带关系的对象 user User(name赵六, emailzhaoliuexample.com) post Post(title我的第一篇å�šå®¢, contentHello World!, authoruser) session.add(post) session.commit() # 通过关系访问 print(fæ–‡ç« {post.title} 的作者是 {post.author.name}) print(f用户 {user.name} çš„æ‰€æœ‰æ–‡ç« :) for p in user.posts: print(f - {p.title}) # 多对多关系æ“�作 python_tag Tag(namePython) sqlalchemy_tag Tag(nameSQLAlchemy) post.tags.append(python_tag) post.tags.append(sqlalchemy_tag) session.commit() print(fæ–‡ç« {post.title} çš„æ ‡ç­¾:) for tag in post.tags: print(f - {tag.name})事务管ç�†python# 自动æ��交事务 try: user User(name测试用户, emailtestexample.com) session.add(user) session.commit() except Exception as e: session.rollback() print(få�‘生错误: {e}) # 使用事务上下文管ç�†å™¨ from sqlalchemy.orm import Session def create_user(session: Session, name: str, email: str): try: user User(namename, emailemail) session.add(user) session.commit() return user except: session.rollback() raise # 嵌套事务 with session.begin_nested(): user User(name事务用户, emailtransactionexample.com) session.add(user) # ä¿�存点 savepoint session.begin_nested() try: user User(nameä¿�存点用户, emailsavepointexample.com) session.add(user) savepoint.commit() except: savepoint.rollback()最佳å®�践会è¯�管ç�†ä¸ºæ¯�个请求创建新会è¯�请求结æ�Ÿå��关闭异常处ç�†å§‹ç»ˆå¤„ç�†å¼‚常并适当å›�æ»šäº‹åŠ¡å»¶è¿ŸåŠ è½½æ³¨æ„�N1查询问题使用 eager loading 优化è¿�æ�¥æ± å�ˆç�†é…�ç½®è¿�æ�¥æ± 大å°�和超时设置数æ�®éªŒè¯�在模å�‹å±‚或应用层验è¯�æ•°æ�®å®Œæ•´æ€§python# 使用上下文管ç�†å™¨ç®¡ç�†ä¼šè¯� from contextlib import contextmanager contextmanager def get_db(): db SessionLocal() try: yield db db.commit() except Exception: db.rollback() raise finally: db.close() # 使用示例 with get_db() as db: user User(name上下文用户, emailcontextexample.com) db.add(user)总结SQLAlchemy ORMæ��供了强大而ç�µæ´»çš„æ•°æ�®åº“æ“�作方å¼�通过本文的介ç»�您应该能够安装和é…�ç½®SQLAlchemy定义数æ�®æ¨¡å�‹å’Œå…³ç³»æ‰§è¡ŒåŸºæœ¬çš„CRUDæ“�作æ�„建å¤�æ�‚查询管ç�†æ•°æ�®åº“事务é�µå¾ªæœ€ä½³å®�è·µSQLAlchemy还有更多高级特性如混å�ˆå±�性ã€�事件监å�¬ã€�自定义查询等值得进一步æ�¢ç´¢å­¦ä¹ 。

17.c5gov 起草视频的背景和内容-17.c5gov 起草视频的背景和内容应用