flask-ORM增删改查

ORM增加数据

基本框架：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

HOSTNAME = '127.0.0.1'
DATABASE = 'demo0417'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'

DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

engine = create_engine(DB_URL)

创建表，增添数据代码：

Base = declarative_base(engine)
class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

# Base.metadata.create_all()

article = Article(name='happy')
article1 = Article(name='hua')
print(article.name)
print(article.id)  # None

# 保存到数据库中
# 类的实例化  __call__ 将类变成方法去调用
Session = sessionmaker(bind=engine)
session = Session()

# 添加
session.add(article1)
# 添加多个数据
session.add_all([article, article1])

# 提交
session.commit()
print(article.name)
print(article.id)

查询：

Base = declarative_base(engine)

class Article(Base):
    __tablename__ = 'art'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50), nullable=False)
    content = Column(String(50))
    author = Column(String(50))

    def __str__(self):
        return "Article(title:{},content:{},author:{})".format(self.title, self.content, self.author)

# Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
def search_data():
    # all  查询所有
    # data = session.query(Article).all()
    # 如果有多条 数据 遍历出来他们
    # for item in data:
        # print(item)  # 显示的是<__main__.Article object at 0x000001FA8B973288>
        # 查看里面数据的内容 调用
        # print(item.title)
        # print(item.content)
    # print(data)
    #     print(item)  # 直接打印item，让他显示数据，在类里面定义一个str方法，其他的就可以注释掉
    # 有条件的查询数据  filter(Article.title=='happy')
    #     data = session.query(Article).filter(Article.title=='happy').all()
    #     for item in data:
    #         print(item)
    # 第二种查询方法  filter_by(title='happy')
    #     data = session.query(Article).filter_by(title='happy').all()
    #     for item in data:
    #         print

        # 查询第一条
        # data = session.query(Article).first()
        # print(data)

        # get 方法 查询第几条数据， 传的ID没有 则返回None
        data = session.query(Article).get(3)
        print(data)

if __name__ == '__main__':
    # add_data()
    search_data()

修改

def update_data():
    # 查询出要修改的这条记录
    article = session.query(Article).first()
    article.title = 'gudk'
    print(article.title)
    session.commit()  # 必须要提交 ，否则数据库不会更改

if __name__ == '__main__':
    # add_data()
    # search_data()
    update_data()

删除

def delete_data():
    article = session.query(Article).first()
    # 真实项目中不使用这种方法， 只是认识一下
    # is_delete  1 未删除   0 删除
    # 修改操作  is_delete  1=>0
    session.delete(article)
    session.commit()

if __name__ == '__main__':
    # add_data()
    # search_data()
    # update_data()
    delete_data()

回滚操作

回滚只能在提交之前！

def update_data():
# 回滚
article = session.query(Article).first()
article.title = 'happy'
print(article.title)

# session.rollback()
# print(article.title)
session.commit()


if __name__ == '__main__':
update_data()