Access to databases

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{style=“text-decoration: none;” rel=“nofollow”} 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.