erpnext-database

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

ERPNext Database Operations

ERPNext 数据库操作

Quick Overview

快速概览

Frappe provides three abstraction levels for database operations:

Level	API	Usage
High-level ORM	`frappe.get_doc` , `frappe.new_doc`	Document CRUD with validations
Mid-level Query	`frappe.db.get_list` , `frappe.db.get_value`	Reading with filters
Low-level SQL	`frappe.db.sql` , `frappe.qb`	Complex queries, reports

RULE: Always use the highest abstraction level appropriate for your use case.

Frappe为数据库操作提供了三个抽象层级：

层级	API	用途
高层级ORM	`frappe.get_doc` , `frappe.new_doc`	带验证的文档增删改查
中层级查询	`frappe.db.get_list` , `frappe.db.get_value`	带过滤条件的读取操作
低层级SQL	`frappe.db.sql` , `frappe.qb`	复杂查询、报表生成

规则：始终选择与你的使用场景最匹配的最高抽象层级。

Decision Tree

决策树

What do you want to do?
│
├─ Create/modify/delete document?
│  └─ frappe.get_doc() + .insert()/.save()/.delete()
│
├─ Get single document?
│  ├─ Changes frequently? → frappe.get_doc()
│  └─ Changes rarely? → frappe.get_cached_doc()
│
├─ List of documents?
│  ├─ With user permissions? → frappe.db.get_list()
│  └─ Without permissions? → frappe.get_all()
│
├─ Single field value?
│  ├─ Regular DocType → frappe.db.get_value()
│  └─ Single DocType → frappe.db.get_single_value()
│
├─ Direct update without triggers?
│  └─ frappe.db.set_value() or doc.db_set()
│
└─ Complex query with JOINs?
   └─ frappe.qb (Query Builder) or frappe.db.sql()

What do you want to do?
│
├─ Create/modify/delete document?
│  └─ frappe.get_doc() + .insert()/.save()/.delete()
│
├─ Get single document?
│  ├─ Changes frequently? → frappe.get_doc()
│  └─ Changes rarely? → frappe.get_cached_doc()
│
├─ List of documents?
│  ├─ With user permissions? → frappe.db.get_list()
│  └─ Without permissions? → frappe.get_all()
│
├─ Single field value?
│  ├─ Regular DocType → frappe.db.get_value()
│  └─ Single DocType → frappe.db.get_single_value()
│
├─ Direct update without triggers?
│  └─ frappe.db.set_value() or doc.db_set()
│
└─ Complex query with JOINs?
   └─ frappe.qb (Query Builder) or frappe.db.sql()

Most Used Patterns

最常用模式

Get Document

获取文档

python

undefined

python

undefined

With ORM (triggers validations)

doc = frappe.get_doc('Sales Invoice', 'SINV-00001')

Cached (faster for frequently accessed docs)

doc = frappe.get_cached_doc('Company', 'My Company')

undefined

doc = frappe.get_cached_doc('Company', 'My Company')

undefined

List Query

列表查询

python

undefined

python

undefined

With user permissions

tasks = frappe.db.get_list('Task', filters={'status': 'Open'}, fields=['name', 'subject'], order_by='creation desc', page_length=50 )

Without permissions

all_tasks = frappe.get_all('Task', filters={'status': 'Open'})

undefined

all_tasks = frappe.get_all('Task', filters={'status': 'Open'})

undefined

Single Value

单个值查询

python

undefined

python

undefined

Single field

status = frappe.db.get_value('Task', 'TASK001', 'status')

Multiple fields

subject, status = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'])

As dict

data = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'], as_dict=True)

undefined

data = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'], as_dict=True)

undefined

Create Document

创建文档

python

doc = frappe.get_doc({
    'doctype': 'Task',
    'subject': 'New Task',
    'status': 'Open'
})
doc.insert()

python

doc = frappe.get_doc({
    'doctype': 'Task',
    'subject': 'New Task',
    'status': 'Open'
})
doc.insert()

Update Document

更新文档

python

undefined

python

undefined

Via ORM (with validations)

doc = frappe.get_doc('Task', 'TASK001') doc.status = 'Completed' doc.save()

Direct (without validations) - use carefully!

frappe.db.set_value('Task', 'TASK001', 'status', 'Completed')

---

frappe.db.set_value('Task', 'TASK001', 'status', 'Completed')

---

Filter Operators

过滤操作符

python

{'status': 'Open'}                          # =
{'status': ['!=', 'Cancelled']}             # !=
{'amount': ['>', 1000]}                     # >
{'amount': ['>=', 1000]}                    # >=
{'status': ['in', ['Open', 'Working']]}     # IN
{'date': ['between', ['2024-01-01', '2024-12-31']]}  # BETWEEN
{'subject': ['like', '%urgent%']}           # LIKE
{'description': ['is', 'set']}              # IS NOT NULL
{'description': ['is', 'not set']}          # IS NULL

python

{'status': 'Open'}                          # =
{'status': ['!=', 'Cancelled']}             # !=
{'amount': ['>', 1000]}                     # >
{'amount': ['>=', 1000]}                    # >=
{'status': ['in', ['Open', 'Working']]}     # IN
{'date': ['between', ['2024-01-01', '2024-12-31']]}  # BETWEEN
{'subject': ['like', '%urgent%']}           # LIKE
{'description': ['is', 'set']}              # IS NOT NULL
{'description': ['is', 'not set']}          # IS NULL

Query Builder (frappe.qb)

查询构建器（frappe.qb）

python

Task = frappe.qb.DocType('Task')

results = (
    frappe.qb.from_(Task)
    .select(Task.name, Task.subject)
    .where(Task.status == 'Open')
    .orderby(Task.creation, order='desc')
    .limit(10)
).run(as_dict=True)

python

Task = frappe.qb.DocType('Task')

results = (
    frappe.qb.from_(Task)
    .select(Task.name, Task.subject)
    .where(Task.status == 'Open')
    .orderby(Task.creation, order='desc')
    .limit(10)
).run(as_dict=True)

With JOIN

关联查询

python

SI = frappe.qb.DocType('Sales Invoice')
Customer = frappe.qb.DocType('Customer')

results = (
    frappe.qb.from_(SI)
    .inner_join(Customer)
    .on(SI.customer == Customer.name)
    .select(SI.name, Customer.customer_name)
    .where(SI.docstatus == 1)
).run(as_dict=True)

python

SI = frappe.qb.DocType('Sales Invoice')
Customer = frappe.qb.DocType('Customer')

results = (
    frappe.qb.from_(SI)
    .inner_join(Customer)
    .on(SI.customer == Customer.name)
    .select(SI.name, Customer.customer_name)
    .where(SI.docstatus == 1)
).run(as_dict=True)

Caching

缓存

Basics

基础用法

python

undefined

python

undefined

Set/Get

frappe.cache.set_value('key', 'value') value = frappe.cache.get_value('key')

With expiry

frappe.cache.set_value('key', 'value', expires_in_sec=3600)

Delete

frappe.cache.delete_value('key')

undefined

frappe.cache.delete_value('key')

undefined

@redis_cache Decorator

@redis_cache 装饰器

python

from frappe.utils.caching import redis_cache

@redis_cache(ttl=300)  # 5 minutes
def get_dashboard_data(user):
    return expensive_calculation(user)

python

from frappe.utils.caching import redis_cache

@redis_cache(ttl=300)  # 5 minutes
def get_dashboard_data(user):
    return expensive_calculation(user)

Invalidate cache

get_dashboard_data.clear_cache()

---

get_dashboard_data.clear_cache()

---

Transactions

事务

Framework manages transactions automatically:

Context	Commit	Rollback
POST/PUT request	After success	On exception
Background job	After success	On exception

框架会自动管理事务：

上下文	提交	回滚
POST/PUT 请求	成功后	发生异常时
后台任务	成功后	发生异常时

Manual (rarely needed)

手动事务（极少需要）

python

frappe.db.savepoint('my_savepoint')
try:
    # operations
    frappe.db.commit()
except:
    frappe.db.rollback(save_point='my_savepoint')

python

frappe.db.savepoint('my_savepoint')
try:
    # operations
    frappe.db.commit()
except:
    frappe.db.rollback(save_point='my_savepoint')

Critical Rules

重要规则

1. NEVER Use String Formatting in SQL

1. 绝对不要在SQL中使用字符串格式化

python

undefined

python

undefined

❌ SQL Injection risk!

frappe.db.sql(f"SELECT * FROM

tabUser

WHERE name = '{user_input}'")

frappe.db.sql(f"SELECT * FROM

tabUser

WHERE name = '{user_input}'")

✅ Parameterized

frappe.db.sql("SELECT * FROM

tabUser

WHERE name = %(name)s", {'name': user_input})

undefined

frappe.db.sql("SELECT * FROM

tabUser

WHERE name = %(name)s", {'name': user_input})

undefined

2. NEVER Commit in Controller Hooks

2. 绝对不要在控制器钩子中提交事务

python

undefined

python

undefined

❌ WRONG

def validate(self): frappe.db.commit() # Never do this!

✅ Framework handles commits

undefined

undefined

3. ALWAYS Paginate

3. 始终使用分页

python

undefined

python

undefined

✅ Always limit

docs = frappe.get_all('Sales Invoice', page_length=100)

undefined

docs = frappe.get_all('Sales Invoice', page_length=100)

undefined

4. Avoid N+1 Queries

4. 避免N+1查询问题

python

undefined

python

undefined

❌ N+1 problem

for name in names: doc = frappe.get_doc('Customer', name)

✅ Batch fetch

docs = frappe.get_all('Customer', filters={'name': ['in', names]})

---

docs = frappe.get_all('Customer', filters={'name': ['in', names]})

---

Version Differences

版本差异

Feature	v14	v15	v16
Transaction hooks	❌	✅	✅
bulk_update	❌	✅	✅
Aggregate syntax	String	String	Dict

特性	v14	v15	v16
事务钩子	❌	✅	✅
bulk_update	❌	✅	✅
聚合语法	字符串	字符串	字典

v16 Aggregate Syntax

v16 聚合语法

python

undefined

python

undefined

v14/v15

fields=['count(name) as count']

v16

fields=[{'COUNT': 'name', 'as': 'count'}]

---

fields=[{'COUNT': 'name', 'as': 'count'}]

---

Reference Files

参考文件

See the

references/

folder for detailed documentation:

methods-reference.md - All Database and Document API methods
query-patterns.md - Filter operators and Query Builder syntax
caching-patterns.md - Redis cache patterns and @redis_cache
examples.md - Complete working examples
anti-patterns.md - Common mistakes and how to avoid them

详见

references/

文件夹中的详细文档：

methods-reference.md - 所有数据库与文档API方法
query-patterns.md - 过滤操作符与查询构建器语法
caching-patterns.md - Redis缓存模式与@redis_cache
examples.md - 完整可运行示例
anti-patterns.md - 常见错误及规避方法

Quick Reference

快速参考

Action	Method
Get document	`frappe.get_doc(doctype, name)`
Cached document	`frappe.get_cached_doc(doctype, name)`
New document	`frappe.new_doc(doctype)` or `frappe.get_doc({...})`
Save document	`doc.save()`
Insert document	`doc.insert()`
Delete document	`doc.delete()` or `frappe.delete_doc()`
Get list	`frappe.db.get_list()` / `frappe.get_all()`
Single value	`frappe.db.get_value()`
Single value	`frappe.db.get_single_value()`
Direct update	`frappe.db.set_value()` / `doc.db_set()`
Exists check	`frappe.db.exists()`
Count records	`frappe.db.count()`
Raw SQL	`frappe.db.sql()`
Query Builder	`frappe.qb.from_()`

操作	方法
获取文档	`frappe.get_doc(doctype, name)`
获取缓存文档	`frappe.get_cached_doc(doctype, name)`
创建新文档	`frappe.new_doc(doctype)` 或 `frappe.get_doc({...})`
保存文档	`doc.save()`
插入文档	`doc.insert()`
删除文档	`doc.delete()` 或 `frappe.delete_doc()`
获取文档列表	`frappe.db.get_list()` / `frappe.get_all()`
获取单个字段值	`frappe.db.get_value()`
获取单文档类型字段值	`frappe.db.get_single_value()`
直接更新字段	`frappe.db.set_value()` / `doc.db_set()`
检查文档是否存在	`frappe.db.exists()`
统计记录数	`frappe.db.count()`
原生SQL查询	`frappe.db.sql()`
查询构建器	`frappe.qb.from_()`