• SQLAlchemy的使用(SQLAlchemy 是一种对象关系映射模型(Object Relational Mapper), 简称ORM。)


    一. 介绍

    SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上,使用关系对象映射进行数据库操作,简言之便是:将类和对象转换成SQL,然后使用数据API执行SQL并获取执行结果。

     pip install sqlalchemy -i http://pypi.douban.com/simple  --trusted-host pypi.douban.com 

    组成部分:

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

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

    复制代码
    MySQL-Python
        mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
        
    pymysql
        mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
        
    MySQL-Connector
        mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
        
    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语句

    复制代码
    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 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 import sqlalchemy
     6 from sqlalchemy import create_engine
     7 from sqlalchemy.engine.base import Engine
     8 
     9 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5)
    10 
    11 
    12 def task(arg):
    13     conn = engine.contextual_connect()
    14     with conn:
    15         cur = conn.execute(
    16             "select * from t1"
    17         )
    18         result = cur.fetchall()
    19         print(result)
    20 
    21 
    22 for i in range(20):
    23     t = threading.Thread(target=task, args=(i,))
    24     t.start()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 import sqlalchemy
     6 from sqlalchemy import create_engine
     7 from sqlalchemy.engine.base import Engine
     8 from sqlalchemy.engine.result import ResultProxy
     9 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=0, pool_size=5)
    10 
    11 
    12 def task(arg):
    13     cur = engine.execute("select * from t1")
    14     result = cur.fetchall()
    15     cur.close()
    16     print(result)
    17 
    18 
    19 for i in range(20):
    20     t = threading.Thread(target=task, args=(i,))
    21     t.start()
    复制代码

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

    2. ORM

    a. 创建数据库表

    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import datetime
     4 from sqlalchemy import create_engine
     5 from sqlalchemy.ext.declarative import declarative_base
     6 from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
     7 
     8 Base = declarative_base()
     9 
    10 
    11 class Users(Base):
    12     __tablename__ = 'users'
    13 
    14     id = Column(Integer, primary_key=True)
    15     name = Column(String(32), index=True, nullable=False)
    16     # email = Column(String(32), unique=True)
    17     # ctime = Column(DateTime, default=datetime.datetime.now)
    18     # extra = Column(Text, nullable=True)
    19 
    20     __table_args__ = (
    21         # UniqueConstraint('id', 'name', name='uix_id_name'),
    22         # Index('ix_id_name', 'name', 'email'),
    23     )
    24 
    25 
    26 def init_db():
    27     """
    28     根据类创建数据库表
    29     :return: 
    30     """
    31     engine = create_engine(
    32         "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
    33         max_overflow=0,  # 超过连接池大小外最多创建的连接
    34         pool_size=5,  # 连接池大小
    35         pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
    36         pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    37     )
    38 
    39     Base.metadata.create_all(engine)
    40 
    41 
    42 def drop_db():
    43     """
    44     根据类删除数据库表
    45     :return: 
    46     """
    47     engine = create_engine(
    48         "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
    49         max_overflow=0,  # 超过连接池大小外最多创建的连接
    50         pool_size=5,  # 连接池大小
    51         pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
    52         pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    53     )
    54 
    55     Base.metadata.drop_all(engine)
    56 
    57 
    58 if __name__ == '__main__':
    59     drop_db()
    60     init_db()
    复制代码
    复制代码
      1 #!/usr/bin/env python
      2 # -*- coding:utf-8 -*-
      3 import datetime
      4 from sqlalchemy import create_engine
      5 from sqlalchemy.ext.declarative import declarative_base
      6 from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
      7 from sqlalchemy.orm import relationship
      8 
      9 Base = declarative_base()
     10 
     11 
     12 # ##################### 单表示例 #########################
     13 class Users(Base):
     14     __tablename__ = 'users'
     15 
     16     id = Column(Integer, primary_key=True)
     17     name = Column(String(32), index=True)
     18     age = Column(Integer, default=18)
     19     email = Column(String(32), unique=True)
     20     ctime = Column(DateTime, default=datetime.datetime.now)
     21     extra = Column(Text, nullable=True)
     22 
     23     __table_args__ = (
     24         # UniqueConstraint('id', 'name', name='uix_id_name'),
     25         # Index('ix_id_name', 'name', 'extra'),
     26     )
     27 
     28 
     29 class Hosts(Base):
     30     __tablename__ = 'hosts'
     31 
     32     id = Column(Integer, primary_key=True)
     33     name = Column(String(32), index=True)
     34     ctime = Column(DateTime, default=datetime.datetime.now)
     35 
     36 
     37 # ##################### 一对多示例 #########################
     38 class Hobby(Base):
     39     __tablename__ = 'hobby'
     40     id = Column(Integer, primary_key=True)
     41     caption = Column(String(50), default='篮球')
     42 
     43 
     44 class Person(Base):
     45     __tablename__ = 'person'
     46     nid = Column(Integer, primary_key=True)
     47     name = Column(String(32), index=True, nullable=True)
     48     hobby_id = Column(Integer, ForeignKey("hobby.id"))
     49 
     50     # 与生成表结构无关,仅用于查询方便
     51     hobby = relationship("Hobby", backref='pers')
     52 
     53 
     54 # ##################### 多对多示例 #########################
     55 
     56 class Server2Group(Base):
     57     __tablename__ = 'server2group'
     58     id = Column(Integer, primary_key=True, autoincrement=True)
     59     server_id = Column(Integer, ForeignKey('server.id'))
     60     group_id = Column(Integer, ForeignKey('group.id'))
     61 
     62 
     63 class Group(Base):
     64     __tablename__ = 'group'
     65     id = Column(Integer, primary_key=True)
     66     name = Column(String(64), unique=True, nullable=False)
     67 
     68     # 与生成表结构无关,仅用于查询方便
     69     servers = relationship('Server', secondary='server2group', backref='groups')
     70 
     71 
     72 class Server(Base):
     73     __tablename__ = 'server'
     74 
     75     id = Column(Integer, primary_key=True, autoincrement=True)
     76     hostname = Column(String(64), unique=True, nullable=False)
     77 
     78 
     79 def init_db():
     80     """
     81     根据类创建数据库表
     82     :return: 
     83     """
     84     engine = create_engine(
     85         "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
     86         max_overflow=0,  # 超过连接池大小外最多创建的连接
     87         pool_size=5,  # 连接池大小
     88         pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
     89         pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
     90     )
     91 
     92     Base.metadata.create_all(engine)
     93 
     94 
     95 def drop_db():
     96     """
     97     根据类删除数据库表
     98     :return: 
     99     """
    100     engine = create_engine(
    101         "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",
    102         max_overflow=0,  # 超过连接池大小外最多创建的连接
    103         pool_size=5,  # 连接池大小
    104         pool_timeout=30,  # 池中没有线程最多等待的时间,否则报错
    105         pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收(重置)
    106     )
    107 
    108     Base.metadata.drop_all(engine)
    109 
    110 
    111 if __name__ == '__main__':
    112     drop_db()
    113     init_db()
    复制代码

    b. 操作数据库表

    复制代码
    #!/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()

    ######################方式二####################

    # 方式二:支持线程安全,为每个线程创建一个session
    # - threading.Local
    # - 唯一标识
    # ScopedSession对象
    # self.registry(), 加括号 创建session
    # self.registry(), 加括号 创建session
    # self.registry(), 加括号 创建session
    from greenlet import getcurrent as get_ident

    Session = sessionmaker(bind=engine)

    session = scoped_session(Session,get_ident)
    # session.add
    # 操作
    session.remove()



    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from db import Users
    11 
    12 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
    13 Session = sessionmaker(bind=engine)
    14 
    15 
    16 def task(arg):
    17     session = Session()
    18 
    19     obj1 = Users(name="alex1")
    20     session.add(obj1)
    21 
    22     session.commit()
    23 
    24 
    25 for i in range(10):
    26     t = threading.Thread(target=task, args=(i,))
    27     t.start()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from sqlalchemy.sql import text
    11 
    12 from db import Users, Hosts
    13 
    14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
    15 Session = sessionmaker(bind=engine)
    16 
    17 session = Session()
    18 
    19 # ################ 添加 ################
    20 """
    21 obj1 = Users(name="wupeiqi")
    22 session.add(obj1)
    23 
    24 session.add_all([
    25     Users(name="wupeiqi"),
    26     Users(name="alex"),
    27     Hosts(name="c1.com"),
    28 ])
    29 session.commit()
    30 """
    31 
    32 # ################ 删除 ################
    33 """
    34 session.query(Users).filter(Users.id > 2).delete()
    35 session.commit()
    36 """
    37 # ################ 修改 ################
    38 """
    39 session.query(Users).filter(Users.id > 0).update({"name" : "099"})
    40 session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False)
    41 session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
    42 session.commit()
    43 """
    44 # ################ 查询 ################
    45 """
    46 r1 = session.query(Users).all()
    47 r2 = session.query(Users.name.label('xx'), Users.age).all()
    48 r3 = session.query(Users).filter(Users.name == "alex").all()
    49 r4 = session.query(Users).filter_by(name='alex').all()
    50 r5 = session.query(Users).filter_by(name='alex').first()
    51 r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
    52 r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()
    53 """
    54 
    55 
    56 session.close()
    复制代码
    复制代码
     1 # 条件
     2 ret = session.query(Users).filter_by(name='alex').all()
     3 ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
     4 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
     5 ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
     6 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
     7 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
     8 from sqlalchemy import and_, or_
     9 ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
    10 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
    11 ret = session.query(Users).filter(
    12     or_(
    13         Users.id < 2,
    14         and_(Users.name == 'eric', Users.id > 3),
    15         Users.extra != ""
    16     )).all()
    17 
    18 
    19 # 通配符
    20 ret = session.query(Users).filter(Users.name.like('e%')).all()
    21 ret = session.query(Users).filter(~Users.name.like('e%')).all()
    22 
    23 # 限制
    24 ret = session.query(Users)[1:2]
    25 
    26 # 排序
    27 ret = session.query(Users).order_by(Users.name.desc()).all()
    28 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()
    29 
    30 # 分组
    31 from sqlalchemy.sql import func
    32 
    33 ret = session.query(Users).group_by(Users.extra).all()
    34 ret = session.query(
    35     func.max(Users.id),
    36     func.sum(Users.id),
    37     func.min(Users.id)).group_by(Users.name).all()
    38 
    39 ret = session.query(
    40     func.max(Users.id),
    41     func.sum(Users.id),
    42     func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()
    43 
    44 # 连表
    45 
    46 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()
    47 
    48 ret = session.query(Person).join(Favor).all()
    49 
    50 ret = session.query(Person).join(Favor, isouter=True).all()
    51 
    52 
    53 # 组合
    54 q1 = session.query(Users.name).filter(Users.id > 2)
    55 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
    56 ret = q1.union(q2).all()
    57 
    58 q1 = session.query(Users.name).filter(Users.id > 2)
    59 q2 = session.query(Favor.caption).filter(Favor.nid < 2)
    60 ret = q1.union_all(q2).all()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from sqlalchemy.sql import text
    11 from sqlalchemy.engine.result import ResultProxy
    12 from db import Users, Hosts
    13 
    14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)
    15 Session = sessionmaker(bind=engine)
    16 
    17 session = Session()
    18 
    19 # 查询
    20 # cursor = session.execute('select * from users')
    21 # result = cursor.fetchall()
    22 
    23 # 添加
    24 cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})
    25 session.commit()
    26 print(cursor.lastrowid)
    27 
    28 session.close()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from sqlalchemy.sql import text
    11 from sqlalchemy.engine.result import ResultProxy
    12 from db import Users, Hosts, Hobby, Person
    13 
    14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
    15 Session = sessionmaker(bind=engine)
    16 session = Session()
    17 # 添加
    18 """
    19 session.add_all([
    20     Hobby(caption='乒乓球'),
    21     Hobby(caption='羽毛球'),
    22     Person(name='张三', hobby_id=3),
    23     Person(name='李四', hobby_id=4),
    24 ])
    25 
    26 person = Person(name='张九', hobby=Hobby(caption='姑娘'))
    27 session.add(person)
    28 
    29 hb = Hobby(caption='人妖')
    30 hb.pers = [Person(name='文飞'), Person(name='博雅')]
    31 session.add(hb)
    32 
    33 session.commit()
    34 """
    35 
    36 # 使用relationship正向查询
    37 """
    38 v = session.query(Person).first()
    39 print(v.name)
    40 print(v.hobby.caption)
    41 """
    42 
    43 # 使用relationship反向查询
    44 """
    45 v = session.query(Hobby).first()
    46 print(v.caption)
    47 print(v.pers)
    48 """
    49 
    50 session.close()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from sqlalchemy.sql import text
    11 from sqlalchemy.engine.result import ResultProxy
    12 from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group
    13 
    14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
    15 Session = sessionmaker(bind=engine)
    16 session = Session()
    17 # 添加
    18 """
    19 session.add_all([
    20     Server(hostname='c1.com'),
    21     Server(hostname='c2.com'),
    22     Group(name='A组'),
    23     Group(name='B组'),
    24 ])
    25 session.commit()
    26 
    27 s2g = Server2Group(server_id=1, group_id=1)
    28 session.add(s2g)
    29 session.commit()
    30 
    31 
    32 gp = Group(name='C组')
    33 gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')]
    34 session.add(gp)
    35 session.commit()
    36 
    37 
    38 ser = Server(hostname='c6.com')
    39 ser.groups = [Group(name='F组'),Group(name='G组')]
    40 session.add(ser)
    41 session.commit()
    42 """
    43 
    44 
    45 # 使用relationship正向查询
    46 """
    47 v = session.query(Group).first()
    48 print(v.name)
    49 print(v.servers)
    50 """
    51 
    52 # 使用relationship反向查询
    53 """
    54 v = session.query(Server).first()
    55 print(v.hostname)
    56 print(v.groups)
    57 """
    58 
    59 
    60 session.close()
    复制代码
    复制代码
     1 #!/usr/bin/env python
     2 # -*- coding:utf-8 -*-
     3 import time
     4 import threading
     5 
     6 from sqlalchemy.ext.declarative import declarative_base
     7 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
     8 from sqlalchemy.orm import sessionmaker, relationship
     9 from sqlalchemy import create_engine
    10 from sqlalchemy.sql import text, func
    11 from sqlalchemy.engine.result import ResultProxy
    12 from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group
    13 
    14 engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8", max_overflow=0, pool_size=5)
    15 Session = sessionmaker(bind=engine)
    16 session = Session()
    17 
    18 # 关联子查询
    19 subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()
    20 result = session.query(Group.name, subqry)
    21 """
    22 SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid 
    23 FROM server 
    24 WHERE server.id = `group`.id) AS anon_1 
    25 FROM `group`
    26 """
    27 
    28 
    29 # 原生SQL
    30 """
    31 # 查询
    32 cursor = session.execute('select * from users')
    33 result = cursor.fetchall()
    34 
    35 # 添加
    36 cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})
    37 session.commit()
    38 print(cursor.lastrowid)
    39 """
    40 
    41 session.close()
    复制代码

     基本增删改查补充

    复制代码
    #!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import time
    import threading
    
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
    from sqlalchemy.orm import sessionmaker, relationship
    from sqlalchemy import create_engine
    from sqlalchemy.sql import text
    
    from db import Users, Hosts
    
    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()
    
    # ################ 添加 ################
    """
    obj1 = Users(name="wupeiqi")
    session.add(obj1)
    
    #批量添加
    session.add_all([
        Users(name="wupeiqi"),
        Users(name="alex"),
        Hosts(name="c1.com"),
    ])
    session.commit()
    """
    
    # ################ 删除 ################
    """
    session.query(Users).filter(Users.id > 2).delete()
    session.commit()
    """
    # ################ 修改 ################
    """
    session.query(Users).filter(Users.id > 0).update({"name" : "099"})
    session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) #字符串
    session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")  #数字
    session.commit()
    """
    # ################ 查询 ################
    """
    r1 = session.query(Users).all()
    r2 = session.query(Users.name.label('xx'), Users.age).all()   #lable #as xx
    r3 = session.query(Users).filter(Users.name == "alex").all()
    r4 = session.query(Users).filter_by(name='alex').all()   #如果里面写条件就用filter_by,和上面filter查询是一回事,只是一种不同的方式
    r5 = session.query(Users).filter_by(name='alex').first()
    r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()  #查询里面如果有动态传参的时候,吧它包在text里面,:value,:name这样的语法后面用.params来进行格式化
    r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()  #上面的这个方式也可以用这一种 ,直接可以进行SQL语句的查询
    """
    
    
    session.close()
    
    
    复制代码
  • 相关阅读:
    C语言多文件参数传递
    第十章 C++11新特性
    第九章 STL标准库(二)
    第八章 标准模板库STL(一)
    第七章 2.泛型编程(模板)
    第七章 1.输入输出与模板
    第六章 多态
    第五章 继承与派生
    第四章 运算符重载
    第三章 类与对象进阶
  • 原文地址:https://www.cnblogs.com/kcwxx/p/10145850.html
Copyright © 2020-2023  润新知