SQLAlchemy(4)

rs = session.query(User)
print(rs)
for i in rs:
     print(i)
#----------------------------------

#根据返回结果来看， rs  是一个 Query 对象，打印出来可以看到转化的 SQL
rs = session.query(User).filter(User.username==‘塔卡‘)
#all 是返回所有符合条件的数据
rs = session.query(User).all() #查询所有数据,返回一个列表
print(rs)
print(rs[0].username) #属性访问
#first 是返回所有符合条件数据的第一条数据
rs = session.query(User).first()  #查询第一条数据
#[0] 和 first 类似，但是如果没有符合条件的数据则会报错
session.query(User).filter(User.username==‘budong‘)[0]
#这里，在 query 中查询对象的某个属性值 ( 对应为查询表中某个字段的值 )，返回的结果不再是一个 Query 对象，而是一个列表
rs = session.query(User.username).filter(User.username == ‘塔卡‘).all()
#同理，但是 first 返回结果是一个元组
rs = session.query(User.username).filter(User.username == ‘塔卡‘).first()
#[0] 和 first 类似，但是如果没有符合条件的数据则会报错
session.query(User.username).filter(User.username==‘budong‘)[0]


#getattr(rs[0], ‘username‘),  rs[0].username这两种方式可以取到具体的数据值
rs = session.query(User).filter(User.username==‘budong‘).all()
print(hasattr(rs,‘username‘))
print(getattr(rs,‘username‘,‘litao‘))


rs = session.query(User)[0:2]  #索引取值
print(rs)
# rs2 = session.query(User).filter(User.username==‘塔卡‘).all()
# print(rs,type(rs))

rs  =session.query(User.username).all()
print(rs)
rs  =session.query(User.username).limit(2).all()  #限制数量查询
rs  =session.query(User.username).offset(2).all()  #偏移量
rs  =session.query(User.username).slice(1,4).all()  #切片

#不管怎样写one只能查一条数据，如果有多条重复数据，会报错
#sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()
rs = session.query(User.username).filter(User.username == ‘李涛‘).one()
print(rs)

from sqlalchemy import desc
rs = session.query(User.username,User.id).order_by(User.id).all() #升序排列
rs = session.query(User.username,User.id).order_by(desc(User.id)).all()

#综合使用
rs = session.query(User.username,User.id).order_by(desc(User.id)).filter(User.username == ‘李涛‘).all()
print(rs)

# 分组查询与聚合函数一起使用
from sqlalchemy import func,extract,or_
# rs = session.query(User.password,func.count(User.id)).group_by(User.password).all()
# rs = session.query(User.password,func.count(User.id)).group_by(User.password).having(func.count(User.id) >1).all()
# print(rs)

# rs = session.query(User.password,func.sum(User.id)).group_by(User.password).all()
# rs = session.query(User.password,func.max(User.id)).group_by(User.password).all()
# rs = session.query(User.password,func.min(User.id)).group_by(User.password).all()
# print(rs)

#extract
# rs = session.query(extract(‘minute‘,User.create_time).label(‘minute‘),func.count(User.id)).group_by(‘minute‘).all()


#or_
rs = session.query(User.username).filter(or_(User.username.isnot(None),User.password == ‘1234‘)).all()
print(rs)

class UserDetails(Base):
    __tablename__=‘user_details‘
    id = Column(Integer, primary_key=True, autoincrement=True)
    id_card = Column(Integer,nullable=True,unique=True)
    lost_login = Column(DateTime)
    login_num = Column(Integer,default=0)
    user_id = Column(Integer,ForeignKey(‘user.id‘))

    def __repr__(self):
        return ‘<UserDetails(id=%s,id_card=%s,last_login=%s,login_num=%s,user_id=%s)>‘%(
            self.id,
            self.id_card,
            self.lost_login,
            self.login_num,
            self.user_id
        )

sql_1 = ‘‘‘
    select * from   `user`
‘‘‘
#查询
row = session.execute(sql_1)
#循环取值# for i in row:
#     print(i)
#取值
print(row)
#fetch会记录游标运行了上面的for以后下面就查不到数据
print(row.fetchone())  #查询一条
print(row.fetchmany())  #在多查询一条
print(row.fetchall()) # 差剩下的所有

from sqlalchemy.orm import relationship
....
class User(Base):
    .....
class UserDetails(Base):
    ......

    userdetail = relationship(‘User‘,backref=‘details‘,uselist=False,cascade=‘all‘)
    #SQLAlchemy里面用来表示表关系的，一对一
    #relationship只在模型层面上生效，只在模型层面上
    def __repr__(self):           
            ........

from sqlalchemy import Table

user_article = Table(‘user_article‘, Base.metadata,
                     Column(‘user_id‘, Integer, ForeignKey(‘user.id‘), primary_key=True), #联合主键
                     Column(‘article_id‘, Integer, ForeignKey(‘article.id‘), primary_key=True)
                     )

class Article(Base):
    __tablename__ = ‘article‘
    id = Column(Integer,primary_key=True,autoincrement=True)
    content = Column(String(500),nullable=True)
    create_time  = Column(DateTime,default=datetime.now)

    article_user = relationship(‘User‘,backref = ‘article‘,secondary=user_article)

    def __repr__(self):
        return ‘Article(id=%s, content=%s, creat_time=%s)‘ % (
            self.id,
            self.content,
            self.create_time
          )

[‘__class__‘, ‘__delattr__‘, ‘__dict__‘, ‘__dir__‘, ‘__doc__‘, ‘__eq__‘, ‘__format__‘, ‘__ge__‘, ‘__getattribute__‘, ‘__gt__‘, ‘__hash__‘, ‘__init__‘, ‘__le__‘, ‘__lt__‘, ‘__mapper__‘, ‘__module__‘, ‘__ne__‘, ‘__new__‘, ‘__reduce__‘, ‘__reduce_ex__‘, ‘__repr__‘, ‘__setattr__‘, ‘__sizeof__‘, ‘__str__‘,‘__subclasshook__‘, ‘__table__‘, ‘__tablename__‘, ‘__weakref__‘, ‘_decl_class_registry‘, ‘_locked‘, ‘_sa_class_manager‘, ‘_sa_instance_state‘, ‘article‘, ‘create_time‘, ‘details‘, ‘id‘, ‘metadata‘, ‘password‘, ‘username‘]

        <User(id=4,username=小泼,password=55432,create_time=2019-04-01 23:10:56,_locked=False)>
        
[Article(id=1, content=litao进阶了, creat_time=2019-04-05 18:51:34), Article(id=2, content=式微走了, creat_time=2019-04-01 18:52:06)]
[<UserDetails(id=2,id_card=543,last_login=2019-04-02 23:35:38,login_num=4,user_id=4)>]

Traceback (most recent call last):
  File "/home/pyvip/tornado_pro/06-login.py", line 11, in <module>
    from data.user_modules import UserDetails
  File "/home/pyvip/tornado_pro/data/user_modules.py", line 2, in <module>
    from connect import Base
ImportError: No module named ‘connect‘

import sys
import tornado.web
import tornado.ioloop
import tornado.httpserver
import tornado.options
from tornado.web import RequestHandler
from tornado.options import define,options
import util.ui_modules
import util.ui_methods
# import time
from data.connect import session
from data.user_modules import UserDetails, User

define(‘port‘,default=8080,help=‘run server‘,type=int)

#
class MainHandler(RequestHandler):
    def get(self):
        self.write(‘请去login‘)


class LoginHandler(RequestHandler):
    def get(self):
        self.render(‘in_out.html‘)

    def post(self):
        ‘‘‘验证逻辑‘‘‘
        user = self.get_argument(‘name‘,None)
        password = self.get_argument(‘password‘,None)
        username = session.query(User).filter(User.username == user).first()print(username)
        if username and  password == username.password:
            self.render(‘02-templates.html‘,
                        username = username.username
                        )
        else:
            self.write(‘登录失败‘)



application = tornado.web.Application(
    handlers=[
        (r‘/‘,MainHandler),
        (r‘/login‘,LoginHandler),
    ],
    debug=True,
    template_path = ‘templates‘,
    static_path=‘static‘,
    # autoescape = None, #全局取消转义
    ui_methods=util.ui_methods,
    ui_modules=util.ui_modules
)
if __name__ == ‘__main__‘:
    tornado.options.parse_command_line()
    http_server = tornado.httpserver.HTTPServer(application)
    http_server.listen(options.port)
    tornado.ioloop.IOLoop.current().start()

<!DOCTYPE html>
{#去掉整个页面的转义#}
{#{% autoescape None %}#}
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Templates</title>
</head>
<body>
{% if username !=‘‘ %}
    欢迎 {{ username }} 登录
<img src="static/images/01.jpg" width="200px" alt="">
<img src="{{ static_url(‘images/02.webp‘) }}" width="200px" alt="">
{% else %}
    亲，请登录
{% end %}
</body>
</html>

class LoginHandler(RequestHandler):
    def get(self):
        self.render(‘in_out.html‘)
    def post(self):
        ‘‘‘验证逻辑‘‘‘
        user = self.get_argument(‘name‘,None)
        password = self.get_argument(‘password‘,None)
        # username = session.query(User).filter(User.username == user).first()
        username = User.get_name(user)
        print(username)
        if username and  password == username.password:
            self.render(‘02-templates.html‘,
                        username = username.username
                        )
        else:
            self.write(‘登录失败‘)

class User(Base):
　　。。。。
    def __repr__(self):
        return ‘‘‘
        <User(id=%s,username=%s,password=%s,create_time=%s,_locked=%s)>
        ‘‘‘%(self.id,self.username,self.password,self.create_time,self._locked)
    @classmethod
    def get_name(cls,user):
        ‘‘‘用来查询用户名的方法‘‘‘
        return session.query(cls).filter(cls.username == user).first()