# coding:utf8
# ORM是一种将类映射成表数据的工具
# pip install mysql-client 
# pip install sqlalchemy

# 创建链接
from sqlalchemy import create_engine

# echo=True-----显示数据库中原始的sql语句
engine = create_engine('mysql://root:admin@127.0.0.1:3306/test1', echo=True)

# 声明映射文件
# declarative_base为类工厂函数
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String


class User(Base):
# 定义数据库表的名称
__tablename__ = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(50))
password = Column(String(50))

# 类实例的打印
def __repr__(self):
return "<User(name='%s',name='%s',password='%s')>" % (self.name, self.name, self.password)


# 创建数据表
User.metadata.create_all(engine)

# 创建会话
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()


# 持久化一个实例对象

# add相当于sql中的insert
# ed_user = User(id=0, name='test', password='test123')
# session.add(ed_user)
# session.commit()
# print("add数据:", ed_user)

def insert_con():
try:
insert_user = User(id=0, name='test', password='test123')
print("Select数据:", insert_user)
# add相当于sql中的insert
session.add(insert_user)
session.commit()
print("插入成功！！！")
except:
print("插入失败！！！")


# 删除操作
def delete_con(did):
try:
# select_user = session.query(User).filter_by(id=did).first()
select_user = session.query(User).filter(User.id == did).first()

print("Select数据:", select_user)
session.delete(select_user)
session.commit()
print("删除成功！！！")
except:
print("删除失败！！！")


# session.query(参数)-----是表的对象
# filter_by----相当于sql中的where
# first()------相当于sql中的limit1
'''
select_user=session.query(User).filter_by(password='123').first()等效于
select * from users where password='123' limit 1; 
'''


def update_con(uid):
# update_user=session.execute('update users set name="hello world" where id=2 ')
# session.query(User).filter_by(id=uid).first().update({User.name: "Dr. No"})
session.query(User).filter(User.id == 5).update(
{User.name: "Dr. No"})

session.commit()
print("update成功！！！")


if __name__ == '__main__':
delete_con(5)
# update_con(5)