Created by Shared Doc User on Feb 13, 2023
This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.

Generic access to databases

PEP 249

Simple database queries consistent with the Python Database API Specification (PEP 249) are recognized. This allows to support a large number of important libraries interfacing Python and SQL databases (SQLite, MySQL, etc). The analyzer identifies execute method calls as potential database queries and searches for generic SQL statements passed in as an argument ('SELECT ...", "INSERT ...)". In the example below data from the stocks table is retrieved via a SELECT statement passed explicitly by a string to the execute method of a cursor object.

# query.py
import sqlite3
 
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('SELECT * FROM informations') 

In addition to execute method calls, the analyzer identifies raw method calls which are used in Django framework. SQL queries can be defined directly or via a method.

from django.db import models
...
def function(self):
    sql = 'SELECT * FROM informations'
    return model.objects.raw(sql)

The analyzer creates a Python Query object with name SELECT * FROM informations (first 4 words are only used as naming convention) representing a call to a database. Provided analysis dependencies between SQL and Python are configured in CAST Management Studio, the analyzer will automatically link this object to the corresponding Table object, in this case informations, that has been generated by a SQL analysis unit.

In some cases SQL queries can be defined via SQL files.

def function(self):
    file_path = "db_queries.sql"
    sql = open(file_path).read()
    cursor.execute(sql)

where the file db_queries.sql contains SQL code that is analyzed independently by the sqlanalyzer extension.

CREATE TABLE IF NOT EXISTS informations;
SELECT * FROM informations

In this situation, the analyzer will create a Python File Query object with the name of the sql file. This object will make the link between the method containing the query and the SQL script (if it is present, and dependencies between SQL and Python are configured as previously mentioned), so that the end point of the transaction (for example, a table) can be reached.

Only files containg '.sql' extensions are supported.

SQLAlchemy

SQLAlchemy is a Python SQL toolkit providing a way of interaction with databases. SQLAlchemy includes both a database server independent SQL expression language and an Object Relational Mapper (ORM). An ORM presents a method of associating user-defined Python classes with database tables and instances of the classes(objects) with rows in their corresponding tables. The analyzer identifies query method calls in addition to execute method calls.

Example using query method call:

class UserTable:
    __tablename__ = "users"
     
    def __init__(self):
        pass
 
class User(UserTable):
    __tablename__ = "users_table"
 
    def __init__(self):
        UserTable.__init__()
         
    def f(self):
        query = UserTable.query().filter(UserTable.name == "new_user") #query().filter(...) is equivalent to SELECT statement

Example using execute method call:

class Information:
    __tablename__ = "informations"
 
    def find_information(self):
        informations_table = Information.__table__
        select_query = (
            informations_table.filter(informations_table.id == target.information_id)
            )
        connection.execute(select_query)

In this example the analyzer creates a Python ORM Mapping object with the name of the table designated by __tablename__ in class. As in the case of creation of Python Query objects, it is assumed that analysis dependencies between SQL and Python are correctly configured in CAST Management Studio. Then, links between these objects and the corresponding Table objects (in this example informations, generated by a SQL analysis unit) will automatically be created by the analyzer. The type of the link in this particular case is useSelectLink (Us) because of the filter() method call present in the query expression.