python orm子產品sqlalchemy的簡單使用（增删改查）

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import TEXT, INTEGER, VARCHAR, DATETIME, TINYINT

# 建立對象的基類
Base = declarative_base()


# 定義User對象
class User(Base):
    # 表的名字
    __tablename__ = 'user'

    # 表的結構
    id = Column(INTEGER, primary_key=True, autoincrement=True, comment='id關聯表自增id')
    name = Column(String(20))


# 初始化資料庫連結
engine = create_engine('mysql+pymysql://root:[email protected]:3306/tapp')

# 建立DBSession類型
DBSession = sessionmaker(bind=engine)

# # 添加
# # 建立Session對象
# session = DBSession()
# # 建立User對象
# new_user = User(name='Bob')
# # 添加到session
# session.add(new_user)
# # 送出
# session.commit()
# # 關閉session
# session.close()

# 查詢
# 建立session
session = DBSession()
# 利用session建立查詢，query(對象類).filter(條件).one()/all()
# filters = {'name': 'Bob', 'id': 6}
# user_list = session.query(User).filter_by(**filters).filter(User.id > 1).all()
# filters = {'name': , 'id': 6}
user_list = session.query(User).filter(User.name.like('Bob'), User.id.like(2)).all()
for user in user_list:
    print('type:{0}'.format(user.id))
    print('name:{0}'.format(user.name))
# 關閉session
session.close()

# # 更新
# session = DBSession()
# user_result = session.query(User).filter_by(id='5').first()
# user_result.name = "jack"
# session.commit()
# session.close()

# 删除
# session = DBSession()
# user_del = session.query(User).filter_by(id='5').first()
# session.delete(user_del)
# session.commit()
# session.close()