Database API

frappe.db.get_list

frappe.db.get_list(doctype, filters, or_filters, fields, order_by, group_by, start, page_length)

• Also aliased to frappe.get_list

Returns a list of records from a doctype table. ORM Wrapper for a SELECT query. Will also apply user permissions for the records for the session user. Only returns the document names if the fields keyword argument is not given. By default this method returns a list of dicts, but, you can pluck a particular field by giving the pluck keyword argument:

frappe.db.get_list('Employee')

# output
[{'name': 'HR-EMP-00008'},
 {'name': 'HR-EMP-00006'},
 {'name': 'HR-EMP-00010'},
 {'name': 'HR-EMP-00005'}
]

# with pluck
frappe.db.get_list('Employee', pluck='name')

# output
['HR-EMP-00008',
 'HR-EMP-00006',
 'HR-EMP-00010',
 'HR-EMP-00005'
]

Combining filters and other arguments:

frappe.db.get_list('Task',
    filters={
        'status': 'Open'
    },
    fields=['subject', 'date'],
    order_by='date desc',
    start=10,
    page_length=20,
    as_list=True
)

# output
(('Update Branding and Design', '2019-09-04'),
('Missing Documentation', '2019-09-02'),
('Fundraiser for Foundation', '2019-09-03'))

# Tasks with date after 2019-09-08
frappe.db.get_list('Task', filters={
    'date': ['>', '2019-09-08']
})

# Tasks with date between 2020-04-01 and 2021-03-31 (both inclusive)
frappe.db.get_list('Task', filters=[[
    'date', 'between', ['2020-04-01', '2021-03-31']
]])

# Tasks with subject that contains "test"
frappe.db.get_list('Task', filters={
    'subject': ['like', '%test%']
})

# Count number of tasks grouped by status
frappe.db.get_list('Task',
    fields=['count(name) as count', 'status'],
    group_by='status'
)
# output
[{'count': 1, 'status': 'Working'},
 {'count': 2, 'status': 'Overdue'},
 {'count': 2, 'status': 'Open'},
 {'count': 1, 'status': 'Filed'},
 {'count': 20, 'status': 'Completed'},
 {'count': 1, 'status': 'Cancelled'}]

frappe.db.get_all

frappe.db.get_all(doctype, filters, or_filters, fields, order_by, group_by, start, page_length)

• Also aliased to frappe.get_all

Same as frappe.db.get_list but will fetch all records without applying permissions.

frappe.db.get_value

frappe.db.get_value(doctype, name, fieldname) or frappe.db.get_value(doctype, filters, fieldname)

• Also aliased to frappe.get_value and frappe.db.get_values

Returns a document's field value or a list of values.

# single value
subject = frappe.db.get_value('Task', 'TASK00002', 'subject')

# multiple values
subject, description = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'])

# as dict
task_dict = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'], as_dict=1)
task_dict.subject
task_dict.description

# with filters, will return the first record that matches filters
subject, description = frappe.db.get_value('Task', {'status': 'Open'}, ['subject', 'description'])

frappe.db.get_single_value

frappe.db.get_single_value(doctype, fieldname)

Returns a field value from a Single DocType.

timezone = frappe.db.get_single_value('System Settings', 'timezone')

frappe.db.set_value

frappe.db.set_value(doctype, name, fieldname, value)

• Also aliased to frappe.db.update

Sets a field's value in the database, does not call the ORM triggers but updates the modified timestamp (unless specified not to).

# update a field value
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject')

# update multiple values
frappe.db.set_value('Task', 'TASK00002', {
    'subject': 'New Subject',
    'description': 'New Description'
})

# update without updating the `modified` timestamp
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject', update_modified=False)

This method won't call ORM triggers like validate and on_update. Use this method to update hidden fields or if you know what you are doing.

frappe.db.exists

frappe.db.exists(doctype, name)

Returns true if a document record exists.

Pass doctype and docname:

frappe.db.exists("User", "jane@example.org", cache=True)

Pass a dict of filters including the "doctype" key:

frappe.db.exists({"doctype": "User", "full_name": "Jane Doe"})

Pass the doctype and a dict of filters:

frappe.db.exists("User", {"full_name": "Jane Doe"})

frappe.db.count

frappe.db.count(doctype, filters)

Returns number of records for a given doctype and filters.

# total number of Task records
frappe.db.count('Task')

# total number of Open tasks
frappe.db.count('Task', {'status': 'Open'})

frappe.db.delete

frappe.db.delete(doctype, filters)

Delete doctype records that match filters. This runs a DML command, which means it can be rolled back. If no filters specified, all the records of the doctype are deleted.

frappe.db.delete("Route History", {
    "modified": ("<=", last_record_to_keep[0].modified),
    "user": user
})

frappe.db.delete("Error Log")
frappe.db.delete("__Test Table")

You may pass the doctype name or an internal table name. Conventionally, internal tables in Frappe are prefixed with __. The API follows this. The above commands run an unconditional DELETE query over tables tabError Log and __Test Table.

frappe.db.truncate

frappe.db.truncate(doctype)

Truncate a table in the database. This runs a DDL command TRUNCATE TABLE, a commit is triggered before the statement is executed. This action cannot be rolled back. You may want to use this for clearing out log tables periodically.

frappe.db.truncate("Error Log")
frappe.db.truncate("__Test Table")

The above commands run a TRUNCATE query over tables tabError Log and __Test Table.

frappe.db.commit

frappe.db.commit()

Commits current transaction. Calls SQL COMMIT.

In most cases you don't need to commit manually. Refer Frappe's Database transaction model below.

frappe.db.savepoint

frappe.db.savepoint(save_point)

Create a named savepoint to which you can later roll back to.

frappe.db.rollback

frappe.db.rollback()

Rollbacks current transaction. Calls SQL ROLLBACK.

Frappe will automatically run frappe.db.rollback() if an exception is thrown during a Web Request of type POST or PUT. Use this if you have to rollback early in a transaction.

frappe.db.rollback(save_point="save_point_name")

Rollback to a specific savepoint instead rolling back full transactions. This rollback won't undo changes done to filesytem and any other rollback watchers.

frappe.db.sql

frappe.db.sql(query, values, as_dict)

Execute an arbitrary SQL query. This may be useful for complex server side reports with join statements, adjusting the database to new features, etc.

Example:

values = {'company': 'Frappe Technologies Inc'}
data = frappe.db.sql("""
    SELECT
        acc.account_number
        gl.debit
        gl.credit
    FROM `tabGL Entry` gl
        LEFT JOIN `tabAccount` acc
        ON gl.account = acc.name
    WHERE gl.company = %(company)s
""", values=values, as_dict=0)

Avoid using this method as it will bypass validations and integrity checks. It's always better to use frappe.get_doc, frappe.db.get_list, etc., if possible.

frappe.db.multisql

frappe.db.multisql({'mariadb': mariadb_query, 'postgres': postgres_query})

Execute the suitable SQL statement for any supported database engine.

frappe.db.rename_table

frappe.db.rename_table(old_name, new_name)

Executes a query to change table name. Specify the DocType or internal table's name directly to rename the table.

Example:

frappe.db.rename_table("__internal_cache", "__temporary_cache")
frappe.db.rename_table("todo", "ToDo")

The second example should be used only if you understand the ramifications of it.

Don't use this to rename DocType tables. Use frappe.rename_doc for that instead

frappe.db.describe

frappe.db.describe(doctype)

Returns a tuple of the table description for given DocType.

frappe.db.change_column_type

frappe.db.change_column_type(doctype, column, new_type)

Changes the type of column for specified DocType.

frappe.db.add_index

frappe.db.add_index(doctype, fields, index_name)

Creates indexes for doctypes for the specified fields.

Note: if you want an index on a TEXT or a BLOB field, you must specify a fixed length to do that.

Example:

frappe.db.add_index("Notes", ["id(10)", "content(500)"], index_name)

frappe.db.add_unique

frappe.db.add_unique(doctype, fields, constraint_name=None)

Creates unique constraint for doctypes for the specified fields.

Example:

frappe.db.add_unique("DoctypeName",["field1","field2"])

Database transaction hooks

Frappe provides hooks for running callbacks before/after transaction commands like commit/rollback are issued. These hooks are useful for:

• Rolling back changes that are done outside of the database if the transaction is rolled back
• Flushing changes outside of the database only if the transaction is committed.

These hooks are :

• frappe.db.before_commit.add(func: Callable)
• frappe.db.after_commit.add(func: Callable)
• frappe.db.before_rollback.add(func: Callable)
• frappe.db.after_rollback.add(func: Callable)

Example usage:

def create_file(self):
    self.write_file()
    # This ensures rollback if DB transaction is rolledback
    frappe.db.after_rollback.add(self.rollback_file)

def rollback_file(self):
    self.delete_file()

Database transaction model

Frappe's database abstractions implement a sane transaction model by default. So in most cases, you won't have to deal with SQL transactions manually. A broad description of this model is described below:

Web requests

• While performing POST or PUT, if any writes were made to the database, they are committed at end of the successful request.
• AJAX calls made using frappe.call are POST by default unless changed.
• GET requests do not cause an implicit commit.
• Any uncaught exception during handling of request will rollback the transaction.

Background/scheduled Jobs

• Calling a function as background or scheduled job will commit the transaction after successful completion.
• Any uncaught exception will cause rollback of the transaction.

Patches

• Successful completion of the patch's execute function will commit the transaction automatically.
• Any uncaught exception will cause rollback of the transaction.

Unit tests

• Transaction is committed after running one test module. Test module means any python test file like test_core.py.
• Transaction is also committed after finishing all tests.
• Any uncaught exception will exit the test runner, hence won't commit.

Note: If you're catching exceptions anywhere, then database abstraction does not know that something has gone wrong hence you're responsible for the correct rollback of the transaction.

frappe.db.get_list('Employee')

# output
[{'name': 'HR-EMP-00008'},
 {'name': 'HR-EMP-00006'},
 {'name': 'HR-EMP-00010'},
 {'name': 'HR-EMP-00005'}
]

# with pluck
frappe.db.get_list('Employee', pluck='name')

# output
['HR-EMP-00008',
 'HR-EMP-00006',
 'HR-EMP-00010',
 'HR-EMP-00005'
]

frappe.db.get_list('Task',
    filters={
        'status': 'Open'
    },
    fields=['subject', 'date'],
    order_by='date desc',
    start=10,
    page_length=20,
    as_list=True
)

# output
(('Update Branding and Design', '2019-09-04'),
('Missing Documentation', '2019-09-02'),
('Fundraiser for Foundation', '2019-09-03'))

# Tasks with date after 2019-09-08
frappe.db.get_list('Task', filters={
    'date': ['>', '2019-09-08']
})

# Tasks with date between 2020-04-01 and 2021-03-31 (both inclusive)
frappe.db.get_list('Task', filters=[[
    'date', 'between', ['2020-04-01', '2021-03-31']
]])

# Tasks with subject that contains "test"
frappe.db.get_list('Task', filters={
    'subject': ['like', '%test%']
})

# Count number of tasks grouped by status
frappe.db.get_list('Task',
    fields=['count(name) as count', 'status'],
    group_by='status'
)
# output
[{'count': 1, 'status': 'Working'},
 {'count': 2, 'status': 'Overdue'},
 {'count': 2, 'status': 'Open'},
 {'count': 1, 'status': 'Filed'},
 {'count': 20, 'status': 'Completed'},
 {'count': 1, 'status': 'Cancelled'}]

# single value
subject = frappe.db.get_value('Task', 'TASK00002', 'subject')

# multiple values
subject, description = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'])

# as dict
task_dict = frappe.db.get_value('Task', 'TASK00002', ['subject', 'description'], as_dict=1)
task_dict.subject
task_dict.description

# with filters, will return the first record that matches filters
subject, description = frappe.db.get_value('Task', {'status': 'Open'}, ['subject', 'description'])

timezone = frappe.db.get_single_value('System Settings', 'timezone')

# update a field value
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject')

# update multiple values
frappe.db.set_value('Task', 'TASK00002', {
    'subject': 'New Subject',
    'description': 'New Description'
})

# update without updating the `modified` timestamp
frappe.db.set_value('Task', 'TASK00002', 'subject', 'New Subject', update_modified=False)

frappe.db.exists("User", "jane@example.org", cache=True)

frappe.db.exists({"doctype": "User", "full_name": "Jane Doe"})

frappe.db.exists("User", {"full_name": "Jane Doe"})

# total number of Task records
frappe.db.count('Task')

# total number of Open tasks
frappe.db.count('Task', {'status': 'Open'})

frappe.db.delete("Route History", {
    "modified": ("<=", last_record_to_keep[0].modified),
    "user": user
})

frappe.db.delete("Error Log")
frappe.db.delete("__Test Table")

frappe.db.truncate("Error Log")
frappe.db.truncate("__Test Table")

values = {'company': 'Frappe Technologies Inc'}
data = frappe.db.sql("""
    SELECT
        acc.account_number
        gl.debit
        gl.credit
    FROM `tabGL Entry` gl
        LEFT JOIN `tabAccount` acc
        ON gl.account = acc.name
    WHERE gl.company = %(company)s
""", values=values, as_dict=0)

frappe.db.rename_table("__internal_cache", "__temporary_cache")
frappe.db.rename_table("todo", "ToDo")

frappe.db.add_index("Notes", ["id(10)", "content(500)"], index_name)

frappe.db.add_unique("DoctypeName",["field1","field2"])

def create_file(self):
    self.write_file()
    # This ensures rollback if DB transaction is rolledback
    frappe.db.after_rollback.add(self.rollback_file)

def rollback_file(self):
    self.delete_file()