# _*_ coding: utf-8 _*_

"""
python_sqlalchemy.py by xianhu
"""

import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative

# engine, echoTruesql, 
# engine = sqlalchemy.create_engine("mysql+pymysql://username:password@hostname/dbname", encoding="utf8", echo=True)
engine = sqlalchemy.create_engine("mysql+pymysql://dba_0:mimadba_0@101.200.174.172/data_secret", encoding="utf8", echo=False)

"""
# engineconnection,withclose,
with engine.connect() as conn:
    # 
    result = conn.execute("select * from tablename limit 10;")
    for item in result:
        print(item)

    # execute,pymysql,commit
    conn.execute("insert into tablename(id, url, title) values(1, 'url1', 'title1');")
    conn.execute("insert into tablename(id, url, title) values(%s, %s, %s);", 2, "url2", "title2")
    conn.execute("insert into tablename(id, url, title) values(%s, %s, %s)", (3, "url3", "title3"))
    conn.execute("insert into tablename(id, url, title) values(%s, %s, %s)", [(31, "url31", "title31"), (32, "url32", "title32")])

    # 
    trans = conn.begin()
    try:
        conn.execute("insert into tablename(id, url, title) values(%s, %s, %s)", [(4, "url4", "title4"), (5, "url5", "title5")])
        trans.commit()
    except Exception as excep:
        trans.rollback()
        raise
    trans.close()
"""

# BaseModel,
BaseModel = sqlalchemy.ext.declarative.declarative_base()


# User
class User(BaseModel):
    __tablename__ = "Users"         # 
    __table_args__ = {
        "mysql_engine": "InnoDB",   # 
        "mysql_charset": "utf8",    # 
    }

    # ,
    id = sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column("name", sqlalchemy.String(50), nullable=False)
    age = sqlalchemy.Column("age", sqlalchemy.Integer, default=0)

    # id,Rolesid
    role_id = sqlalchemy.Column("role_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("Roles.id"))

    # ,role_id
    role = sqlalchemy.orm.relationship("Role", foreign_keys="User.role_id")

    # ,role_id,,Roleusers
    # role = sqlalchemy.orm.relationship("Role", foreign_keys="User.role_id", backref=sqlalchemy.orm.backref("users"))


# Role
class Role(BaseModel):
    __tablename__ = "Roles"         # 
    __table_args__ = {
        "mysql_engine": "InnoDB",   # 
        "mysql_charset": "utf8",    # 
    }

    # ,
    id = sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column("name", sqlalchemy.String(50), unique=True)

    # ,Userrole_id
    users = sqlalchemy.orm.relationship("User", foreign_keys="User.role_id")


# Session
DBSessinon = sqlalchemy.orm.sessionmaker(bind=engine)   # 
session = DBSessinon()                                  # 


# 
BaseModel.metadata.drop_all(engine)
# ,,
BaseModel.metadata.create_all(engine)

try:
    # ,commit
    session.query(User).filter(User.id != -1).delete()
    session.query(Role).filter(Role.id != -1).delete()
    # :session.delete()

    # ,,
    session.add(Role(id=1, name="student"))
    session.add(Role(id=2, name="teacher"))
    session.commit()

    session.add(User(name="James", age=20, role_id=1))
    session.add(User(name="Wade", age=40, role_id=2))
    session.commit()

    user = User(name="Kobe", age=24, role_id=1)
    session.add(user)
    session.commit()

    # 
    user.name = "Allen"
    session.merge(user)                         # merge,,
    session.query(User).filter(User.id == user.id).update({User.name: "Allen"})         # update
    session.query(User).filter(User.id == user.id).update({User.age: User.age + 1})     # update,

    # 
    roles = session.query(Role)                 # 
    for role in roles:
        print("Role:", role.id, role.name)

    users = session.query(User)                 # 
    for user in users:
        print("User:", user.id, user.name, user.age, user.role_id)

    # 
    print("get(id):", session.query(User).get(1))                       # id1
    print("get[1:3]:", session.query(User)[1:3])                        # 2-3

    # ,Users
    users = session.query(User).filter(User.id > 6)                     # 
    users = session.query(User).filter(User.id > 6).all()               # ,
    user = session.query(User).filter(User.id > 6).first()              # ,
    users = session.query(User).filter(User.id > 6).limit(10)           # ,10
    users = session.query(User).filter(User.id > 6).offset(2)           # ,3

    users = session.query(User).filter(User.id > 6, User.name == "Kobe")                    # ,and
    users = session.query(User).filter(User.id > 6).filter(User.name == "Kobe")             # ,and
    users = session.query(User).filter(sqlalchemy.or_(User.id > 6, User.name == "Kobe"))    # ,or
    users = session.query(User).filter(User.id.in_((1, 2)))                                 # ,in
    users = session.query(User).filter(sqlalchemy.not_(User.name))                          # ,not

    user_count = session.query(User.id).count()                                             # user
    user_count = session.query(sqlalchemy.func.count(User.id)).scalar()                     # scalar
    session.query(sqlalchemy.func.count("*")).select_from(User).scalar()                    # scalar
    session.query(sqlalchemy.func.count(1)).select_from(User).scalar()                      # scalar
    session.query(sqlalchemy.func.count(User.id)).filter(User.id > 0).scalar()              # filter()  User

    session.query(sqlalchemy.func.sum(User.age)).scalar()                                   # ,scalar
    session.query(sqlalchemy.func.avg(User.age)).scalar()                                   # ,scalar
    session.query(sqlalchemy.func.md5(User.name)).filter(User.id == 1).scalar()             # md5

    users = session.query(sqlalchemy.distinct(User.name))               # ,name
    users = session.query(User).order_by(User.name)                     # ,
    users = session.query(User).order_by(User.name.desc())              # ,
    users = session.query(User).order_by(sqlalchemy.desc(User.name))    # ,

    users = session.query(User.id, User.name)                           # 
    users = session.query(User.name.label("user_name"))                 # 
    for user in users:
        print("label test:", user.user_name)                            # 

    users = session.query(sqlalchemy.func.count(User.name).label("count"), User.age).group_by(User.age)    # 
    for user in users:
        print("age:{0}, count:{1}".format(user.age, user.count))

    # 
    result = session.query(User, Role).filter(User.role_id == Role.id)
    for user, role in result:
        print("user %s's role is %s" % (user.name, role.name))
    users = session.query(User).join(Role, User.role_id == Role.id)
    for user in users:
        print("user join, name:", user.name)

    # 
    roles = session.query(Role)
    for role in roles:
        print("role:%s users:" % role.name)
        for user in role.users:
            print("\t%s" % user.name)
    users = session.query(User)
    for user in users:
        print("user %s's role is %s" % (user.name, user.role.name))

except Exception as excep:
    session.rollback()
    raise

session.close()