Database API

Edit Page New Page Revisions

dontmanage.db.get_list

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

  • Also aliased to dontmanage.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:

dontmanage.db.get_list('Employee')

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

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

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

Combining filters and other arguments:

dontmanage.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
dontmanage.db.get_list('Task', filters={
    'date': ['>', '2019-09-08']
})

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

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

# Count number of tasks grouped by status
dontmanage.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'}]

dontmanage.db.get_all

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

  • Also aliased to dontmanage.get_all

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

dontmanage.db.get_value

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

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

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

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

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

# as dict
task_dict = dontmanage.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 = dontmanage.db.get_value('Task', {'status': 'Open'}, ['subject', 'description'])

dontmanage.db.get_single_value

dontmanage.db.get_single_value(doctype, fieldname)

Returns a field value from a Single DocType.

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

dontmanage.db.set_value

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

  • Also aliased to dontmanage.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
dontmanage.db.set_value('Task', 'TASK00002', 'subject', 'New Subject')

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

# update without updating the `modified` timestamp
dontmanage.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.

dontmanage.db.exists

dontmanage.db.exists(doctype, name)

Returns true if a document record exists.

Pass doctype and docname:

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

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

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

Pass the doctype and a dict of filters:

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

dontmanage.db.count

dontmanage.db.count(doctype, filters)

Returns number of records for a given doctype and filters.

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

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

dontmanage.db.delete

dontmanage.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.

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

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

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

dontmanage.db.truncate

dontmanage.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.

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

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

dontmanage.db.commit

dontmanage.db.commit()

Commits current transaction. Calls SQL COMMIT.

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

dontmanage.db.savepoint

dontmanage.db.savepoint(save_point)

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

dontmanage.db.rollback

dontmanage.db.rollback()

Rollbacks current transaction. Calls SQL ROLLBACK.

DontManage will automatically run dontmanage.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.

dontmanage.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.

dontmanage.db.sql

dontmanage.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': 'DontManage Technologies Inc'}
data = dontmanage.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 dontmanage.get_doc, dontmanage.db.get_list, etc., if possible.

dontmanage.db.multisql

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

Execute the suitable SQL statement for any supported database engine.

dontmanage.db.rename_table

dontmanage.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:

dontmanage.db.rename_table("__internal_cache", "__temporary_cache")
dontmanage.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 dontmanage.rename_doc for that instead

dontmanage.db.describe

dontmanage.db.describe(doctype)

Returns a tuple of the table description for given DocType.

dontmanage.db.change_column_type

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

Changes the type of column for specified DocType.

dontmanage.db.add_index

dontmanage.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:

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

dontmanage.db.add_unique

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

Creates unique constraint for doctypes for the specified fields.

Example:

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


Database transaction hooks

Note: This API is introduced in v15.

DontManage 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 :

  • dontmanage.db.before_commit.add(func: Callable)

  • dontmanage.db.after_commit.add(func: Callable)

  • dontmanage.db.before_rollback.add(func: Callable)

  • dontmanage.db.after_rollback.add(func: Callable)

Example usage:

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

def rollback_file(self):
    self.delete_file()

Database transaction model

DontManage'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 dontmanage.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.

Previous Page

Left

Next Page

Right
On this page