from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

from sqlalchemy.orm import sessionmaker, relationship

from sqlalchemy import create_engine

Base = declarative_base()

# 创建单表

class UserType(Base):

    __tablename__ = 'usertype'

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

    name = Column(String(32),nullable=True,default='sf',index=True)

class Users(Base):

    __tablename__ = 'users'

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

    name = Column(String(32),nullable=True,default='sf',index=True)

    extra = Column(String(16),unique=True)

    user_type_id = Column(Integer,ForeignKey('usertype.id'))

def create_db():

    engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

    Base.metadata.create_all(engine)

def delete_db():

    engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

    Base.metadata.drop_all(engine)

#增加

obj1 = UserType(name='普通用户')

session.add(obj1)

session.commit()

# obj1 = UserType(name='普通用户')

objs=[

     UserType(name='超级用户'),

     UserType(name='白金用户'),

     UserType(name='黑金用户')

]

# session.add(obj1)

session.add_all(objs)

session.commit()

session.close()

#修改

# session.query(UserType.id,UserType.name).filter(UserType.id>0).update({'name':'黑金'})

session.query(UserType.id,UserType.name).filter(UserType.id>2).update({UserType.name: UserType.name + "xxx"}, synchronize_session=False)

session.commit()

session.close()

# 删

# session.query(UserType.id,UserType.name).filter(UserType.id>3).delete()

# 查

# user_type_list = session.query(UserType).all()

# for row in user_type_list:

#     print(row.id,row.name)

# user_type_list = session.query(UserType.id,UserType.name).filter(UserType.id>2)

# for row in user_type_list:

#     print(row.id,row.name)