当前位置：Gxlcms > 数据库问题 > Flask-SQLAlchemy

Flask-SQLAlchemy

时间：2021-07-01 10:21:17 帮助过：12人阅读

?
1 pip3 install sqlalchemy

技术分享图片

组成部分：

  • Engine，框架的引擎
  • Connection Pooling ，数据库连接池
  • Dialect，选择连接数据库的DB API种类
  • Schema/Types，架构和类型
  • SQL Exprression Language，SQL表达式语言

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

?
1 2 3 4 5 6 7 8 9 10 11 12 13 MySQL-Python mysql+mysqldb://:@[:]/   pymysql mysql+pymysql://:@/[?]   MySQL-Connector mysql+mysqlconnector://:@[:]/   cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]   更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

二. 使用

1. 执行原生SQL语句

?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 import time import threading import sqlalchemy from sqlalchemy import create_engine from sqlalchemy.engine.base import Engine   engine = create_engine( "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） )     def task(arg): conn = engine.raw_connection() cursor = conn.cursor() cursor.execute( "select * from t1" ) result = cursor.fetchall() cursor.close() conn.close()     for i in range(20): t = threading.Thread(target=task, args=(i,)) t.start()
技术分享图片 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> sqlalchemy
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.base <span style="color: #0000ff;">import</span><span style="color: #000000;"> Engine
  8. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/t1</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  9. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> task(arg):
  10.     conn </span>=<span style="color: #000000;"> engine.contextual_connect()
  11.     with conn:
  12.         cur </span>=<span style="color: #000000;"> conn.execute(
  13.             </span><span style="color: #800000;">"</span><span style="color: #800000;">select * from t1</span><span style="color: #800000;">"</span><span style="color: #000000;">
  14.         )
  15.         result </span>=<span style="color: #000000;"> cur.fetchall()
  16.         </span><span style="color: #0000ff;">print</span><span style="color: #000000;">(result)
  17. </span><span style="color: #0000ff;">for</span> i <span style="color: #0000ff;">in</span> range(20<span style="color: #000000;">):
  18.     t </span>= threading.Thread(target=task, args=<span style="color: #000000;">(i,))
  19.     t.start()</span>
技术分享图片 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> sqlalchemy
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.base <span style="color: #0000ff;">import</span><span style="color: #000000;"> Engine
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.result <span style="color: #0000ff;">import</span><span style="color: #000000;"> ResultProxy
  9. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/t1</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  10. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> task(arg):
  11.     cur </span>= engine.execute(<span style="color: #800000;">"</span><span style="color: #800000;">select * from t1</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  12.     result </span>=<span style="color: #000000;"> cur.fetchall()
  13.     cur.close()
  14.     </span><span style="color: #0000ff;">print</span><span style="color: #000000;">(result)
  15. </span><span style="color: #0000ff;">for</span> i <span style="color: #0000ff;">in</span> range(20<span style="color: #000000;">):
  16.     t </span>= threading.Thread(target=task, args=<span style="color: #000000;">(i,))
  17.     t.start()</span>
View Code

注意: 查看连接 show status like ‘Threads%‘;

2. ORM

a. 创建数据库表

技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> datetime
  4. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
  7. Base </span>=<span style="color: #000000;"> declarative_base()
  8. </span><span style="color: #0000ff;">class</span><span style="color: #000000;"> Users(Base):
  9.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">users</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  10.     id </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  11.     name </span>= Column(String(32), index=True, nullable=<span style="color: #000000;">False)
  12.     </span><span style="color: #008000;">#</span><span style="color: #008000;"> email = Column(String(32), unique=True)</span>
  13.     <span style="color: #008000;">#</span><span style="color: #008000;"> ctime = Column(DateTime, default=datetime.datetime.now)</span>
  14.     <span style="color: #008000;">#</span><span style="color: #008000;"> extra = Column(Text, nullable=True)</span>
  15.     <span style="color: #800080;">__table_args__</span> =<span style="color: #000000;"> (
  16.         </span><span style="color: #008000;">#</span><span style="color: #008000;"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
  17.         <span style="color: #008000;">#</span><span style="color: #008000;"> Index(‘ix_id_name‘, ‘name‘, ‘email‘),</span>
  18. <span style="color: #000000;">    )
  19. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> init_db():
  20.     </span><span style="color: #800000;">"""</span><span style="color: #800000;">
  21.     根据类创建数据库表
  22.     :return: 
  23.     </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  24.     engine </span>=<span style="color: #000000;"> create_engine(
  25.         </span><span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  26.         max_overflow</span>=0,  <span style="color: #008000;">#</span><span style="color: #008000;"> 超过连接池大小外最多创建的连接</span>
  27.         pool_size=5,  <span style="color: #008000;">#</span><span style="color: #008000;"> 连接池大小</span>
  28.         pool_timeout=30,  <span style="color: #008000;">#</span><span style="color: #008000;"> 池中没有线程最多等待的时间，否则报错</span>
  29.         pool_recycle=-1  <span style="color: #008000;">#</span><span style="color: #008000;"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  30. <span style="color: #000000;">    )
  31.     Base.metadata.create_all(engine)
  32. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> drop_db():
  33.     </span><span style="color: #800000;">"""</span><span style="color: #800000;">
  34.     根据类删除数据库表
  35.     :return: 
  36.     </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  37.     engine </span>=<span style="color: #000000;"> create_engine(
  38.         </span><span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  39.         max_overflow</span>=0,  <span style="color: #008000;">#</span><span style="color: #008000;"> 超过连接池大小外最多创建的连接</span>
  40.         pool_size=5,  <span style="color: #008000;">#</span><span style="color: #008000;"> 连接池大小</span>
  41.         pool_timeout=30,  <span style="color: #008000;">#</span><span style="color: #008000;"> 池中没有线程最多等待的时间，否则报错</span>
  42.         pool_recycle=-1  <span style="color: #008000;">#</span><span style="color: #008000;"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  43. <span style="color: #000000;">    )
  44.     Base.metadata.drop_all(engine)
  45. </span><span style="color: #0000ff;">if</span> <span style="color: #800080;">__name__</span> == <span style="color: #800000;">‘</span><span style="color: #800000;">__main__</span><span style="color: #800000;">‘</span><span style="color: #000000;">:
  46.     drop_db()
  47.     init_db()</span>
创建单表 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> datetime
  4. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> relationship
  8. Base </span>=<span style="color: #000000;"> declarative_base()
  9. </span><span style="color: #008000;">#</span><span style="color: #008000;"> ##################### 单表示例 #########################</span>
  10. <span style="color: #0000ff;">class</span><span style="color: #000000;"> Users(Base):
  11.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">users</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  12.     id </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  13.     name </span>= Column(String(32), index=<span style="color: #000000;">True)
  14.     age </span>= Column(Integer, default=18<span style="color: #000000;">)
  15.     email </span>= Column(String(32), unique=<span style="color: #000000;">True)
  16.     ctime </span>= Column(DateTime, default=<span style="color: #000000;">datetime.datetime.now)
  17.     extra </span>= Column(Text, nullable=<span style="color: #000000;">True)
  18.     </span><span style="color: #800080;">__table_args__</span> =<span style="color: #000000;"> (
  19.         </span><span style="color: #008000;">#</span><span style="color: #008000;"> UniqueConstraint(‘id‘, ‘name‘, name=‘uix_id_name‘),</span>
  20.         <span style="color: #008000;">#</span><span style="color: #008000;"> Index(‘ix_id_name‘, ‘name‘, ‘extra‘),</span>
  21. <span style="color: #000000;">    )
  22. </span><span style="color: #0000ff;">class</span><span style="color: #000000;"> Hosts(Base):
  23.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">hosts</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  24.     id </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  25.     name </span>= Column(String(32), index=<span style="color: #000000;">True)
  26.     ctime </span>= Column(DateTime, default=<span style="color: #000000;">datetime.datetime.now)
  27. </span><span style="color: #008000;">#</span><span style="color: #008000;"> ##################### 一对多示例 #########################</span>
  28. <span style="color: #0000ff;">class</span><span style="color: #000000;"> Hobby(Base):
  29.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">hobby</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  30.     id </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  31.     caption </span>= Column(String(50), default=<span style="color: #800000;">‘</span><span style="color: #800000;">篮球</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
  32. </span><span style="color: #0000ff;">class</span><span style="color: #000000;"> Person(Base):
  33.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">person</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  34.     nid </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  35.     name </span>= Column(String(32), index=True, nullable=<span style="color: #000000;">True)
  36.     hobby_id </span>= Column(Integer, ForeignKey(<span style="color: #800000;">"</span><span style="color: #800000;">hobby.id</span><span style="color: #800000;">"</span><span style="color: #000000;">))
  37.     </span><span style="color: #008000;">#</span><span style="color: #008000;"> 与生成表结构无关，仅用于查询方便</span>
  38.     hobby = relationship(<span style="color: #800000;">"</span><span style="color: #800000;">Hobby</span><span style="color: #800000;">"</span>, backref=<span style="color: #800000;">‘</span><span style="color: #800000;">pers</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
  39. </span><span style="color: #008000;">#</span><span style="color: #008000;"> ##################### 多对多示例 #########################</span>
  40. <span style="color: #0000ff;">class</span><span style="color: #000000;"> Server2Group(Base):
  41.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">server2group</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  42.     id </span>= Column(Integer, primary_key=True, autoincrement=<span style="color: #000000;">True)
  43.     server_id </span>= Column(Integer, ForeignKey(<span style="color: #800000;">‘</span><span style="color: #800000;">server.id</span><span style="color: #800000;">‘</span><span style="color: #000000;">))
  44.     group_id </span>= Column(Integer, ForeignKey(<span style="color: #800000;">‘</span><span style="color: #800000;">group.id</span><span style="color: #800000;">‘</span><span style="color: #000000;">))
  45. </span><span style="color: #0000ff;">class</span><span style="color: #000000;"> Group(Base):
  46.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">group</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  47.     id </span>= Column(Integer, primary_key=<span style="color: #000000;">True)
  48.     name </span>= Column(String(64), unique=True, nullable=<span style="color: #000000;">False)
  49.     </span><span style="color: #008000;">#</span><span style="color: #008000;"> 与生成表结构无关，仅用于查询方便</span>
  50.     servers = relationship(<span style="color: #800000;">‘</span><span style="color: #800000;">Server</span><span style="color: #800000;">‘</span>, secondary=<span style="color: #800000;">‘</span><span style="color: #800000;">server2group</span><span style="color: #800000;">‘</span>, backref=<span style="color: #800000;">‘</span><span style="color: #800000;">groups</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
  51. </span><span style="color: #0000ff;">class</span><span style="color: #000000;"> Server(Base):
  52.     </span><span style="color: #800080;">__tablename__</span> = <span style="color: #800000;">‘</span><span style="color: #800000;">server</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  53.     id </span>= Column(Integer, primary_key=True, autoincrement=<span style="color: #000000;">True)
  54.     hostname </span>= Column(String(64), unique=True, nullable=<span style="color: #000000;">False)
  55. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> init_db():
  56.     </span><span style="color: #800000;">"""</span><span style="color: #800000;">
  57.     根据类创建数据库表
  58.     :return: 
  59.     </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  60.     engine </span>=<span style="color: #000000;"> create_engine(
  61.         </span><span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  62.         max_overflow</span>=0,  <span style="color: #008000;">#</span><span style="color: #008000;"> 超过连接池大小外最多创建的连接</span>
  63.         pool_size=5,  <span style="color: #008000;">#</span><span style="color: #008000;"> 连接池大小</span>
  64.         pool_timeout=30,  <span style="color: #008000;">#</span><span style="color: #008000;"> 池中没有线程最多等待的时间，否则报错</span>
  65.         pool_recycle=-1  <span style="color: #008000;">#</span><span style="color: #008000;"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  66. <span style="color: #000000;">    )
  67.     Base.metadata.create_all(engine)
  68. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> drop_db():
  69.     </span><span style="color: #800000;">"""</span><span style="color: #800000;">
  70.     根据类删除数据库表
  71.     :return: 
  72.     </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  73.     engine </span>=<span style="color: #000000;"> create_engine(
  74.         </span><span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span><span style="color: #000000;">,
  75.         max_overflow</span>=0,  <span style="color: #008000;">#</span><span style="color: #008000;"> 超过连接池大小外最多创建的连接</span>
  76.         pool_size=5,  <span style="color: #008000;">#</span><span style="color: #008000;"> 连接池大小</span>
  77.         pool_timeout=30,  <span style="color: #008000;">#</span><span style="color: #008000;"> 池中没有线程最多等待的时间，否则报错</span>
  78.         pool_recycle=-1  <span style="color: #008000;">#</span><span style="color: #008000;"> 多久之后对线程池中的线程进行一次连接的回收（重置）</span>
  79. <span style="color: #000000;">    )
  80.     Base.metadata.drop_all(engine)
  81. </span><span style="color: #0000ff;">if</span> <span style="color: #800080;">__name__</span> == <span style="color: #800000;">‘</span><span style="color: #800000;">__main__</span><span style="color: #800000;">‘</span><span style="color: #000000;">:
  82.     drop_db()
  83.     init_db()</span>
创建多个表并包含Fk、M2M关系

b. 操作数据库表

?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 #!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users   engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine)   # 每次执行数据库操作时，都需要创建一个session session = Session()   # ############# 执行ORM操作 ############# obj1 = Users(name="alex1") session.add(obj1)   # 提交事务 session.commit() # 关闭session session.close()
技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users
  10. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  11. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  12. </span><span style="color: #0000ff;">def</span><span style="color: #000000;"> task(arg):
  13.     session </span>=<span style="color: #000000;"> Session()
  14.     obj1 </span>= Users(name=<span style="color: #800000;">"</span><span style="color: #800000;">alex1</span><span style="color: #800000;">"</span><span style="color: #000000;">)
  15.     session.add(obj1)
  16.     session.commit()
  17. </span><span style="color: #0000ff;">for</span> i <span style="color: #0000ff;">in</span> range(10<span style="color: #000000;">):
  18.     t </span>= threading.Thread(target=task, args=<span style="color: #000000;">(i,))
  19.     t.start()</span>
多线程执行示例 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> text
  10. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users, Hosts
  11. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  12. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  13. session </span>=<span style="color: #000000;"> Session()
  14. </span><span style="color: #008000;">#</span><span style="color: #008000;"> ################ 添加 ################</span>
  15. <span style="color: #800000;">"""</span><span style="color: #800000;">
  16. obj1 = Users(name="wupeiqi")
  17. session.add(obj1)
  18. session.add_all([
  19.     Users(name="wupeiqi"),
  20.     Users(name="alex"),
  21.     Hosts(name="c1.com"),
  22. ])
  23. session.commit()
  24. </span><span style="color: #800000;">"""</span>
  25. <span style="color: #008000;">#</span><span style="color: #008000;"> ################ 删除 ################</span>
  26. <span style="color: #800000;">"""</span><span style="color: #800000;">
  27. session.query(Users).filter(Users.id > 2).delete()
  28. session.commit()
  29. </span><span style="color: #800000;">"""</span>
  30. <span style="color: #008000;">#</span><span style="color: #008000;"> ################ 修改 ################</span>
  31. <span style="color: #800000;">"""</span><span style="color: #800000;">
  32. session.query(Users).filter(Users.id > 0).update({"name" : "099"})
  33. session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False)
  34. session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
  35. session.commit()
  36. </span><span style="color: #800000;">"""</span>
  37. <span style="color: #008000;">#</span><span style="color: #008000;"> ################ 查询 ################</span>
  38. <span style="color: #800000;">"""</span><span style="color: #800000;">
  39. r1 = session.query(Users).all()
  40. r2 = session.query(Users.name.label(‘xx‘), Users.age).all()
  41. r3 = session.query(Users).filter(Users.name == "alex").all()
  42. r4 = session.query(Users).filter_by(name=‘alex‘).all()
  43. r5 = session.query(Users).filter_by(name=‘alex‘).first()
  44. r6 = session.query(Users).filter(text("id<span style="color: #800000;">"""</span><span style="color: #000000;">
  45. session.close()</span></span>
基本增删改查示例 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">　条件</span>
  2. ret = session.query(Users).filter_by(name=<span style="color: #800000;">‘</span><span style="color: #800000;">alex</span><span style="color: #800000;">‘</span><span style="color: #000000;">).all()
  3. ret </span>= session.query(Users).filter(Users.id > 1, Users.name == <span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span><span style="color: #000000;">).all()
  4. ret </span>= session.query(Users).filter(Users.id.between(1, 3), Users.name == <span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span><span style="color: #000000;">).all()
  5. ret </span>= session.query(Users).filter(Users.id.in_([1,3,4<span style="color: #000000;">])).all()
  6. ret </span>= session.query(Users).filter(~Users.id.in_([1,3,4<span style="color: #000000;">])).all()
  7. ret </span>= session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name=<span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span><span style="color: #000000;">))).all()
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> and_, or_
  9. ret </span>= session.query(Users).filter(and_(Users.id > 3, Users.name == <span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span><span style="color: #000000;">)).all()
  10. ret </span>= session.query(Users).filter(or_(Users.id < 2, Users.name == <span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span><span style="color: #000000;">)).all()
  11. ret </span>=<span style="color: #000000;"> session.query(Users).filter(
  12.     or_(
  13.         Users.id </span>< 2<span style="color: #000000;">,
  14.         and_(Users.name </span>== <span style="color: #800000;">‘</span><span style="color: #800000;">eric</span><span style="color: #800000;">‘</span>, Users.id > 3<span style="color: #000000;">),
  15.         Users.extra </span>!= <span style="color: #800000;">""</span><span style="color: #000000;">
  16.     )).all()
  17. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 通配符</span>
  18. ret = session.query(Users).filter(Users.name.like(<span style="color: #800000;">‘</span><span style="color: #800000;">e%</span><span style="color: #800000;">‘</span><span style="color: #000000;">)).all()
  19. ret </span>= session.query(Users).filter(~Users.name.like(<span style="color: #800000;">‘</span><span style="color: #800000;">e%</span><span style="color: #800000;">‘</span><span style="color: #000000;">)).all()
  20. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 限制</span>
  21. ret = session.query(Users)[1:2<span style="color: #000000;">]
  22. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 排序</span>
  23. ret =<span style="color: #000000;"> session.query(Users).order_by(Users.name.desc()).all()
  24. ret </span>=<span style="color: #000000;"> session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
  25. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 分组</span>
  26. <span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> func
  27. ret </span>=<span style="color: #000000;"> session.query(Users).group_by(Users.extra).all()
  28. ret </span>=<span style="color: #000000;"> session.query(
  29.     func.max(Users.id),
  30.     func.sum(Users.id),
  31.     func.min(Users.id)).group_by(Users.name).all()
  32. ret </span>=<span style="color: #000000;"> session.query(
  33.     func.max(Users.id),
  34.     func.sum(Users.id),
  35.     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) </span>>2<span style="color: #000000;">).all()
  36. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 连表</span>
  37. <span style="color: #000000;">
  38. ret </span>= session.query(Users, Favor).filter(Users.id ==<span style="color: #000000;"> Favor.nid).all()
  39. ret </span>=<span style="color: #000000;"> session.query(Person).join(Favor).all()
  40. ret </span>= session.query(Person).join(Favor, isouter=<span style="color: #000000;">True).all()
  41. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 组合</span>
  42. q1 = session.query(Users.name).filter(Users.id > 2<span style="color: #000000;">)
  43. q2 </span>= session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000;">)
  44. ret </span>=<span style="color: #000000;"> q1.union(q2).all()
  45. q1 </span>= session.query(Users.name).filter(Users.id > 2<span style="color: #000000;">)
  46. q2 </span>= session.query(Favor.caption).filter(Favor.nid < 2<span style="color: #000000;">)
  47. ret </span>= q1.union_all(q2).all()
常用操作 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> text
  10. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.result <span style="color: #0000ff;">import</span><span style="color: #000000;"> ResultProxy
  11. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users, Hosts
  12. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  13. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  14. session </span>=<span style="color: #000000;"> Session()
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 查询</span><span style="color: #008000;">
  16. #</span><span style="color: #008000;"> cursor = session.execute(‘select * from users‘)</span><span style="color: #008000;">
  17. #</span><span style="color: #008000;"> result = cursor.fetchall()</span>
  18. <span style="color: #008000;">#</span><span style="color: #008000;"> 添加</span>
  19. cursor = session.execute(<span style="color: #800000;">‘</span><span style="color: #800000;">insert into users(name) values(:value)</span><span style="color: #800000;">‘</span>,params={<span style="color: #800000;">"</span><span style="color: #800000;">value</span><span style="color: #800000;">"</span>:<span style="color: #800000;">‘</span><span style="color: #800000;">wupeiqi</span><span style="color: #800000;">‘</span><span style="color: #000000;">})
  20. session.commit()
  21. </span><span style="color: #0000ff;">print</span><span style="color: #000000;">(cursor.lastrowid)
  22. session.close()</span>
原生SQL语句 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> text
  10. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.result <span style="color: #0000ff;">import</span><span style="color: #000000;"> ResultProxy
  11. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users, Hosts, Hobby, Person
  12. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  13. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  14. session </span>=<span style="color: #000000;"> Session()
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 添加</span>
  16. <span style="color: #800000;">"""</span><span style="color: #800000;">
  17. session.add_all([
  18.     Hobby(caption=‘乒乓球‘),
  19.     Hobby(caption=‘羽毛球‘),
  20.     Person(name=‘张三‘, hobby_id=3),
  21.     Person(name=‘李四‘, hobby_id=4),
  22. ])
  23. person = Person(name=‘张九‘, hobby=Hobby(caption=‘姑娘‘))
  24. session.add(person)
  25. hb = Hobby(caption=‘人妖‘)
  26. hb.pers = [Person(name=‘文飞‘), Person(name=‘博雅‘)]
  27. session.add(hb)
  28. session.commit()
  29. </span><span style="color: #800000;">"""</span>
  30. <span style="color: #008000;">#</span><span style="color: #008000;"> 使用relationship正向查询</span>
  31. <span style="color: #800000;">"""</span><span style="color: #800000;">
  32. v = session.query(Person).first()
  33. print(v.name)
  34. print(v.hobby.caption)
  35. </span><span style="color: #800000;">"""</span>
  36. <span style="color: #008000;">#</span><span style="color: #008000;"> 使用relationship反向查询</span>
  37. <span style="color: #800000;">"""</span><span style="color: #800000;">
  38. v = session.query(Hobby).first()
  39. print(v.caption)
  40. print(v.pers)
  41. </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  42. session.close()</span>
基于relationship操作ForeignKey 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> text
  10. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.result <span style="color: #0000ff;">import</span><span style="color: #000000;"> ResultProxy
  11. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users, Hosts, Hobby, Person, Group, Server, Server2Group
  12. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  13. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  14. session </span>=<span style="color: #000000;"> Session()
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 添加</span>
  16. <span style="color: #800000;">"""</span><span style="color: #800000;">
  17. session.add_all([
  18.     Server(hostname=‘c1.com‘),
  19.     Server(hostname=‘c2.com‘),
  20.     Group(name=‘A组‘),
  21.     Group(name=‘B组‘),
  22. ])
  23. session.commit()
  24. s2g = Server2Group(server_id=1, group_id=1)
  25. session.add(s2g)
  26. session.commit()
  27. gp = Group(name=‘C组‘)
  28. gp.servers = [Server(hostname=‘c3.com‘),Server(hostname=‘c4.com‘)]
  29. session.add(gp)
  30. session.commit()
  31. ser = Server(hostname=‘c6.com‘)
  32. ser.groups = [Group(name=‘F组‘),Group(name=‘G组‘)]
  33. session.add(ser)
  34. session.commit()
  35. </span><span style="color: #800000;">"""</span>
  36. <span style="color: #008000;">#</span><span style="color: #008000;"> 使用relationship正向查询</span>
  37. <span style="color: #800000;">"""</span><span style="color: #800000;">
  38. v = session.query(Group).first()
  39. print(v.name)
  40. print(v.servers)
  41. </span><span style="color: #800000;">"""</span>
  42. <span style="color: #008000;">#</span><span style="color: #008000;"> 使用relationship反向查询</span>
  43. <span style="color: #800000;">"""</span><span style="color: #800000;">
  44. v = session.query(Server).first()
  45. print(v.hostname)
  46. print(v.groups)
  47. </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  48. session.close()</span>
基于relationship操作m2m 技术分享图片 
  1. <span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
  2. #</span><span style="color: #008000;"> -*- coding:utf-8 -*-</span>
  3. <span style="color: #0000ff;">import</span><span style="color: #000000;"> time
  4. </span><span style="color: #0000ff;">import</span><span style="color: #000000;"> threading
  5. </span><span style="color: #0000ff;">from</span> sqlalchemy.ext.declarative <span style="color: #0000ff;">import</span><span style="color: #000000;"> declarative_base
  6. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> Column, Integer, String, ForeignKey, UniqueConstraint, Index
  7. </span><span style="color: #0000ff;">from</span> sqlalchemy.orm <span style="color: #0000ff;">import</span><span style="color: #000000;"> sessionmaker, relationship
  8. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> create_engine
  9. </span><span style="color: #0000ff;">from</span> sqlalchemy.sql <span style="color: #0000ff;">import</span><span style="color: #000000;"> text, func
  10. </span><span style="color: #0000ff;">from</span> sqlalchemy.engine.result <span style="color: #0000ff;">import</span><span style="color: #000000;"> ResultProxy
  11. </span><span style="color: #0000ff;">from</span> db <span style="color: #0000ff;">import</span><span style="color: #000000;"> Users, Hosts, Hobby, Person, Group, Server, Server2Group
  12. engine </span>= create_engine(<span style="color: #800000;">"</span><span style="color: #800000;">mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8</span><span style="color: #800000;">"</span>, max_overflow=0, pool_size=5<span style="color: #000000;">)
  13. Session </span>= sessionmaker(bind=<span style="color: #000000;">engine)
  14. session </span>=<span style="color: #000000;"> Session()
  15. </span><span style="color: #008000;">#</span><span style="color: #008000;"> 关联子查询</span>
  16. subqry = session.query(func.count(Server.id).label(<span style="color: #800000;">"</span><span style="color: #800000;">sid</span><span style="color: #800000;">"</span>)).filter(Server.id ==<span style="color: #000000;"> Group.id).correlate(Group).as_scalar()
  17. result </span>=<span style="color: #000000;"> session.query(Group.name, subqry)
  18. </span><span style="color: #800000;">"""</span><span style="color: #800000;">
  19. SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid 
  20. FROM server 
  21. WHERE server.id = `group`.id) AS anon_1 
  22. FROM `group`
  23. </span><span style="color: #800000;">"""</span>
  24. <span style="color: #008000;">#</span><span style="color: #008000;"> 原生SQL</span>
  25. <span style="color: #800000;">"""</span><span style="color: #800000;">
  26. # 查询
  27. cursor = session.execute(‘select * from users‘)
  28. result = cursor.fetchall()
  29. # 添加
  30. cursor = session.execute(‘insert into users(name) values(:value)‘,params={"value":‘wupeiqi‘})
  31. session.commit()
  32. print(cursor.lastrowid)
  33. </span><span style="color: #800000;">"""</span><span style="color: #000000;">
  34. session.close()</span>
其他

 

　　

Flask-SQLAlchemy

标签：install   reads   session   table   架构   类型   反向   _id   insert   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！