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

# import sqlalchemy
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
'''
连接数据库
连接其他种类数据库参考连接
https://docs.sqlalchemy.org/en/13/dialects/index.html
 '''
# engine = create_engine("mysql+pymysql://linyaohong:111111@47.244.4.192/linyaohong", encodeing='utf-8', echo=True)
# engine = create_engine("mysql+pymysql://linyaohong:111111@47.244.4.192/linyaohong?charset=utf8", max_overflow=5)
'''

max_overflowd=5 最大连接数
如果创建的表已经存在则不重新创建(比如修改了类代码)

'''
Base = declarative_base()  # 生成 ORM 基类

class UserType(Base):

    __tablename__ = "usertype"
    id = Column(Integer, primary_key=True,autoincrement=True)
    user_type = Column(String(32))


class UserInfo(Base):

    __tablename__ = "userinfo"
    id = Column(Integer, primary_key=True,autoincrement=True)
    name = Column(String(32)) # ,nullable=True,default='s',index=True unique=True 可以为空/默认值/普通索引/唯一索引
    age = Column(Integer)
    user_type_id = Column(Integer, ForeignKey("usertype.id"))  # 外键

    user_type = relationship("UserType",backref="type_to_user") # 创建这个让查询简单一些(三)
    '''
    relationship 写在 有 ForeignKey的表里
    正向查找
    反向查找
    '''
    # def __repr__(self):
    #     return self.name

# 创建表
def create_table():
    engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)
# 删除表
def drop_table():
    engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
    Base.metadata.drop_all(engine)

if __name__ == '__main__':

    drop_table()
    create_table()
    # 连接数据库
    engine = create_engine("mysql+pymysql://root:111111@127.0.0.1/testdb?charset=utf8", max_overflow=5)
    Session = sessionmaker(bind=engine)
    session = Session()
    '''
    批量插入
    '''
    objs=[
        UserType(user_type="管理员"),
        UserType(user_type="钻石用户"),
        UserType(user_type="白金用户"),
        UserType(user_type="黄金用户"),
        UserType(user_type="普通用户"),
        UserType(user_type="普通游客"),
    ]
    session.add_all(objs)

    '''
    单调插入
    '''
    obj = UserInfo(name="钢铁侠",age="45", user_type_id=1)
    session.add(obj)
    user_obj =[
        UserInfo(name="蜘蛛侠", age="52", user_type_id=2),
        UserInfo(name="美国队长", age="14", user_type_id=6),
        UserInfo(name="奇异博士", age="80", user_type_id=2),
        UserInfo(name="黑寡妇", age="80", user_type_id=3),
        UserInfo(name="鹰眼", age="80", user_type_id=4),
        UserInfo(name="幻世", age="55", user_type_id=2),
        UserInfo(name="蚁人", age="66", user_type_id=5),
        UserInfo(name="蚁人", age="66", user_type_id=5),
        UserInfo(name="蚁人", age="66", user_type_id=5),
        UserInfo(name="钢铁侠他爸", age="66", user_type_id=5),
        UserInfo(name="钢铁侠他女儿", age="66", user_type_id=5),
        UserInfo(name="雷神", age="47", user_type_id=3),
    ]
    session.add_all(user_obj)
    session.commit() # 提交
    session.close() # 关闭