query函数的可查询数据

#encoding: utf-8

from sqlalchemy import create_engine,Column,Integer,String,Float,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from random import randint

HOSTNAME = '127.0.0.1'

PORT = 3306

DATABASE = 'first_sqlalchemy'

USERNAME = 'root'

PASSWORD = '123456'

#dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/" \
         "{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

engine = create_engine(DB_URI)

Base = declarative_base(engine)

# Session = sessionmaker(engine)
# session = Session()

session = sessionmaker(engine)() #Session(**local_kw)


class Article(Base):
    __tablename__  = 'article'

    id = Column(Integer,primary_key=True,autoincrement=True)

    title = Column(String(50),nullable=False)

    price = Column(Float,nullable=False)

    def __repr__(self):
        return '<Article(title:%s)>'%self.title

# Base.metadata.drop_all()
#
# Base.metadata.create_all()


# for x in range(6):
#     article = Article(title='title%s'%x,price=randint(x,x+1))
#     session.add(article)
#
# session.commit()

#模型对象
# articles = session.query(Article).all() #查出所有的数据
#
# s = [article for article in articles]
#
# print(s)

#模型属性
article = session.query(Article.title,Article.price).all()#根据类的属性查出相应得数据
print(article) #[('title0', 1.0), ('title1', 2.0), ('title2', 3.0), ('title3', 3.0), ('title4', 4.0), ('title5', 5.0)]
#里面是（）元祖

#聚合函数
#func是个类，调用count方法
article_count = session.query(func.count(Article.id)).first()
print(article_count)#(6,)返回得结果是元祖

price_avg = session.query(func.avg(Article.price)).first()
print(price_avg)#(3.0,)

price_max = session.query(func.max(Article.price)).first()
print(price_max)#(5.0,)

price_min = session.query(func.min(Article.price)).all()
print(price_min)#[(1.0,)]

price_sum = session.query(func.sum(Article.price)).first()
print(price_sum)#(18.0,)

#来看下源码func是什么鬼

func = _FunctionGenerator()是这个实例化出来的对象

class _FunctionGenerator(object):
    """Generate :class:`.Function` objects based on getattr calls."""

    def __init__(self, **opts):
        self.__names = []
        self.opts = opts

构造没有func.min这些属性，怎搞出来的，如果对象.__dict__没有这个属性就会执行__getattr__这个方法，所以func.min =_FunctionGenerator(**self.opts)

def __getattr__(self, name):
    # passthru __ attributes; fixes pydoc
    if name.startswith('__'):
        try:
            return self.__dict__[name]
        except KeyError:
            raise AttributeError(name)

    elif name.endswith('_'):
        name = name[0:-1]
    f = _FunctionGenerator(**self.opts)
    f.__names = list(self.__names) + [name]
    return f

print(func.sum)

#func.sum()就是调用 __call__方法

def __call__(self, *c, **kwargs):
    o = self.opts.copy()
    o.update(kwargs)

    tokens = len(self.__names)

    if tokens == 2:
        package, fname = self.__names
    elif tokens == 1:
        package, fname = "_default", self.__names[0]
    else:
        package = None

    if package is not None:
        func = _registry[package].get(fname)
        if func is not None:
            return func(*c, **o)

    return Function(self.__names[-1],
                    packagenames=self.__names[0:-1], *c, **o) #func.sum(Article.price) 相当于 select sum(price) from article