¶ (c) Technocore - All Rights Reserved.
NOTICE: All information contained herein is, and remains
the property of TechnoCore.
The intellectual and technical concepts contained
herein are proprietary to TechnoCore and dissemination of this information or reproduction of this material
is strictly forbidden unless prior written permission is obtained
from TechnoCore.
¶ ObjDataSql
The ObjDataSql class provides a comprehensive set of methods for executing SQL queries and handling data retrieval from relational databases. It acts as a foundational class for SQL-based data operations within the Axion framework.
¶ Class Overview
ObjDataSql is designed to be a versatile and robust SQL interface. It abstracts away the complexities of database-specific SQL syntax and provides a consistent API for querying and data manipulation.
Key functionalities include:
- Executing SQL statements and handling transactions.
- Retrieving data in various formats (single value, list, dictionary, DataFrame).
- Handling database connection checks and reconnections.
- Parsing and extracting information from SQL queries.
- Comparing table schemas and data.
¶ Methods
¶ sql_format(sql)
Formats an SQL query string for better readability.
sql(str): The SQL query to format.
¶ sql_status()
Retrieves the status of the database server and returns it as a JSON string.
¶ _sanitise_sql(sql) (static)
Strips trailing semicolons from SQL before execution. Applied at
entry point of sql_execute, sql_get_value, sql_get_tuple_set,
and sql_get_dictionary_list. Prevents near '' syntax errors
from stray semicolons in calculation SQL.
¶ sql_execute(sql, alt_db)
Executes one or more SQL statements. It handles compound queries,
commits, and specific errors like duplicate entries. On error,
returns (False, 0) instead of calling sys.exit().
sql(str): The SQL statement(s) to execute.alt_db(DatabaseConnection): An optional alternative database connection.
Returns a tuple (success, return_code).
¶ sql_execute_many(sql, data, alt_db)
Executes a multi-row INSERT or UPDATE statement efficiently using executemany.
sql(str): The SQL statement with placeholders.data(list): A list of tuples or dictionaries containing the data to be inserted/updated.alt_db(DatabaseConnection): An optional alternative database connection.
¶ sql_compare_tables(table1, table2)
Compares the schema and data of two tables to check if they are identical.
table1(str): The name of the first table.table2(str): The name of the second table.
Returns True if the tables are identical, False otherwise.
¶ Data Retrieval Methods
ObjDataSql provides a rich set of methods for retrieving data in different formats:
sql_get_value(sql, alt_db): Retrieves a single value from a query.sql_get_int(sql, alt_db): Retrieves a single integer value.sql_get_values(sql, alt_db): Retrieves a list of values from the first column of the query result.sql_get_dict(sql, alt_db): Retrieves a single row as a dictionary.sql_get_dictionary_list(sql, alt_db): Retrieves all rows as a list of dictionaries.sql_get_list(sql, alt_db): Retrieves all rows as a list of tuples.sql_get_tuple(sql, alt_db): Retrieves a single row as a tuple.sql_get_tuple_set(sql, alt_db): Retrieves all rows as a list of tuples.sql_get_dataframe(sql, alt_db): Retrieves the query result as a pandas DataFrame.sql_get_array(sql, alt_db): Retrieves all rows as a list of tuples.sql_get_array_and_headings(sql, alt_db): Returns a tuple containing the data as a list of tuples and a list of column headings.sql_get_headings(sql, alt_db): Retrieves only the column headings for a query.sql_get_headings_full(sql, alt_db): Retrieves detailed information about each column, including data type and nullability.
¶ explain_sql(sql, alt_db)
Runs EXPLAIN on a SELECT, UPDATE, or DELETE
statement and returns index usage information.
MySQL/MariaDB only — returns a safe default for
other engines.
sql(str): The SQL statement to analyse.alt_db(DatabaseConnectionType): Optional
alternative database connection.
Returns a dict:
| Key | Type | Description |
|---|---|---|
using_key |
bool | True if any index is used |
key |
str | Index name from the first plan row |
rows |
int | Total estimated rows across all plan rows |
type |
str | Access type (ALL, index, ref, etc.) |
extra |
str | Extra info from the first plan row |
warning |
str | Human-readable warning when a full table scan is detected |
plans |
list | Raw EXPLAIN rows as dicts |
Skips non-DML statements (INSERT, CREATE, etc.)
and returns {"using_key": True, "warning": ""}
so callers can treat them as safe. On error the
warning field contains the exception message and
using_key is set to True to avoid false
alarms.
Used by ObjCalculation in DEV mode to populate
ExplainKey, ExplainWarning, and ExplainRows
columns in track_calculations.
¶ Test Coverage
The ObjDataSql module has comprehensive test coverage for SQL operations and caching functionality.
¶ Test Summary
| Test Suite | Tests | Type | Purpose |
|---|---|---|---|
test_ObjDataSql.py::TestBufferCache |
6 | Unit | Buffer cache storage, TTL expiration, LRU eviction, thread safety |
test_ObjDataSql.py::TestCacheStatistics |
5 | Unit | Cache hit/miss tracking, statistics calculation, manual cache clearing |
test_ObjDataSql.py::TestSQLGetValue |
4 | Unit | SQL value retrieval, multi-statement execution, error handling |
test_ObjDataSql.py::TestRetryLogic |
4 | Unit | Transient failure recovery, retry exhaustion, non-transient errors |
test_ObjDataSql.py::TestPerformanceLogging |
3 | Unit | Slow query detection, performance timing, logging controls |
test_ObjDataSql.py::TestDeprecatedMethods |
2 | Unit | Deprecation warnings, backward compatibility |
test_ObjDataSql.py::TestAllSQLMethods |
29 | Integration | Real database operations for all SQL retrieval methods |
| Total | 53 |
¶ Running Tests
# Run all ObjDataSql tests
pytest resource.test/pytests/factory.core/test_ObjDataSql.py -v
¶ Test Features
- Buffer Caching: Tests TTL-based expiration (600s), LRU eviction, and thread-safe operations
- Cache Statistics: Validates hit/miss tracking, hit rate calculation, and manual cache management
- SQL Operations: Tests single/multi-statement execution, empty results, and error handling
- Retry Logic: Verifies automatic retry on transient failures with configurable backoff
- Performance Monitoring: Tests slow query logging (>1s) when DO_QUERY_TIMING enabled
- Integration Tests: Real database tests covering all 20+ SQL retrieval methods
- Fast Execution: All 53 tests complete in ~8.5 minutes (includes database operations)