Tornado实战之数据库表操作详解

1.表的创建及数据状态分析（增删改）

Base.metadata.create_all()
将所有salalchemy管理的对象同步到数据库中产生对应的数据表

临时状态(游离状态)：程序中直接创建的对象，临时对象
特点：程序中有数据，缓存中无数据，数据库中无数据

    p = Person(name="jerry", age=12)
    #保存并运行在内存中的~一旦程序结束，内存中的数据会清空,此时 p 对象 无 id .

缓存状态(托管状态)：只是存在于连接会话缓存中，数据库中并没有相关数据，缓存对象。
特点：程序中有数据，缓存中有数据，数据库中无数据

    session.add(p)  #此时还是没有 id
    #程序中的对象，可以通过连接会话session的add()函数，将对象交给 sqlalchemy 进行管理

持久状态(持久persistent状态)：对象在程序中存在，在数据库中有对应的记录
特点：程序中有数据{id}， 缓存中有数据， 数据库中有数据

    session.commit()
    # 缓存中的数据，可以通过连接会话session的commit()函数，将缓存数据提交给数据库进行持久化保存

修改操作
特点：一旦对缓存状态的对象进行修改，此时缓存对象和数据库中的数据不一致~~就会形成脏数据，脏数据并不是不可取的，更新操作就是将这样的数据从缓存同步到数据库(commit)

    p.name = "shuke"
    # 可以通过session.dirty来查询缓存中的脏数据
    session.commit()

删除操作

    session.delete(p)
    # 直接删除一个缓存的数据[脏数据]，通过commit()提交到数据库
    session.commit()

    # 注意删除的只能是持久对象
    #p2 = Person(id=1)
    #session.delete(p2)
    # 抛出异常~不能删除，因为p2不是持久对象is not persisted

2. 查询操作

1、全表查询

    #1.1. 直接调用query，就相当于默认调用了all()进行了全表查询
    person_list = sess.query(Person)
    # 1.2. 直接通过all()函数指定进行全表查询
    person_list = sess.query(Person).all()

2、排序查询

    # 通过order_by()函数可以直接指定排序方式
    # 2.1. 通过 类型.属性 指定按照什么属性进行默认顺序排序
    person_list = sess.query(Person).order_by(Person.id)
    # 2.2. 通过 -类型.属性 指定按照什么属性进行倒序排序
    person_list = sess.query(Person).order_by(-Person.id)
    # 2.3. 通过 类型.属性,类型.属性~指定多列，表示按照多列进行排序，
    #~如果第一列数据相同，按照第二列进行排序，以此类推。
    person_list = sess.query(Person).order_by(Person.name, Person.age)

3、指定列查询

    # 模拟sql语句中的指定列查询 select p.name from person p;
    # 生成的sql语句：SELECT persons.name AS persons_name FROM persons
    #只查找person 的name
    person_list = sess.query(Person.name)

4、特殊操作

    #如果类型的字段名称过长，不好书写的情况下，可以指定别名
    #字段属性的别名，在使用过程中可以进行简化处理，也是常见的操作手段
    #[属性名称一般是开发人员自定义的，有可能非常长还没有太大必要，此时操作时就需要简化]
    person_list = sess.query(Person.name.label("n"))
    for p in person_list:
         print(p.n)   # 此时  p.n ==  p.name

  #如果类型过长，不好书写的情况下，可以给类型指定一个别名
  #类型名称的别名，慎重使用~
  #类型的名称一般都已经是比较简化的名称并且能直接表述其正式意义的名称，慎重简化！
  #先导入模块
  from sqlalchemy.orm import aliased
  p = aliased(Person, name="p")
  ......
  person_list = sess.query(p).all()  #此时的  p  相当于 Person 

5、切片查询

    #一般使用在数据分页查询等这样的业务中，切片查询
    # 由于sqlalchemy查询的结果就是一个like list的存在，所以直接用Python的切片即可。
    # 如果网页页面中的数据~需要分页展示，每页两条记录
    #~start = (pageno-1)*pagecount, end: start+pagecount
    person_list = sess.query(Person).all()[:2] # 第一页数据  [0, 2]
    person_list = sess.query(Person).all()[2:4] # 第二页数据 [2, 4]

 

6、filter()[常用]条件查询

6.1. 等值查询 | 非等值

    person_list = sess.query(Person).filter(Person.name!="tom")
    person_list = sess.query(Person).filter(Person.name=="tom")

6.2. 模糊查询

    #name 里包含 e 字母的用户
    person_list = sess.query(Person).filter(Person.name.like('%e%'))

6.3. 范围查询：in 和 not i

    # in   查询名字叫Tom 和Jerry 的所有人
    person_list = sess.query(Person).filter(Person.name.in_(['tom', 'jerry']))
    # not in   查询名字不叫Tom 和Jerry 的所有人
    person_list = sess.query(Person).filter(~Person.name.in_(['tom', 'jerry']))

6.4. 空值查询 is null / is not nul

    # 空值
    person_list = sess.query(Person).filter(Person.name == None) # 常用~但是不是规范
    person_list= sess.query(Person).filter(Person.name.is_(None)) # 不太常用~pep8编码规范推荐
    # 非空值
    person_list = sess.query(Person).filter(Person.name != None)
    person_list = sess.query(Person).filter(Person.name.isnot(None))

6.5. 并且条件 和 或者条件 查询

    # 并且条件的查询，有三种实现模式
    # 第一种：多个filter
    person_list = sess.query(Person).filter(Person.name=="tom").filter(Person.age==12)
    # 第二种：一个filter，多个条件
    person_list = sess.query(Person).filter(Person.name=="tom", Person.age==12)
    # 第三种：规范并且条件查询方式：通过and_()函数收集条件
    from sqlalchemy import and_
    person_list = sess.query(Person).filter(and_(Person.name=='tom', Person.age==12))

    # 或者查询：or 查询，通过or_()函数进行查询
    from sqlalchemy import or_
    person_list = sess.query(Person).filter(or_(Person.name=='tom', Person.name=='jerry'))

6.6. 定制化SQL语句查询

    # 导入模块
    from sqlalchemy import text

    person_list = sess.query(Person)\
      .from_statement(text("select p.id, p.name, p.age from persons p where name=:myname"))\
      .params(myname="tom").all()

 

3、SQLAlchemy中的query，filter和filter_by两个函数的区别。

filter：

filter －》 column == expression
传入参数的写法，要用：类名.列名 两个等号 去判断
举例：
query(User.name).filter(User.fullname==’Ed Jones’)
且更复杂的查询的语法，比如_and()，or_()等多个条件的查询，只支持filter
举例：
query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))
query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))

filter_by：

filter_by －》keyword = expression
传入参数的写法，只需要用：（不带类名的）列名 单个等号 就可以判断。
－》filter中，语法更加贴近于，类似于，Python的语法。
举例：
query(User.name).filter_by(fullname=’Ed Jones’)

实例： 

test_user_modules.py
from user_modules import User,session#引入数据模型
 
def add_user():#增加记录
    person = User(username='wawa',password='qwe123')#实例用户表模型
    session.add(person)#会话添加单条记录
    #session.add_all([User(username='wgn4',password='qwe123'),
    User(username='wgn5', password='qwe123')])#支持列表
    session.commit()#执行
 
def search_user():#查询内容
    #row = session.query(User).all()
    row = session.query(User).filter_by(id=1).all()
    print(row[0].username)#[0]代表第一行数据
    print(row)#使用user_modules.py当是 def __repr__(self)函数返回的格式，如果没有这个函数返回的是对象
row = session.query(User).filter_by(username='wgn')
print(row.all())
print(row.count())#取消all()可以得到行数
    print(row)
    print(row[0].locked)#使用了类属性
 
def delete_user():
    row = session.query(User).filter_by(username='wgn53')  
    if row.count() == 1:# 删除符合条件的一行记录
        session.delete(row[0])
        session.commit()
    elif row.count() > 1:# 删除符合条件的多行记录
        row.delete()
        session.commit()
 
def update_user():#修改数据（更新）
    session.query(User).filter_by(username='wgn').update({User.password: 'abc8881', User._locked: 'Ture'})
    session.commit()
 
if __name__=='__main__':
   add_user()

 

参考大佬：

      https://www.jianshu.com/p/3896b632928b

      https://blog.csdn.net/weixin_42670402/article/details/84522877