SQLAlchemy orm query

SQLAlchemy的ORM是一个映射函数（Ｍapper），将Python中定义的 类与数据库中的 表建立关联，以及类的 实例（instance）和表的 行（row）建立关联 。 

查看一个类所对应的数据库表，使用__tablename__属性，例如 User.__tablename__

1. 查询数据 （query）

1.1 查询一个trace中flow个数（to count flows of specific trace）

session.query(Flow).filter(Flow.trace_id == 1) .count()

1.2. 查询一个trace中不同srcIP的个数 （to count distinct srcIP）

from sqlalchemy import distinct

from config import *

session = DBSession()

session.query( Flow.srcIP).filter(Flow.trace_id == 1). distinct(). count()

1.3 查询一个trace中不同的dstIP和dstPort对的个数（to count distinct dstIP and dstPort）

session.query( Flow.dstIP, Flow.dstPort).filter(Flow.trace_id == 1).distinct().count()

1.4 查询指定列的数据，返回一个KeyedTuple数据类型的列表( get a tuple list of specified columns )

n = session.query(Flow.dstIP, Flow.dstPort).filter(Flow.trace_id == 1) .all()

# The type of n is list.

# The type of n[0] is sqlalchemy.util._collections. KeyedTuple

1.5 查询指定列中的所有不同值( get a distinct tuple list of specified columns)

n = session.query(Flow.dstIP, Flow.dstPort).filter(Flow.trace_id == 1) .distinct().all()

1.6 获得一列数据的平均值（get average value of a column）

# sql language： select avg(txPkt) from Flow

from sqlalchemy.sql import func

q = session.query( func.avg(Flow.txPkt)).filter(Flow.trace_id == 1)

print q[0][0]

# The type of q is sqlalchemy.orm.query.Query

# The type of q[0] is sqlalchemy.util._collections.KeyedTuple

# The type of q[0][0] is decimal.Decimal

1.7 多列数据平均值的计算（compute average values of columns）

q = session.query((func.avg(Flow.txPkt) +func.avg(Flow.rxPkt)) /2).filter(Flow.trace_id == 1)

1.8 对查询到的数据排序（order by ）

from sqlalchemy import desc

q = session.query(Flow.timestamp).filter(trace_id == 1). order_by(desc(Flow.timestamp))

1.9 分组查询

q = session.query(Flow.dstIP, Flow.dstPort, func.count(Flow.id)).filter(Flow.trace_id == tid). group_by(Flow.dstIP, Flow.dstPort).all()

2 查询中，常用的过滤操作

等于(equals)， 例如 query.filter(name   == 'Jack')

不等于(not equals)， 例如 query.filter(name  != 'Jack')

在列表中(in)， 例如 query.filter(name. in_(['Micheal', 'Bob', 'Jack']))

不在列表中(not in)， 例如 query.filter(~name.in_(['Micheal', 'Bob', 'Jack']))

空值(null), 例如 query.filter(name  == None)

不是空值(not null), 例如 query.filter(name  != None)

与(and), 例如 query.filter( and_(name == 'Andy', fullname == 'Andy Liu' ))

and_可以省略， 例如 query.filter(name=='Andy', fullname==‘Andy Liu')

或(or), 例如 query.filter( or_(name == 'Andy', name == 'Micheal'))

2. 表的数据操作（table data operation）

2.1 添加\删除一个column ( add a new column to a table)

from db import engine

from sqlalchemy import DDL

add_column =  DDL('alter table  Flow  add column cluster_id integer after trace_id')

drop_column =  DDL('alter table Flow  drop column microsecond')

engine. execute(add_column)

engine. execute(drop_column)

2.2 修改一个数据（update  a value）

session.query(Flow).filter(Flow.dstIP == dstIP, Flow.dstPort == dstPort, Flow.trace_id == 1). update({'cluster_id' : 0})

2.3 插入一行数据（insert a row）

      session = DBSession()

      cluster = Clusters(trace_id = tid, cluster_id = cid, \

                                   dstIP = dIP, dstPort = dPort, \

                                   avgPkt = aPkt, avgByte = aByte, \

                                   size = count)

      session. add(cluster)

      session.commit() # commit or flush

      session.close()

2.4  删除一行数据（delete a row ）

      session = DBSession()

      session.query(Clusters).filter(Clusters.trace_id = 2).delete()

      session.commit() # commit or flush

      session.close()

补充：

外键 ForeignKey只能引用外表的指定列中已经存在的值。

几种常见sqlalchemy查询：

?

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 30 31 32 33

#简单查询      print (session.query(User). all ())      print (session.query(User.name, User.fullname). all ())      print (session.query(User, User.name). all ())            #带条件查询      print (session.query(User).filter_by(name = 'user1' ). all ())      print (session.query(User). filter (User.name  = =  "user" ). all ())      print (session.query(User). filter (User.name.like( "user%" )). all ())            #多条件查询      print (session.query(User). filter (and_(User.name.like( "user%" ), User.fullname.like( "first%" ))). all ())      print (session.query(User). filter (or_(User.name.like( "user%" ), User.password ! =  None )). all ())            #sql过滤      print (session.query(User). filter ( "id>:id" ).params( id = 1 ). all ())            #关联查询       print (session.query(User, Address). filter (User. id  = =  Address.user_id). all ())      print (session.query(User).join(User.addresses). all ())      print (session.query(User).outerjoin(User.addresses). all ())            #聚合查询      print (session.query(User.name, func.count( '*' ).label( "user_count" )).group_by(User.name). all ())      print (session.query(User.name, func. sum (User. id ).label( "user_id_sum" )).group_by(User.name). all ())            #子查询      stmt  =  session.query(Address.user_id, func.count( '*' ).label( "address_count" )).group_by(Address.user_id).subquery()      print (session.query(User, stmt.c.address_count).outerjoin((stmt, User. id  = =  stmt.c.user_id)).order_by(User. id ). all ())            #exists      print (session.query(User). filter (exists().where(Address.user_id  = =  User. id )))      print (session.query(User). filter (User.addresses. any ()))

限制返回字段查询

?

1 2 3

person  =  session.query(Person.name, Person.created_at,                                    Person.updated_at).filter_by(name = "zhongwei" ).order_by(                           Person.created_at).first()

记录总数查询：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

from  sqlalchemy  import  func   # count User records, without # using a subquery. session.query(func.count(User. id ))   # return count of user "id" grouped # by "name" session.query(func.count(User. id )).\          group_by(User.name)   from  sqlalchemy  import  distinct   # count distinct "name" values session.query(func.count(distinct(User.name))) 每一个session表示一个事务，关闭session时： 首先预提交： session.flush() 然后提交： session.commit() 最后关闭： session.close()