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