import datetime

import sqlalchemy
from sqlalchemy import create_engine
from urllib.parse import quote
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy import Column, DateTime, String, insert, Integer, update, delete, select
import time

"""
create database learn_db;
use learn_db;
create table staff
(
    id          int auto_increment
        primary key,
    create_time datetime     null,
    name       varchar(255) not null,
    address       varchar(255) not null
);


"""

print(sqlalchemy.__version__)

"""
https://docs.sqlalchemy.org/en/14/tutorial/engine.html
future=True代表使用2.0风格的连接 在2.0
`with engine.connect() as conn` 代码将不会自动commit， 并且会在代码块中执行ROLLBACK
 `with Session(engine) as session` 在2.0同样不会自动提交 并且会在代码块中执行ROLLBACK(如果没有commit)
"""
engine = create_engine(f'mysql://root:{quote("kexin@kexin2021")}@192.168.1.80/learn_db', echo=True, future=True,
                       pool_size=3)

engine.connect()

Base = declarative_base(engine)


# time.sleep(100)
# see https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#declaring-mapped-classes

class Staff(Base):
    __tablename__ = 'staff2'
    id = Column(Integer, primary_key=True)
    create_time = Column(DateTime)
    name = Column(String(255))
    address = Column(String(255))

    def __str__(self):
        return f'[id: {self.id}, name: {self.name}, address: {self.address}]'


Base.metadata.drop_all()
Base.metadata.create_all()


def a():
    # create
    with Session(engine) as session:
        obj: Staff = Staff()
        obj.address = '北京2'
        obj.name = 'tom'
        obj.create_time = datetime.datetime.now()
        session.add(obj)
        session.commit()
        # session.flush()  https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html#flushing 没有必要flush


a()


def b():
    # read
    # https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#the-select-sql-expression-construct
    with Session(engine) as session:
        from sqlalchemy.engine.row import Row
        from sqlalchemy.engine.result import ChunkedIteratorResult
        stmt = select(Staff).where(Staff.name == 'tom')
        for i in session.execute(stmt):  # i 是Row类型
            print('obj111111111-->', i, type(i), i[0].name)
        res = session.execute(stmt).first()  # first 即使数据不存在也不会异常
        print('obj22222222222222222-->', res, type(res), res[0])  # res[0]是Staff 如果没有数据那么是None异常

        stmt = select(Staff).where(Staff.name == 'tom2')
        res2 = session.execute(stmt)
        print('resssss->', type(res2))
        try:
            print('obj3-->', res2.scalar_one_or_none())  # scalar_one数据不存在会异常， scalar_one_or_none()则不会异常
        except Exception as e:
            print(e, type(e))


b()


def c():
    # update
    with Session(engine) as session:
        # https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html#updating-orm-objects
        session.execute(update(Staff).where(Staff.name == 'tom').values(name='tom_update'))
        session.commit()
        stmt = select(Staff).where(Staff.name == 'tom_update')
        res = session.execute(stmt)
        obj: Staff = res.scalar_one_or_none()
        if obj:
            obj.name = 'tom_update2'
        session.add(obj)
        session.commit()


c()

# delete
with Session(engine) as session:
    # https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html#deleting-orm-objects
    # stmt = select(Staff).where(Staff.name == 'tom_update2')
    # res: ChunkedIteratorResult = session.execute(stmt)
    # obj: Staff = res.scalar_one_or_none()
    # session.delete(obj)
    # session.commit()
    from sqlalchemy.engine.cursor import CursorResult

    stmt2 = delete(Staff).where(Staff.name == 'tom_update2')
    res11: CursorResult = session.execute(stmt2)
    print(res11, type(res11), '影响的行数:',
          res11.rowcount)  # https://docs.sqlalchemy.org/en/14/tutorial/data_update.html#getting-affected-row-count-from-update-delete
    session.commit()

# create
with Session(engine) as session:
    obj: Staff = Staff()
    obj.address = '北京2'
    obj.name = 'tom'
    obj.create_time = datetime.datetime.now()
    session.add(obj)
    session.commit()

# 长事物
session = Session(engine)
# try:
#     session.execute(update(Staff).where(Staff.name == 'tom').values(name='tom_update_long_trans', create_time='sdsd'))
# except Exception as e:
#     print('出现异常没有调用commit或者collback')
# else:
#     session.commit()

with session:
    try:
        session.execute(
            update(Staff).where(Staff.name == 'tom').values(name='tom_update_long_trans', create_time='sdsd'))
    except Exception as e:
        print('出现异常没有调用commit或者collback')
    else:
        session.commit()

time.sleep(100)

# select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>10