SQLAlchemy基本操作(增、删、改、查)

准备工作

为了简化数据库操作,我们将使用 SQLAlchemy——一个 Python 数据库工具(ORM,即对象关系映射)。借助 SQLAlchemy,你可以通过定义 Python 类来表示数据库里的一张表(类属性表示表中的字段 / 列),通过对这个类进行各种操作来代替写 SQL 语句。这个类我们称之为模型类,类中的属性我们将称之为字段

导入扩展类

from flask_sqlalchemy import SQLAlchemy

设置数据库URL

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/test?charset=utf8'

创建数据库模型

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))

创建数据库表

创建

下面的操作演示了如何向数据库中添加记录:

代码清单:user.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
# 配置 sqlalchemy 数据库驱动
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/test?charset=utf8'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False    # 关闭对模型修改的监控
# 初始化
db = SQLAlchemy(app)

#创建数据库模型
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))

    def __init__(self, id, name):
        self.id = id
        self.name = name

    #添加用户
    def save(self):
        db.session.add(self)
        db.session.commit()


 user = User(id='3', name='Tom')
 user.save()

操作结果:

查询

下面的操作演示了如何从数据库中读取记录,并进行简单的查询:

代码清单:user.py

users=User.query.all()
for user in users:
    print('User [id:{0},name{1}]'.format(user.id,user.name))

操作结果:

更新

user=User.query.get(2)
user.name='lishi'
db.session.commit()

操作结果:

删除

下面的操作删除了 User 模型中主键为 2 的记录:

user=User.query.get(2)
db.session.delete(user)
db.session.commit()

操作结果:

The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a sys- tem that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other. The ORM is in contrast to the SQLAlchemy Expression Language, upon which the ORM is constructed. Whereas the SQL Expression Language, introduced in SQL Expression Language Tutorial, presents a system of representing the primitive constructs of the relational database directly without opinion, the ORM presents a high level and abstracted pattern of usage, which itself is an example of applied usage of the Expression Language. While there is overlap among the usage patterns of the ORM and the Expression Language, the similarities are more superficial than they may at first appear. One approaches the structure and content of data from the perspective of a user-defined domain model which is transparently persisted and refreshed from its underlying storage model. The other approaches it from the perspective of literal schema and SQL expression representations which are explicitly composed into messages consumed individually by the database. A successful application may be constructed using the Object Relational Mapper exclusively. In advanced situations, an application constructed with the ORM may make occasional usage of the Expression Language directly in certain areas where specific database interactions are required. The following tutorial is in doctest format, meaning each >>> line represents something you can type at a Python command prompt, and the following text represents the expected return value.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

云淡风轻58

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值