MySQL 命令大全
DDL（數(shù)據(jù)定義語句）
CREATE TABLE/DATABASE
ALTER TABLE/DATABASE
DROP TABLE/DATABASE
DML（數(shù)據(jù)管理語句）
INSERT => 新增
DELETE => 刪除
UPDATE => 更新
SELECT => 查詢
創(chuàng)建數(shù)據(jù)庫
—?jiǎng)?chuàng)建并使用數(shù)據(jù)庫
CREATE DATABASE mydatabase; 創(chuàng)建數(shù)據(jù)庫
USE mydatabase; 使用數(shù)據(jù)庫
—查看已有的數(shù)據(jù)庫
SHOW DATABASES;
建表
建表語句
<pre class="md-fences md-end-block" lang="sql" contenteditable="false" cid="n554" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">CREATE TABLE students(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL
);</pre>
常見類型
int,char,varchar,datetime
例子：
<pre class="md-fences md-end-block" lang="sql" contenteditable="false" cid="n580" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">-- 新建數(shù)據(jù)庫
CREATE DATABASE school;
?
-- 使用數(shù)據(jù)庫
USE school;
?
-- id : 學(xué)生的ID
-- name: 學(xué)生名稱
-- nickname: 學(xué)生的昵稱
-- sex: 性別
-- in_time: 入學(xué)的時(shí)間
CREATE TABLE students(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
nickname VARCHAR(20) NULL,
sex CHAR(1) NULL,
in_time DATETIME(20) NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
?
-- 插入數(shù)據(jù)
INSERT INTO students(1,'張三','san',now());
?
-- 刪除表
DROP TABLE IF EXISTS students;
?
-- 查詢
SELECT * FROM students;
?
-- 查詢語句
SELECT
select_expr, ...
FROM table_references -- table_references 表來源
[WHERE where_definition] -- where_definition 條件
[GROUP BY {col_name | expr | position}]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC],...]
[LIMIT {[offset,] row_count}] -- LIMIT限制查詢條數(shù) offset(偏移量,從哪開始), row_count(查詢的條數(shù))
?
-- 條件查詢
SELECT * FROM student WHERE sex='男';
SELECT id,name,nickname FROM student WHERE sex='男' ORDER BY id DESC;
?
-- 修改(更新)
UPDATE table_references
SET col_name1=expr1[, col_name2=expr2 ...]
[WHERE where_definition]
?
-- 更新操作
UPDATE student SET sex = '女' WHERE sex = '男';
UPDATE student SET sex = '女' WHERE id > 5;
?
-- 其他操作
CREATE INDEX -- 新建索引
ALTER TABLE -- 修改表
DROP -- 刪除數(shù)據(jù)庫、表、索引、視圖等</pre>
<figure class="md-table-fig" contenteditable="false" cid="n307" mdtype="table" style="box-sizing: border-box; margin: -8px 0px 0px -8px; overflow-x: auto; max-width: calc(100% + 16px); padding: 8px;">
| 命令 | 說明 |
|---|---|
| net start mysql | 啟動(dòng)數(shù)據(jù)庫 |
| net stop mysql | 關(guān)閉數(shù)據(jù)庫 |
| mysql -u root -p | 根據(jù)用戶名密碼，登錄數(shù)據(jù)庫 |
| mysql -u root | 如果數(shù)據(jù)庫沒有密碼則使用 |
| show databases; | 查看在當(dāng)前服務(wù)器中有多少個(gè)數(shù)據(jù)庫 |
| drop database databaseName; | 刪除某個(gè)數(shù)據(jù)庫 |
| commit; | 提交 |
| CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; | 創(chuàng)建數(shù)據(jù)庫 |
| ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; | 更改數(shù)據(jù)庫的字符編碼 |
| use databaseName; | 選擇使用某個(gè)數(shù)據(jù)庫 |
| show tables; | 查看數(shù)據(jù)庫中有多少的表 |
| drop table tableName; | 刪除表 |
| describe tableName; | 顯示表結(jié)構(gòu) |
SET PASSWORD FOR 'root'``@``'localhost' = ``PASSWORD``(``'newpass'``);
|
方法1： 用SET PASSWORD命令 |
| 方法2：用mysqladmin | |
</figure>
方法1： 用SET PASSWORD命令
MySQL -u root
mysql> ``SET PASSWORD FOR 'root'``@``'localhost' = ``PASSWORD``(``'newpass'``);
方法2：用mysqladmin
mysqladmin -u root ``password "newpass"
如果root已經(jīng)設(shè)置過密碼，采用如下方法
mysqladmin -u root ``password oldpass ``"newpass"
方法3： 用UPDATE直接編輯user表
mysql -u root
mysql> use mysql;
mysql> ``UPDATE user SET Password = ``PASSWORD``(``'newpass'``) ``WHERE user = ``'root'``;
mysql> FLUSH ``PRIVILEGES``;
在丟失root密碼的時(shí)候，可以這樣
mysqld_safe ``--skip-grant-tables&
mysql -u root mysql
mysql> ``UPDATE user SET password``=``PASSWORD``(``"new password"``) ``WHERE user``=``'root'``;
mysql> FLUSH ``PRIVILEGES``;
快捷命令說明：
? command + w 關(guān)閉標(biāo)簽command + 數(shù)字 command + 左右方向鍵 切換標(biāo)簽command + enter 切換全屏command + f 查找command + d 垂直分屏command + shift + d 水平分屏command + option + 方向鍵 command + [ 或 command + ] 切換屏幕command + ; 查看歷史命令command + shift + h 查看剪貼板歷史ctrl + u 清除當(dāng)前行ctrl + l 清屏ctrl + a 到行首ctrl + e 到行尾ctrl + f/b 前進(jìn)后退ctrl + p 上一條命令ctrl + r 搜索命令歷史
新聞表設(shè)計(jì)
字段說明
<pre class="md-fences md-end-block" lang="sql" contenteditable="false" cid="n599" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">-- ID: 新聞的唯一標(biāo)識(shí)
-- title: 新聞的標(biāo)題
-- content: 新聞的內(nèi)容
-- create_time: 新聞添加的時(shí)間
-- types: 新聞的類型
-- image: 新的縮略圖
-- author: 作者
-- view_count: 瀏覽量
-- is_valid: 刪除標(biāo)記
CREATE TABLE news(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content VARCHAR(2000) NOT NULL,
types VARCHAR(10) NOT NULL,
image VARCHAR(300) NULL,
author VARCHAR(20) NULL,
view_count INT DEFAULT 0,
create_time DATETIME NULL,
is_valid SMALLINT DEFAULT 1,
PRIMARY KEY (id)
) DEFAULT CHARSET = 'UTF8'
?
-- 任務(wù)
-- 1、創(chuàng)建一個(gè)數(shù)據(jù)庫，然后設(shè)計(jì)一個(gè)新聞表(數(shù)據(jù)類型要使用合理)
-- 2、使用SQL語句向數(shù)據(jù)表寫入十五條不同的數(shù)據(jù)
-- 3、使用SQL語句查詢類別為"百家"的新聞數(shù)據(jù)
-- 4、使用SQL語句刪除一條新聞數(shù)據(jù)
-- 5、使用SQL語句查詢所有的新聞，以添加時(shí)間的倒序進(jìn)行排列
-- 6、使用SQL語句查詢第二頁(每一頁5條數(shù)據(jù))</pre>
Flask-SQLAlchemy使用
Queck-Start快速上手
<pre class="md-fences md-end-block" lang="python" contenteditable="false" cid="n625" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">"""
?
"""
from flask import Flask
from flask-sqlachemy improt SQLAlchemy
?
app = Flask(name)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
?
class User(db.Model)
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nuallable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
def repr(self):
return '<User %r>' % self.username</pre>
為了創(chuàng)建初始數(shù)據(jù)庫，只需db從交互式Python shell 導(dǎo)入對(duì)象并運(yùn)行該SQLAlchemy.create_all()方法來創(chuàng)建表和數(shù)據(jù)庫：
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n644" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> from yourapplication import db
db.create_all()</pre>
接著，需要給你的數(shù)據(jù)庫添加一些數(shù)據(jù)，例如新增兩個(gè)用戶:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n655" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> from yourapplication import User
admin = User(username='admin', email='admin@example.com')
guest = User(username='guest', email='guest@example.com')</pre>
但是它們還沒有真正地寫入到數(shù)據(jù)庫中，因此讓我們來確保它們已經(jīng)寫入到數(shù)據(jù)庫中:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n668" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> db.session.add(admin)
db.session.add(guest)
db.session.commit()</pre>
訪問數(shù)據(jù)庫中的數(shù)據(jù)很簡(jiǎn)單:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n679" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> User.query.all()
[<User u'admin'>, <User u'guest'>]
User.query.filter_by(username='admin').first()
<User u'admin'></pre>
最簡(jiǎn)單的關(guān)系型數(shù)據(jù)庫
SQLAlchemy 連接到關(guān)系型數(shù)據(jù)庫，關(guān)系型數(shù)據(jù)最擅長(zhǎng)的東西就是關(guān)系。因此，我們將創(chuàng)建一個(gè)使用兩張相互關(guān)聯(lián)的表的應(yīng)用作為例子:
<pre class="md-fences md-end-block" lang="python" contenteditable="false" cid="n721" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">from datetime import datetime
?
?
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(80))
body = db.Column(db.Text)
pub_date = db.Column(db.DateTime)
?
category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
category = db.relationship('Category',
backref=db.backref('posts', lazy='dynamic'))
?
def init(self, title, body, category, pub_date=None):
self.title = title
self.body = body
if pub_date is None:
pub_date = datetime.utcnow()
self.pub_date = pub_date
self.category = category
?
def repr(self):
return '<Post %r>' % self.title
?
?
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
?
def init(self, name):
self.name = name
?
def repr(self):
return '<Category %r>' % self.name</pre>
首先讓我們創(chuàng)建一些對(duì)象:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n739" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> py = Category('Python')
p = Post('Hello Python!', 'Python is pretty cool', py)
db.session.add(py)
db.session.add(p)</pre>
現(xiàn)在因?yàn)槲覀冊(cè)?backref 中聲明了 posts 作為動(dòng)態(tài)關(guān)系，查詢顯示為:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n757" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> py.posts
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x1027d37d0></pre>
它的行為像一個(gè)普通的查詢對(duì)象，因此我們可以查詢與我們測(cè)試的 “Python” 分類相關(guān)的所有文章(posts):
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n775" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> py.posts.all()
[<Post 'Hello Python!'>]</pre>
啟蒙之路
您僅需要知道與普通的 SQLAlchemy 不同之處:
-
SQLAlchemy允許您訪問下面的東西:sqlalchemy和sqlalchemy.orm下所有的函數(shù)和類一個(gè)叫做 session 的預(yù)配置范圍的會(huì)話(session)
metadata屬性engine屬性SQLAlchemy.create_all()和SQLAlchemy.drop_all()，根據(jù)模型用來創(chuàng)建以及刪除表格的方法一個(gè)
Model基類，即是一個(gè)已配置的聲明(declarative)的基礎(chǔ)(base)
Model聲明基類行為類似一個(gè)常規(guī)的 Python 類，不過有個(gè) query 屬性，可以用來查詢模型 (Model和BaseQuery)您必須提交會(huì)話，但是沒有必要在每個(gè)請(qǐng)求后刪除它(session)，F(xiàn)lask-SQLAlchemy 會(huì)幫您完成刪除操作。
配置
下面是 Flask-SQLAlchemy 中存在的配置值。Flask-SQLAlchemy 從您的 Flask 主配置中加載這些值。 注意其中的一些在引擎創(chuàng)建后不能修改，所以確保盡早配置且不在運(yùn)行時(shí)修改它們。
配置鍵
Flask-SQLAlchemy 擴(kuò)展能夠識(shí)別的配置鍵的清單:
<figure class="md-table-fig" contenteditable="false" cid="n953" mdtype="table" style="box-sizing: border-box; margin: -8px 0px 0px -8px; overflow-x: auto; max-width: calc(100% + 16px); padding: 8px;">
SQLALCHEMY_DATABASE_URI |
用于連接數(shù)據(jù)的數(shù)據(jù)庫。例如：sqlite:////tmp/test.db``mysql://username:password@server/db
|
|---|---|
SQLALCHEMY_BINDS |
一個(gè)映射綁定 (bind) 鍵到 SQLAlchemy 連接 URIs 的字典。 更多的信息請(qǐng)參閱 綁定多個(gè)數(shù)據(jù)庫。 |
SQLALCHEMY_ECHO |
如果設(shè)置成 True，SQLAlchemy 將會(huì)記錄所有 發(fā)到標(biāo)準(zhǔn)輸出(stderr)的語句，這對(duì)調(diào)試很有幫助。 |
SQLALCHEMY_RECORD_QUERIES |
可以用于顯式地禁用或者啟用查詢記錄。查詢記錄 在調(diào)試或者測(cè)試模式下自動(dòng)啟用。更多信息請(qǐng)參閱get_debug_queries()。 |
SQLALCHEMY_NATIVE_UNICODE |
可以用于顯式地禁用支持原生的 unicode。這是 某些數(shù)據(jù)庫適配器必須的（像在 Ubuntu 某些版本上的 PostgreSQL），當(dāng)使用不合適的指定無編碼的數(shù)據(jù)庫 默認(rèn)值時(shí)。 |
SQLALCHEMY_POOL_SIZE |
數(shù)據(jù)庫連接池的大小。默認(rèn)是數(shù)據(jù)庫引擎的默認(rèn)值 （通常是 5）。 |
SQLALCHEMY_POOL_TIMEOUT |
指定數(shù)據(jù)庫連接池的超時(shí)時(shí)間。默認(rèn)是 10。 |
SQLALCHEMY_POOL_RECYCLE |
自動(dòng)回收連接的秒數(shù)。這對(duì) MySQL 是必須的，默認(rèn) 情況下 MySQL 會(huì)自動(dòng)移除閑置 8 小時(shí)或者以上的連接。 需要注意地是如果使用 MySQL 的話， Flask-SQLAlchemy 會(huì)自動(dòng)地設(shè)置這個(gè)值為 2 小時(shí)。 |
SQLALCHEMY_MAX_OVERFLOW |
控制在連接池達(dá)到最大值后可以創(chuàng)建的連接數(shù)。當(dāng)這些額外的 連接回收到連接池后將會(huì)被斷開和拋棄。 |
SQLALCHEMY_TRACK_MODIFICATIONS |
如果設(shè)置成 True (默認(rèn)情況)，F(xiàn)lask-SQLAlchemy 將會(huì)追蹤對(duì)象的修改并且發(fā)送信號(hào)。這需要額外的內(nèi)存， 如果不必要的可以禁用它。 |
</figure>
連接 URI 格式
完整連接 URI 格式列表請(qǐng)?zhí)D(zhuǎn)到 SQLAlchemy 下面的文檔(支持的數(shù)據(jù)庫)。這里展示了一些常見的連接字符串。
SQLAlchemy 把一個(gè)引擎的源表示為一個(gè)連同設(shè)定引擎選項(xiàng)的可選字符串參數(shù)的 URI。URI 的形式是:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1003" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">dialect+driver://username:password@host:port/database</pre>
該字符串中的許多部分是可選的。如果沒有指定驅(qū)動(dòng)器，會(huì)選擇默認(rèn)的（確保在這種情況下 不 包含 + ）。
Postgres:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1008" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">postgresql://scott:tiger@localhost/mydatabase</pre>
MySQL:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1011" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">mysql://scott:tiger@localhost/mydatabase</pre>
Oracle:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1014" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">oracle://scott:tiger@127.0.0.1:1521/sidname</pre>
SQLite (注意開頭的四個(gè)斜線):
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1017" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">sqlite:////absolute/path/to/foo.db</pre>
聲明模型
通常下，F(xiàn)lask-SQLAlchemy 的行為就像一個(gè)來自 declarative 擴(kuò)展配置正確的 declarative 基類。因此，我們強(qiáng)烈建議您閱讀 SQLAlchemy 文檔以獲取一個(gè)全面的參考。盡管如此，我們這里還是給出了最常用的示例。
需要牢記的事情:
您的所有模型的基類叫做 db.Model。它存儲(chǔ)在您必須創(chuàng)建的 SQLAlchemy 實(shí)例上。 細(xì)節(jié)請(qǐng)參閱 快速入門。
有一些部分在 SQLAlchemy 上是必選的，但是在 Flask-SQLAlchemy 上是可選的。 比如表名是自動(dòng)地為您設(shè)置好的，除非您想要覆蓋它。它是從轉(zhuǎn)成小寫的類名派生出來的，即 “CamelCase” 轉(zhuǎn)換為 “camel_case”。
簡(jiǎn)單示例
一個(gè)非常簡(jiǎn)單的例子:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1037" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True)
email = db.Column(db.String(120), unique=True)
?
def init(self, username, email):
self.username = username
self.email = email
?
def repr(self):
return '<User %r>' % self.username</pre>
用 Column 來定義一列。列名就是您賦值給那個(gè)變量的名稱。如果您想要在表中使用不同的名稱，您可以提供一個(gè)想要的列名的字符串作為可選第一個(gè)參數(shù)。主鍵用 primary_key=True 標(biāo)記?？梢园讯鄠€(gè)鍵標(biāo)記為主鍵，此時(shí)它們作為復(fù)合主鍵。
列的類型是 Column 的第一個(gè)參數(shù)。您可以直接提供它們或進(jìn)一步規(guī)定（比如提供一個(gè)長(zhǎng)度）。下面的類型是最常用的:
<figure class="md-table-fig" contenteditable="false" cid="n1042" mdtype="table" style="box-sizing: border-box; margin: -8px 0px 0px -8px; overflow-x: auto; max-width: calc(100% + 16px); padding: 8px;">
| Integer | 一個(gè)整數(shù) |
|---|---|
| String (size) | 有長(zhǎng)度限制的字符串 |
| Text | 一些較長(zhǎng)的 unicode 文本 |
| DateTime | 表示為 Python datetime 對(duì)象的 時(shí)間和日期 |
| Float | 存儲(chǔ)浮點(diǎn)值 |
| Boolean | 存儲(chǔ)布爾值 |
| PickleType | 存儲(chǔ)為一個(gè)持久化的 Python 對(duì)象 |
| LargeBinary | 存儲(chǔ)一個(gè)任意大的二進(jìn)制數(shù)據(jù) |
</figure>
一對(duì)多(one-to-many)關(guān)系
最為常見的關(guān)系就是一對(duì)多的關(guān)系。因?yàn)殛P(guān)系在它們建立之前就已經(jīng)聲明，您可以使用 字符串來指代還沒有創(chuàng)建的類(例如如果 Person 定義了一個(gè)到 Article 的關(guān)系，而 Article在文件的后面才會(huì)聲明)。
關(guān)系使用 relationship() 函數(shù)表示。然而外鍵必須用類 sqlalchemy.schema.ForeignKey 來單獨(dú)聲明:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1072" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">class Person(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
addresses = db.relationship('Address', backref='person',
lazy='dynamic')
?
class Address(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(50))
person_id = db.Column(db.Integer, db.ForeignKey('person.id'))</pre>
db.relationship() 做了什么？這個(gè)函數(shù)返回一個(gè)可以做許多事情的新屬性。在本案例中，我們讓它指向 Address 類并加載多個(gè)地址。它如何知道會(huì)返回不止一個(gè)地址？因?yàn)?SQLALchemy 從您的聲明中猜測(cè)了一個(gè)有用的默認(rèn)值。 如果您想要一對(duì)一關(guān)系，您可以把 uselist=False 傳給 relationship() 。
那么 backref 和 lazy 意味著什么了？backref 是一個(gè)在 Address 類上聲明新屬性的簡(jiǎn)單方法。您也可以使用 my_address.person 來獲取使用該地址(address)的人(person)。lazy 決定了 SQLAlchemy 什么時(shí)候從數(shù)據(jù)庫中加載數(shù)據(jù):
'select'(默認(rèn)值) 就是說 SQLAlchemy 會(huì)使用一個(gè)標(biāo)準(zhǔn)的 select 語句必要時(shí)一次加載數(shù)據(jù)。'joined'告訴 SQLAlchemy 使用 JOIN 語句作為父級(jí)在同一查詢中來加載關(guān)系。'subquery'類似'joined'，但是 SQLAlchemy 會(huì)使用子查詢。'dynamic'在有多條數(shù)據(jù)的時(shí)候是特別有用的。不是直接加載這些數(shù)據(jù)，SQLAlchemy 會(huì)返回一個(gè)查詢對(duì)象，在加載數(shù)據(jù)前您可以過濾（提?。┧鼈?。
您如何為反向引用（backrefs）定義惰性（lazy）狀態(tài)？使用 backref() 函數(shù):
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1092" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
addresses = db.relationship('Address',
backref=db.backref('person', lazy='joined'), lazy='dynamic')</pre>
多對(duì)多(many-to-many)關(guān)系
如果您想要用多對(duì)多關(guān)系，您需要定義一個(gè)用于關(guān)系的輔助表。對(duì)于這個(gè)輔助表， 強(qiáng)烈建議 不 使用模型，而是采用一個(gè)實(shí)際的表:
<pre class="md-fences md-end-block" lang="" contenteditable="false" cid="n1096" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">tags = db.Table('tags',
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
db.Column('page_id', db.Integer, db.ForeignKey('page.id'))
)
?
class Page(db.Model):
id = db.Column(db.Integer, primary_key=True)
tags = db.relationship('Tag', secondary=tags,
backref=db.backref('pages', lazy='dynamic'))
?
class Tag(db.Model):
id = db.Column(db.Integer, primary_key=True)</pre>
這里我們配置 Page.tags 加載后作為標(biāo)簽的列表，因?yàn)槲覀儾⒉黄谕宽摮霈F(xiàn)太多的標(biāo)簽。而每個(gè) tag 的頁面列表（ Tag.pages）是一個(gè)動(dòng)態(tài)的反向引用。 正如上面提到的，這意味著您會(huì)得到一個(gè)可以發(fā)起 select 的查詢對(duì)象。
選擇(Select),插入(Insert), 刪除(Delete)
現(xiàn)在您已經(jīng)有了 declared models，是時(shí)候從數(shù)據(jù)庫中查詢數(shù)據(jù)。我們將會(huì)使用 快速入門章節(jié)中定義的數(shù)據(jù)模型。
插入記錄
在查詢數(shù)據(jù)之前我們必須先插入數(shù)據(jù)。您的所有模型都應(yīng)該有一個(gè)構(gòu)造函數(shù)，如果您 忘記了，請(qǐng)確保加上一個(gè)。只有您自己使用這些構(gòu)造函數(shù)而 SQLAlchemy 在內(nèi)部不會(huì)使用它， 所以如何定義這些構(gòu)造函數(shù)完全取決與您。
向數(shù)據(jù)庫插入數(shù)據(jù)分為三個(gè)步驟:
創(chuàng)建 Python 對(duì)象
把它添加到會(huì)話
提交會(huì)話
這里的會(huì)話不是 Flask 的會(huì)話，而是 Flask-SQLAlchemy 的會(huì)話。它本質(zhì)上是一個(gè) 數(shù)據(jù)庫事務(wù)的加強(qiáng)版本。它是這樣工作的:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1133" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> from yourapp import User
me = User('admin', 'admin@example.com')
db.session.add(me)
db.session.commit()</pre>
好吧，這不是很難吧。但是在您把對(duì)象添加到會(huì)話之前， SQLAlchemy 基本不考慮把它加到事務(wù)中。這是好事，因?yàn)槟匀豢梢苑艞壐?。比如想?在一個(gè)頁面上創(chuàng)建文章，但是您只想把文章傳遞給模板來預(yù)覽渲染，而不是把它存進(jìn)數(shù)據(jù)庫。
調(diào)用 add() 函數(shù)會(huì)添加對(duì)象。它會(huì)發(fā)出一個(gè) INSERT 語句給數(shù)據(jù)庫，但是由于事務(wù)仍然沒有提交，您不會(huì)立即得到返回的 ID 。如果您提交，您的用戶會(huì)有一個(gè) ID:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1138" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> me.id
1</pre>
刪除記錄
刪除記錄是十分類似的，使用 delete() 代替 add():
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1142" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> db.session.delete(me)
db.session.commit()</pre>
查詢記錄
那么我們?cè)趺磸臄?shù)據(jù)庫中查詢數(shù)據(jù)？為此，F(xiàn)lask-SQLAlchemy 在您的 Model 類上提供了 query 屬性。當(dāng)您訪問它時(shí)，您會(huì)得到一個(gè)新的所有記錄的查詢對(duì)象。在使用 all()或者 first() 發(fā)起查詢之前可以使用方法 filter() 來過濾記錄。如果您想要用主鍵查詢的話，也可以使用 get()。
下面的查詢假設(shè)數(shù)據(jù)庫中有如下條目:
<figure class="md-table-fig" contenteditable="false" cid="n1148" mdtype="table" style="box-sizing: border-box; margin: -8px 0px 0px -8px; overflow-x: auto; max-width: calc(100% + 16px); padding: 8px;">
| id | username | |
|---|---|---|
| 1 | admin | admin@example.com |
| 2 | peter | peter@example.org |
| 3 | guest | guest@example.com |
</figure>
通過用戶名查詢用戶:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1167" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> peter = User.query.filter_by(username='peter').first()
peter.id
1
peter.email
u'peter@example.org'</pre>
同上但是查詢一個(gè)不存在的用戶名返回 None:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1170" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> missing = User.query.filter_by(username='missing').first()
missing is None
True</pre>
使用更復(fù)雜的表達(dá)式查詢一些用戶:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1173" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> User.query.filter(User.email.endswith('@example.com')).all()
[<User u'admin'>, <User u'guest'>]</pre>
按某種規(guī)則對(duì)用戶排序:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1176" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> User.query.order_by(User.username)
[<User u'admin'>, <User u'guest'>, <User u'peter'>]</pre>
限制返回用戶的數(shù)量:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1179" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> User.query.limit(1).all()
[<User u'admin'>]</pre>
用主鍵查詢用戶:
<pre class="md-fences md-end-block" lang="shell" contenteditable="false" cid="n1182" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">>>> User.query.get(1)
<User u'admin'></pre>
在視圖中查詢
當(dāng)您編寫 Flask 視圖函數(shù)，對(duì)于不存在的條目返回一個(gè) 404 錯(cuò)誤是非常方便的。因?yàn)檫@是一個(gè)很常見的問題，F(xiàn)lask-SQLAlchemy 為了解決這個(gè)問題提供了一個(gè)幫助函數(shù)。可以使用 get_or_404() 來代替 get()，使用 first_or_404() 來代替 first()。這樣會(huì)拋出一個(gè) 404 錯(cuò)誤，而不是返回 None:
<pre class="md-fences md-end-block" lang="python" contenteditable="false" cid="n1186" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, "Liberation Mono", Courier, monospace; font-size: 0.9em; white-space: pre; text-align: left; break-inside: avoid; display: block; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(221, 221, 221); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; background-position: inherit inherit; background-repeat: inherit inherit;">@app.route('/user/<username>')
def show_user(username):
user = User.query.filter_by(username=username).first_or_404()
return render_template('show_user.html', user=user)</pre>
