Flask-ORM添加数据
连接数据库创建一个article表,并且实例化一条数据并访问属性
代码如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# 创建一个article表
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='ycx')
print(article.id)
print(article.name)
控制台输出:
None
ycx
数据库显示:
显然,name可以正常打印,但是id为None,这是因为id是一个自增的主键,还未插入到数据库表中,id不存在,所以为空,打印出name的值是定义类中的name属性值,并不是从数据库获取的值,由数据库可以看出:
将数据提交到数据库,需要使用Session对象
代码如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# 创建一个article表
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='ycx')
# 保存数据到数据库中
Session = sessionmaker(bind=engine)
session = Session() # 含有__call__魔术方法,可以将Session类变成方法调用
# 添加数据
session.add(article)
# 提交数据
session.commit()
print(article.id)
print(article.name)
控制台输出:
1
ycx
数据库显示:
此时控制台打印出了对应的id,再查看数据库article表,发现数据成功插入
注意:
数据添加到session中之后,还需要使用commit()方法才能将数据真正的保存到数据库,因为在SQLAlchemy的ORM实现中,在做commit操作之前,所有的操作都是在事务中进行的,因此如果要将事务中的操作效果真正映射到数据库中,还需要做commit()方法操作
说明:
sessionmaker是一个类,实例化生成Session对象,因为sessionmaker类中有魔法方法__call__(),所以可以直接将对象调用,Session成了一个可调用对象,即把实例对象用类似函数的形式调用,模糊了函数和对象之间的概念
sessionmaker源码中魔法方法__call__()的内容:
添加多条数据:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
# 创建引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
# 创建一个article表
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='ycx')
article1 = Article(name='tom')
article2 = Article(name='jack')
# 保存数据到数据库中
Session = sessionmaker(bind=engine)
session = Session() # 含有__call__魔术方法,可以将Session类变成方法调用
# 添加数据
session.add_all([article1, article2])
# 提交数据
session.commit()
数据库显示:
Flask-ORM映射数据库的增删改查
添加数据:
创建一个新表:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
Base.metadata.create_all()
运行后即创建表blog_data
数据库显示:
插入数据:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
Session = sessionmaker(bind=engine)
session = Session()
def add_data():
blog = Blog(title='Python', content='人生苦短,我用Python', author='ycx')
session.add(blog)
session.commit()
if __name__ == '__main__':
add_data()
数据库显示:
数据库显示如此即插入数据成功
插入数据注意
实例化Blog对象时需要显式传入参数,否则会报错,即如果Blog(‘Python’, ‘人生苦短,我用Python’, ‘ycx’)
是会出现报错的
查找数据:
查找操作是通过session.query()方法
实现的,这个方法会返回一个Query对象,Query对象相当于一个数组,数组里面是查找出来的数据,可以进行迭代
•查询所有数据时,需要使用all()方法,示例如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
Session = sessionmaker(bind=engine)
session = Session()
def add_data():
blog = Blog(title='PHP', content='PHP是最好的语言', author='jack')
session.add(blog)
session.commit()
def search_data():
data = session.query(Blog.content, Blog.author).order_by(Blog.author)
for item in data:
print(item)
if __name__ == '__main__':
add_data() #添加数据
search_data() #查询数据
先添加第二条数据,在查询所有数据
数据库显示:
控制台输出:
('PHP是最好的语言', 'jack')
('人生苦短,我用Python', 'ycx')
返回的数据类型是元组
如果传递了两个或两个以上的对象,或者传递的是ORM类的属性,查找得到的结果就是元组
如果要对查询结果进行过滤,可以使用filter()
和filter_by()
两个方法
这两个方法都是用来过滤数据的,区别在于,filter_by()传入的参数是关键字,filter()传入的参数是条件判断,能够传入的条件更多、更灵活
在定义模型时定义str()方法
,从而可以自定义打印对象
使用filter()方法测试如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
def __str__(self):
return 'Blog(id:{}, title:{}, content:{}, author:{})'.format(self.id, self.title, self.content, self.author)
Session = sessionmaker(bind=engine)
session = Session()
def add_data():
blog = Blog(title='PHP', content='PHP是最好的语言', author='jack')
session.add(blog)
session.commit()
def search_data():
data = session.query(Blog).filter(Blog.title == 'PHP').all()
for item in data:
print(item)
if __name__ == '__main__':
search_data()
控制台输出显示:
Blog(id:2, title:PHP, content:PHP是最好的语言, author:jack)
用filter_by()方法
使用示例如下:
def search_data():
data = session.query(Blog).filter_by(title = 'PHP').all()
for item in data:
print(item)
控制台输出与上述相同
当你仅仅需要第一条数据时,可以使用first()方法,代码如下:
def search_data():
data = session.query(Blog).first()
print(data)
控制台显示:
Blog(id:1, title:Python, content:人生苦短,我用Python, author:ycx)
可使用get()方法来指定id来查询所需数据,传入的参数即为所需查询的数据对应的id,代码测试如下
def search_data():
data = session.query(Blog).get(2)
print(data)
控制台显示:
Blog(id:2, title:PHP, content:PHP是最好的语言, author:jack)
当get()方法中传入的参数不存在时,即返回None,表示该查询数据不存在
修改数据
修改数据,即为直接调用对象属性并重新赋值,代码测试如下:
修改数据注意:
修改数据的关键是:先查询数据,再给所查询数据重新赋值即可完成修改数据
代码测试如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
def __str__(self):
return 'Blog(id:{}, title:{}, content:{}, author:{})'.format(self.id, self.title, self.content, self.author)
Session = sessionmaker(bind=engine)
session = Session()
def update_data():
# 先查询数据
blog = session.query(Blog).first()
# 再修改数据
blog.author = 'Tomer'
session.commit()
print(blog)
if __name__ == '__main__':
update_data()
数据库显示:
控制台显示:
Blog(id:1, title:Python, content:人生苦短,我用Python, author:Tomer)
删除数据
删除数据使用delete()方法
,测试代码如下:
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 数据库配置
USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'blog_flask'
DB_URL = "mysql+pymysql://{}:{}@{}:{}/{}".format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Blog(Base):
__tablename__ = 'blog_data'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50), nullable=False)
content = Column(String(500))
author = Column(String(30))
def __str__(self):
return 'Blog(id:{}, title:{}, content:{}, author:{})'.format(self.id, self.title, self.content, self.author)
Session = sessionmaker(bind=engine)
session = Session()
def search_data():
data = session.query(Blog).all()
for item in data:
print(item)
def delete_data():
blog = session.query(Blog).first()
session.delete(blog)
session.commit()
search_data()
if __name__ == '__main__':
delete_data()
控制台输出:
Blog(id:2, title:PHP, content:PHP是最好的语言, author:jack)
数据库显示:
查看数据库表,发现数据已经删除,并且是是永久地删除了这些数据
但是在实际开发中一般不能直接删除数据,而是增加一个字段,比如is_delete字段
来标记是否删除,值1表示已经删除,值0表示未删除,在查询的时候只查询is_delete字段值为0的数据即可