# -*- coding: UTF-8 -*-
# Author  :  LinYaoHong
# Date    :  2019/8/6 11:13
# connsqlalchemy.py
# Tools   :  PyCharm

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
Base = declarative_base()

engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
Session = sessionmaker(bind=engine)
session = Session()

import  connsqlalchemy as sql_table
'''
查询

'''
print(session.query(sql_table.UserInfo))  # 生成sql语句
userinfo_list = session.query(sql_table.UserInfo)


userinfo_list = session.query(sql_table.UserInfo).all()  # 所有的

print(userinfo_list[0])  # <connsqlalchemy.UserInfo object at 0x000001F5AE6140B8> 内存地址
for row in userinfo_list:
    print(row.id, row.name, row.age)

userinfo_list = session.query(sql_table.UserInfo).filter(sql_table.UserInfo.id < 4)
for row in userinfo_list:
    print(row.id, row.name)

# session.query(sql_table.UserInfo.id)可以只取对应的列
userinfo_list = session.query(sql_table.UserInfo.name).filter(sql_table.UserInfo.id < 3)
for row in userinfo_list:
    print('姓名', row.name)


session.commit()  # 提交
session.close()  # 关闭