小福利，flask框架学习笔记（8）--Flask数据库二

最新推荐文章于 2024-07-02 23:32:49 发布

littlespider889

最新推荐文章于 2024-07-02 23:32:49 发布

阅读量183

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Flask 文章标签：数据库 mysql flask

本文链接：https://blog.youkuaiyun.com/littlespider889/article/details/109406199

Flask 专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍了如何在Flask应用中使用SQLAlchemy进行数据库查询，包括基本过滤、外键关联和关联查询的示例。学习者将掌握如何通过ORM进行数据过滤，设置外键并实现表之间的关联操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

大家好，我是天空之城，今天给大家带来小福利，flask框架学习笔记（8）–Flask数据库二
数据库数据的过滤方法

from sqlalchemy import create_engine, and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DECIMAL, Boolean, Enum, DateTime
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy import func
import random


# 连接数据库

# 地址
HOSTNAME = '127.0.0.1'

# 数据库
# 几栋
DATABASE = 'tables'

# 端口
# 门牌号
PORT = 3306

# 用户名和密码
# 钥匙
USERNAME = 'root'
PASSWORD = 'root'

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

engine = create_engine(DB_URL)

# 都要继承这个函数生成的基类
Base = declarative_base(engine)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    price = Column(DECIMAL(20, 5), nullable=False)

    def __str__(self):
        return 'User(title:{}, price:{})'.format(self.title, self.price)


# Base.metadata.drop_all()
# Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

# for i in range(6):
#     user = User(title='title%s' % i, price=random.randint(1, 100))
#     session.add(user)
#
# session.commit()

# eq  相等
# result = session.query(User).filter(User.title == 'title1').all()
# not eq  不相等
# result = session.query(User).filter(User.title != 'title1').all()
# like 模糊查询 title1  2title  %title%
# result = session.query(User).filter(User.title.like('%title%')).all()
# in  在xxx里面 [1, 4]
# result = session.query(User).filter(User.title.in_(['title1', 'title4'])).all()
# not in 不在xxx里面
# result = session.query(User).filter(User.title.notin_(['title1', 'title4'])).all()
# result = session.query(User).filter(~User.title.in_(['title1', 'title4'])).all()
# null None ，没有占位置   为空 相等于空的字符串，占了位置
# result = session.query(User).filter(User.title == None).all()
# result = session.query(User).filter(User.title.is_(None)).all()
# is not null
# result = session.query(User).filter(User.title.isnot(None)).all()
# result = session.query(User).filter(User.title != None).all()
# and
# result = session.query(User).filter(User.title == 'title1', User.price == '87').all()
# result = session.query(User).filter(and_(User.title == 'title1', User.price == '87')).all()
# result = session.query(User).filter(User.title=='title1').filter(User.price==87).all()
# or
result = session.query(User).filter(or_(User.title == 'title9', User.price == '88')).all()
# print(result)
for data in result:
    print(data)

数据库外键的设置

from sqlalchemy import create_engine, and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DECIMAL, Boolean, Enum, DateTime, TEXT, ForeignKey
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy import func
import random


# 连接数据库

# 地址
HOSTNAME = '127.0.0.1'

# 数据库
# 几栋
DATABASE = 'tables'

# 端口
# 门牌号
PORT = 3306

# 用户名和密码
# 钥匙
USERNAME = 'root'
PASSWORD = 'root'

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

engine = create_engine(DB_URL)

# 都要继承这个函数生成的基类
Base = declarative_base(engine)


class User(Base):
    __tablename__ = 'user'

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

    def __str__(self):
        return 'User(name:{})'.format(self.name)


class Article(Base):
    __tablename__ = 'article'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(TEXT, nullable=False)
    uid = Column(Integer, ForeignKey('user.id', ondelete='RESTRICT'))

# Base.metadata.drop_all()
# Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

# user = User(name='cheney')
# session.add(user)
# session.commit()
#
# article = Article(title='Python', content='xxxx', uid=1)
# session.add(article)
# session.commit()

# user = session.query(User).first()
# session.delete(user)
# session.commit()

# 外键的查询
# article = session.query(Article).first()
# id = article.uid
#
# user = session.query(User).get(id)
# print(user)

user = session.query(User).filter(User.id == Article.uid).first()
print(user)

数据库外键的关联查询

from sqlalchemy import create_engine, and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DECIMAL, Boolean, Enum, DateTime, TEXT, ForeignKey
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
from sqlalchemy import func
import random


# 连接数据库

# 地址
HOSTNAME = '127.0.0.1'

# 数据库
# 几栋
DATABASE = 'tables'

# 端口
# 门牌号
PORT = 3306

# 用户名和密码
# 钥匙
USERNAME = 'root'
PASSWORD = 'root'

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

engine = create_engine(DB_URL)

# 都要继承这个函数生成的基类
Base = declarative_base(engine)


class User(Base):
    __tablename__ = 'user'

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

    # 添加反向查询的属性
    # article = relationship('Article', backref='article')
    article = relationship('Article')

    def __str__(self):
        return 'User(name:{})'.format(self.name)


class Article(Base):
    __tablename__ = 'article'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    content = Column(TEXT, nullable=False)
    uid = Column(Integer, ForeignKey('user.id', ondelete='RESTRICT'))

    author = relationship('User')

    def __str__(self):
        return 'Article(title:{})'.format(self.title)


# Base.metadata.drop_all()
# Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()

# data = session.query(Article).first()
# # print(data.uid)
# user = session.query(User).filter(Article.id == data.uid).first()
# print(user)

data = session.query(Article).first()
print(data.uid)

# data = session.query(User).first()
# for item in data.article:
#     print(item)

# user = User(name='cheney')
#
# article = Article(title='天龙八部', content='xxxxx')
# article1 = Article(title='笑傲江湖', content='xxxxx')
#
# article.author = user
# article1.author = user
#
# session.add(article1)
# session.add(article)
# session.commit()

user = session.query(User).filter(User.id ==4).first()
# user = session.query(User).filter(User.id ==4)
# print(user)
for item in user.article:
    print(item)
    
Article(title:无极)
Article(title:射雕)
---------------------------------------------------------------------

user = session.query(User).filter(User.id ==3).first()
print(user.name)
print(user.article)
for item in user.article:
    print(item)

amy
[<__main__.Article object at 0x000000000AEA2198>, <__main__.Article object at 0x000000000AEA2208>]
Article(title:倚天屠龙)
Article(title:倚天屠龙)

--------------------------------------------------------------------------------------------

data = session.query(Article).filter(Article.title =='倚天屠龙')
for data0 in data:
    print(data0.uid)
    print(data0.author)
    print(data0.title)

3
User(name:amy)
倚天屠龙
3
User(name:amy)
倚天屠龙
2
User(name:jack)
倚天屠龙

在这里插入图片描述

from flask import Flask
from sqlalchemy import create_engine


# 连接数据库

# 地址
HOSTNAME = '127.0.0.1'

# 数据库
# 几栋
DATABASE = 'classes'

# 端口
# 门牌号
PORT = 3306

# 用户名和密码
# 钥匙
USERNAME = 'root'
PASSWORD = 'root'

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

engine = create_engine(DB_URL)

with engine.connect() as conn:
    resulut = conn.execute('select * from first_class')
    # print(resulut)
    print(resulut.fetchall())