Database API

Database operations and query execution with frappe.db

The frappe.db object provides methods for database operations including queries, transactions, and value operations.

Query execution

sql

Execute a SQL query and fetch all rows.

query · str · required

SQL query to execute

values · tuple | list | dict

Values to be escaped and substituted in the query

as_dict · bool · default: "False"

Return results as dictionaries

as_list · bool · default: "False"

Return results as lists

pluck · bool · default: "False"

Return only the first column values

debug · bool · default: "False"

Print query and execution time

auto_commit · bool · default: "False"

Commit after executing the query

as_iterator · bool · default: "False"

Return an iterator instead of fetching all results at once

# Return customer names as dicts
frappe.db.sql("select name from tabCustomer", as_dict=True)

# With parameter substitution
frappe.db.sql("select name from tabCustomer where name like %s", "a%")

# Values as dict
frappe.db.sql(
    "select name from tabCustomer where name like %(name)s and owner=%(owner)s",
    {"name": "a%", "owner": "[e-mail]"}
)

# Pluck single column
customer_names = frappe.db.sql("select name from tabCustomer", pluck=True)

Getting values

get_value

Return a document property or list of properties.

doctype · str · required

DocType name

filters · str | dict

Filters like {"x": "y"} or document name. None for Single DocType

fieldname · str | list · required

Column name or list of column names

as_dict · bool · default: "False"

Return values as dict

debug · bool · default: "False"

Print the query

for_update · bool · default: "False"

Select row with FOR UPDATE clause

# Get single value
email = frappe.db.get_value("User", "Administrator", "email")

# Get multiple values
user_details = frappe.db.get_value(
    "User",
    "Administrator",
    ["email", "full_name"],
    as_dict=True
)

# With filters
first_customer = frappe.db.get_value(
    "Customer",
    {"territory": "India"},
    "name"
)

get_single_value

Get a field value from a Single DocType.

doctype · str · required

Single DocType name

fieldname · str · required

Field name

# Get system settings value
app_name = frappe.db.get_single_value("System Settings", "app_name")

get_all

Get a list of document names matching filters. Does not check permissions.

doctype · str · required

DocType name

filters · dict | list

Filter conditions

fields · list | str

Fields to fetch. Default: ["name"]

order_by · str

Sort order. Example: "creation desc"

limit_start · int

Start index for pagination

limit_page_length · int · default: "0"

Number of records per page. 0 for all records

pluck · str

Column name to pluck values from

# Get all customers
customers = frappe.db.get_all("Customer")

# Get with filters and fields
active_customers = frappe.db.get_all(
    "Customer",
    filters={"disabled": 0},
    fields=["name", "customer_name", "territory"],
    order_by="creation desc",
    limit_page_length=20
)

# Pluck single field
customer_names = frappe.db.get_all("Customer", pluck="name")

get_list

Get a list of documents. Checks permissions.

doctype · str · required

DocType name

filters · dict | list

Filter conditions

fields · list | str

Fields to fetch

order_by · str

Sort order

limit_start · int

Start index for pagination

limit_page_length · int · default: "20"

Number of records per page

# Get list with permission check
todos = frappe.db.get_list(
    "ToDo",
    fields=["name", "description"],
    filters={"owner": "[e-mail]"}
)

# Filter as list of lists
recent_todos = frappe.db.get_list(
    "ToDo",
    fields="*",
    filters=[["modified", ">", "2024-01-01"]]
)

Setting values

set_value

Set a field value for a document.

doctype · str · required

DocType name

name · str · required

Document name

fieldname · str | dict · required

Field name or dict of field-value pairs

value · any

Value to set (not required if fieldname is dict)

update_modified · bool · default: "True"

Update the modified timestamp

# Set single value
frappe.db.set_value("Customer", "CUST-001", "territory", "India")

# Set multiple values
frappe.db.set_value(
    "Customer",
    "CUST-001",
    {
        "territory": "India",
        "customer_group": "Commercial"
    }
)

Checking existence

exists

Check if a document exists.

doctype · str · required

DocType name

name · str | dict

Document name or filters

return · str | None

Document name if exists, None otherwise

# Check by name
if frappe.db.exists("Customer", "CUST-001"):
    print("Customer exists")

# Check by filters
if frappe.db.exists("Customer", {"customer_name": "John Doe"}):
    print("Customer with this name exists")

Counting records

count

Count documents matching filters.

doctype · str · required

DocType name

filters · dict | list

Filter conditions

return · int

Number of matching documents

# Count all customers
total_customers = frappe.db.count("Customer")

# Count with filters
active_customers = frappe.db.count("Customer", {"disabled": 0})

Transactions

commit

Commit the current transaction.

frappe.db.commit()

rollback

Rollback the current transaction.

frappe.db.rollback()

savepoint

Create a savepoint for partial rollback.

savepoint · str · required

Savepoint name

# Create savepoint
frappe.db.savepoint("my_savepoint")

# Rollback to savepoint
frappe.db.rollback(save_point="my_savepoint")

Table operations

table_exists

Check if a table exists in the database.

table_name · str · required

Table name (use tab prefix for DocTypes)

return · bool

True if table exists

if frappe.db.table_exists("tabCustomer"):
    print("Customer table exists")

get_table_columns

Get the list of column names in a table.

table_name · str · required

Table name

return · list

List of column names

columns = frappe.db.get_table_columns("tabCustomer")

Bulk operations

delete

Delete a document from the database directly (bypasses controller logic).

doctype · str · required

DocType name

filters · str | dict · required

Document name or filter conditions

# Delete by name
frappe.db.delete("Customer", "CUST-001")

# Delete by filters
frappe.db.delete("Customer", {"disabled": 1, "creation": ["<", "2020-01-01"]})

Use frappe.delete_doc() instead when you need to trigger controller events.