Version 6.x.x Source

Class Database

Open a database connection with the given parmeters, if use_default is True, use the login details from conf.py. This is called by the request handler and is accessible using the db global variable. the sql method is also global to run queries

__init__ (self, host=None, user=None, password=None, ac_name=None, use_default=0)

No docs


_get_value_for_many_names (self, doctype, names, field, debug=False)

No docs


_get_values_from_table (self, fields, filters, doctype, as_dict, debug, order_by=None, update=None)

No docs


a_row_exists (self, doctype)

Returns True if atleast one row exists.


add_default (self, key, val, parent=__default, parenttype=None)

Append a default value for a key, there can be multiple default values for a particular key.


add_index (self, doctype, fields, index_name=None)

Creates an index with given fields if not already created. Index name will be fieldname1_fieldname2_index


add_unique (self, doctype, fields, constraint_name=None)

No docs


begin (self)

No docs


build_conditions (self, filters)

Convert filters sent as dict, lists to SQL conditions. filter's key is passed by map function, build conditions like:


check_transaction_status (self, query)

Raises exception if more than 20,000 INSERT, UPDATE queries are executed in one transaction. This is to ensure that writes are always flushed otherwise this could cause the system to hang.


close (self)

Close database connection.


commit (self)

Commit current transaction. Calls SQL COMMIT.


connect (self)

Connects to a database as set in site_config.json.


convert_to_lists (self, res, formatted=0, as_utf8=0)

Convert tuple output to lists (internal).


convert_to_simple_type (self, v, formatted=0)

Format date, time, longint values.


convert_to_utf8 (self, res, formatted=0)

Encode result as UTF-8.


count (self, dt, filters=None, debug=False)

Returns COUNT(*) for given DocType and filters.


escape (self, s, percent=True)

Excape quotes and percent in given string.


exists (self, dt, dn=None)

Returns true if document exists.

Parameters:


explain_query (self, query, values=None)

Print EXPLAIN in error log.


fetch_as_dict (self, formatted=0, as_utf8=0)

Internal. Converts results to dict.


field_exists (self, dt, fn)

Return true of field exists.


get (self, doctype, filters=None, as_dict=True, cache=False)

Returns get_value with fieldname='*'


get_all (self)

No docs


get_creation_count (self, doctype, minutes)

Get count of records created in the last x minutes


get_db_login (self, ac_name)

No docs


get_default (self, key, parent=__default)

Returns default value as a list if multiple or single


get_defaults (self, key=None, parent=__default)

Get all defaults


get_description (self)

Returns result metadata.


get_global (self, key, user=__global)

Returns a global key value.


get_list (self)

No docs


get_single_value (self, doctype, fieldname, cache=False)

Get property of Single DocType. Cache locally by default


get_singles_dict (self, doctype)

Get Single DocType as dict.


get_singles_value (self)

Alias for getsinglevalue


get_table_columns (self, doctype)

Returns list of column names from given doctype.


get_tables (self)

No docs


get_temp (self, key)

Return the temperory value and delete it.


get_value (self, doctype, filters=None, fieldname=name, ignore=None, as_dict=False, debug=False, cache=False)

Returns a document property or list of properties.

Parameters:

Example:

# return first customer starting with a
frappe.db.get_value("Customer", {"name": ("like a%")})

# return last login of **User** `test@example.com`
frappe.db.get_value("User", "test@example.com", "last_login")

last_login, last_ip = frappe.db.get_value("User", "test@example.com",
    ["last_login", "last_ip"])

# returns default date_format
frappe.db.get_value("System Settings", None, "date_format")

get_values (self, doctype, filters=None, fieldname=name, ignore=None, as_dict=False, debug=False, order_by=None, update=None, cache=False)

Returns multiple document properties.

Parameters:

Example:

# return first customer starting with a
customers = frappe.db.get_values("Customer", {"name": ("like a%")})

# return last login of **User** `test@example.com`
user = frappe.db.get_values("User", "test@example.com", "*")[0]

get_values_from_single (self, fields, filters, doctype, as_dict=False, debug=False, update=None)

Get values from tabSingles (Single DocTypes) (internal).

Parameters:


has_column (self, doctype, column)

Returns True if column exists in database.


needs_formatting (self, result, formatted)

Returns true if the first row in the result has a Date, Datetime, Long Int.


rollback (self)

ROLLBACK current transaction.


set (self, doc, field, val)

Set value in document. Avoid


set_default (self, key, val, parent=__default, parenttype=None)

Sets a global / user default value.


set_global (self, key, val, user=__global)

Save a global key value. Global values will be automatically set if they match fieldname.


set_temp (self, value)

Set a temperory value and return a key.


set_value (self, dt, dn, field, val, modified=None, modified_by=None, update_modified=True, debug=False)

Set a single value in the database, do not call the ORM triggers but update the modified timestamp (unless specified not to).

Warning: this function will not call Document events and should be avoided in normal cases.

Parameters:


sql (self, query, values=(), as_dict=0, as_list=0, formatted=0, debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None)

Execute a SQL query and fetch all rows.

Parameters:

Examples:

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

# return names beginning with a
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":"test@example.com"})

sql_ddl (self, query, values=(), debug=False)

Commit and execute a query. DDL (Data Definition Language) queries that alter schema autocommit in MariaDB.


sql_list (self, query, values=(), debug=False)

Return data as list of single elements (first column).

Example:

# doctypes = ["DocType", "DocField", "User", ...]
doctypes = frappe.db.sql_list("select name from DocType")

table_exists (self, tablename)

Returns True if table exists.


touch (self, doctype, docname)

Update the modified timestamp of this document.


update (self)

Update multiple values. Alias for set_value.


use (self, db_name)

USE db_name.


validate_query (self, q)

Throw exception for dangerous queries: ALTER, DROP, TRUNCATE if not Administrator.