Class SqlExcelQuery

The SqlExcelQuery class is used to execute an SQL query using the sql.excel.pdf template, which is required for the class to function.

This is a non-standard Odoo module provided by third-party applications and must be installed in order to use this class.

To see some usage examples you can look at the page Usage examples.

• Constructor
• Static method is_available
• Property name
• Property language
• Property id
• Method get_sql
• Method set_sql
• Method set_draft
• Method validate
• Method get_state
• Method get_active
• Method set_active
• Method execute
• Method clear
• Method delete
• Method get_file
• Method get_data
• Method add_tag
• Method get_tags

Constructor

class SqlExcelQuery(name: str,
                    category: str,
                    endpoint: str,
                    database: str,
                    username: str,
                    password: str,
                    language: str = None)

Parameters

• name: name of the SQL query to be created or used.
• category: name of the query category to be created or used.
• endpoint: URL of the Odoo endpoint.
• database: name of the Odoo database to use.
• username: name of the Odoo user to use.
• password: password of the Odoo user to use.
• language: default language to use for translatable fields.

Usage example

import pyodoo.v12

query = pyodoo.v12.SqlExcelQuery(name='SQL Excel Query Demo',
                                 category='PyOdoo Demo',
                                 endpoint='https://demo.odoo.com',
                                 database='demo_saas',
                                 username='muflone',
                                 password='mysecret',
                                 language='en_GB')

Access the Odoo instance at https://demo.odoo.com on the demo_saas database, using the credentials provided and the Italian language for the translatable fields, and call up or create the SQL query named SQL Excel Query Demo in the PyOdoo Demo category.

Static method is_available

SqlExcelQuery.is_available(endpoint: str,
                           database: str,
                           username: str,
                           password: str,
                           language: str = None)

The static method is_available is static and can be called on both SqlExcelQuery objects and on the class itself with SqlExcelQuery.is_available. It checks whether the sql.excel.pdf template is available and returns True if the template is found, otherwise it returns False.

Parameters

• endpoint: URL of the Odoo endpoint.
• database: name of the Odoo database to use.
• username: name of the Odoo user to use.
• password: password of the Odoo user to use.
• language: default language to use for translatable fields.

Returns

• True if the sql.excel.pdf model was found.
• False if the sql.excel.pdf model is not available.

Usage example

print(SqlExcelQuery.is_available(endpoint='https://demo.odoo.com',
                                 database='demo_saas',
                                 username='muflone',
                                 password='mysecret',
                                 language='it_IT')

Property name

The name property returns the name of the query of the SqlExcelQuery object.

Returns

• The name of the query used by the SqlExcelQuery object.

Usage example

print(query.name)

Property language

The language property returns and assigns the language for the SqlExcelQuery object.

Returns

• The current language from the SqlExcelQuery object.

Usage example

print(query.language)
query.language = 'it_IT'

Property id

The id property returns the unique identifier of the query of the SqlExcelQuery object.

Returns

• The unique identifier from the SqlExcelQuery object.

Usage example

print(query.id)

Method get_sql

SqlExcelQuery.get_code()

The get_sql method returns the SQL code of the SqlExcelQuery object.

Returns

• The SQL code for the query of the SqlExcelQuery object.

Usage example

print(query.get_sql())

Method set_sql

SqlExcelQuery.set_sql(text: str)

The set_sql method sets the SQL code for the SqlExcelQuery object.

The text parameter corresponds to the SQL code to be saved.

Parameters

• text: SQL code to be set.

Returns

• True if the record has been updated.

Usage example

query.set_sql(text='SELECT * FROM res_partner LIMIT 100')

Method set_draft

SqlExcelQuery.set_draft()

The set_draft method sets the SqlExcelQuery record to draft for editing.

Returns

• The method returns nothing.

Usage example

query.set_draft()

Method validate

SqlExcelQuery.validate()

The validate method validates the SQL code of the SqlExcelQuery object.

Returns

• The method returns nothing.

Usage example

query.validate()

Method get_state

SqlExcelQuery.get_state()

The get_state method reads the status of the SqlExcelQuery object.

Returns

• The status of the SqlExcelQuery object.

Usage example

query.set_draft()
print(query.get_state())
query.validate()
print(query.get_state())

Method get_active

SqlExcelQuery.get_active()

The get_active method reads the active status of the SqlExcelQuery object.

Returns

• The active status of the SqlExcelQuery object.

Usage example

print(query.get_active())

Method set_active

SqlExcelQuery.set_active(active: bool)

The set_active method sets the active status of the SqlExcelQuery object.

Returns

• True if the record has been updated.

Usage example

print(query.set_active(active=False))
print(query.set_active(active=True))

Method execute

SqlExcelQuery.execute()

The execute method executes the SQL query of the SqlExcelQuery object.

Returns

• The method returns nothing.

Usage example

query.set_sql(text='SELECT\n  *\nFROM res_partner\nLIMIT 100')
query.execute()

Method clear

SqlExcelQuery.clear()

The clear method clears the result of previous executions of the SqlExcelQuery object.

Returns

• True if the record has been updated.

Usage example

query.clear()

Method delete

SqlExcelQuery.delete()

The delete method deletes the record from the SqlExcelQuery object.

Returns

• True if the record has been deleted.

Usage example

query.delete()

Method get_file

SqlExcelQuery.get_file()

The get_file method retrieves the Excel file from the last execution of the SQL query of the SqlExcelQuery object.

Returns

• The binary content of the Excel file obtained.

Usage example

query.execute()
data = query.get_file()

Method get_data

SqlExcelQuery.get_data()

The get_data method extracts data from the Excel file of the last execution of the SQL query of the SqlExcelQuery object.

Returns

• A list of dictionaries with data extracted from the Excel file obtained.

Usage example

query.execute()
data = query.get_data()

Method add_tag

SqlExcelQuery.add_tag(tag_id: int)

The add_tag method adds the tag identified by tag_id to the SqlExcelQuery object.

Returns

• True if the record has been updated.

Usage example

query.add_tag(tag_id=4)

Method get_tags

SqlExcelQuery.get_tags()

The get_tags method retrieves all tags from the sql.tags model to be associated with the SqlExcelQuery object using the add_tag method.

Returns

• A list of dictionaries with tag data.

Usage example

print(query.get_tags())