extools.view.exsql

The ExSql view performs SQL queries directly against the database. It can be used to perform high performance reads or writes that are otherwise not allowed by Sage’s validation (caveat emptor).

class extools.view.exsql.ExSql

A class for working with the CS0120 view.

CSQL_VIEWID = 'CS0120'

get(field, _type=-1, size=-1, precision=-1)

Get a field from view.

Overrides the default get to skip verification that the field exists.

query(query)

Perform an SQL query and return the view.

Parameters:query (str) – an SQL query to execute. Returns:view with the first result fetched. Return type:ExView Raises:extools.view.errors.ExSqlError, extools.view.errors.ExViewError

If you only need to execute a query, consider using a context manager like extools.view.exsql.exsql() or extools.view.exsql.exsql_result().

try:
    exs = ExSql()
    result = exs.query("SELECT ITEM FROM OEORDD WHERE "
                       "ORDUNIQ = {} AND LINENUM = {}".format(
                            234634, 2))
    if exs.fetch():
        item = result.get("ITEM")
    else:
        # Handle record doesn't exist
except ExSqlError as e:
    # Handle an SQL fail
except ExViewError as e:
    # Handle a view layer fail

query_results(query)

Perform a query and yield the resulting records one at a time.

Parameters:query (str) – an SQL query to execute. Yields:ExView Returns:None Raises:extools.view.errors.ExSqlError, extools.view.errors.ExViewError

classmethod record_count(table)

Get the total number of records from a table.

Parameters:table – name of the table to count records in. Returns:record count Return type:int Raises:extools.view.errors.ExSqlError, extools.view.errors.ExViewError

extools.view.exsql.columns_for_table(table)

extools.view.exsql.exsql()

Open an ExSql view and yield it.

Yields:ExSql Return type:None Raises:ExSqlError, ExViewError

try:
    with exsql() as exs:
        exs.query("SELECT ITEM FROM OEORDD WHERE "
                  "ORDUNIQ = {} AND LINENUM = {}".format(
                        234634, 2))
        exs.fetch()
        item = exs.get("ITEM")
except ExSqlError as e:
    # Handle an SQL fail
except ExViewError as e:
    # Handle a view layer fail

extools.view.exsql.exsql_result(query)

Open an ExSql view, executes a query, and yield the results.

Parameters:query (str) – SQL query to execute. Yields:ExSql Return type:None Raises:extools.view.errors.ExSqlError, extools.view.errors.ExViewError

query = ("SELECT ITEM FROM OEORDD WHERE "
         "ORDUNIQ = {} AND LINENUM = {}".format(
                234634, 2))
try:
    with exsql_result(query) as res:
        item = res.get("ITEM")
except ExSqlError as e:
    # Handle an SQL fail
except ExViewError as e:
    # Handle a view layer fail

extools.view.exsql.insert_optional_field(table, keys, user, org, field, value)

extools.view.exsql.sql_escape(term)

Escape an SQL string for TSQL server (double quoted).

The following terms are escaped:

• \ -> \
• ' -> ''
• " -> "
• / -> \/

Parameters:term (str) – the string to escape Returns:TSQL escaped string Return type:str

query = "SELECT * from TABLE WHERE SHOW = '{showname}'"
showname = sql_escape("Bob's Burgers") # -> "Bob''s Burgers"
try:
    with exsql_result(query.format(showname=showname)) as result:
        return result.get("NETWORK")
except ExViewRecordDoesNotExist:
    showMessageBox("No such show!")
except ExSqlError:
    showMessageBox("Table or field do not exist.")
except ExViewError:
    showMessageBox("An error occurred in the view.")

extools.view.exsql.update_optional_field(table, keys, user, org, field, value)