Reference

Structure of the .connections.yaml file.

The structure of the .connections.yaml file underpins the ConnectionManager class. It defines the connections to one or more databases per connection mode (production, development or testing).

Each connection has a name, driver and connection. Other options exist as well, such as default and url_escape. Example:

- name: mysql-database
  driver: mysql+pymysql://
  connection: user:password@mysql:3306/testdatabase

The driver and the connection options are simply the full connection string. The are split up so the connection section can be url escaped if required.

Place the development connections and testing connections under the headings dev-connections and test-connections respectively.

The default=true option in a connection means this is used when no con_name is specified in either the execute_sql or recordset methods are used.

Here is an example of a connections file that define a "main" connection to a Microsoft SQL Server database, with a "cache" connection to a SQLite database, with production, development and testing setups:

connections:
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-server>
    url_escape: true
    default: true

  - name: my-sqlite-database
    driver: sqlite:///
    connection: databases/my-database.db

dev-connections:
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-dev-server>
    url_escape: true    
    default: true

  - name: my-sqlite-database
    driver: sqlite:///
    connection: /tmp/my-dev-database.db

test-connections:
  - name: my-sql-server-database
    driver: mssql+pyodbc:///?odbc_connect=
    connection: DRIVER={SQL Server};UID=<username>;PWD=<password>;SERVER=<my-test-server>
    url_escape: true
    default: true

  - name: my-sqlite-database
    driver: sqlite:///
    connection: /tmp/my-test-database.db

ConnectionManager

Intialising Options

You create a ConnectionManager instance referencing the connections to be used in one of three ways:

1 - Reference the connection file relatively or explicitly, for example:

cm = ConnectionManager(".connections.yaml")

or

cm = ConnectionManager("~/connections/.connections.yaml")

2 - Load from a default location. If no parameter is specified, SimQLe will look for the file first in the root of the project, and then in the home directory:

cm = ConnectionManager()

3 - Load from a dict. If you don't need the benefits of storing the connections in a file (say you're just using a temporary SQLite database), then you can pass a dict that mirrors the structure of the yaml file. For example:

connections_dict = {
    "connections": [
        {"name": "my-database",
         "driver": "sqlite:///",
         "connection": "my-database.db"}
    ]
}

cm = ConnectionManager(connections_dict)

Executing SQL

Once the connection manager is initialised with connections, execute_sql is the method that will execute SQL commands on a given connection:

cm = ConnectionManager()

cm.execute_sql(con_name="main", sql=sql, params=params)

Where "main" is the name of the desired connection, sql has the sql statement to execute, and params is a dict with the named parameters (if any). params can be ignored if no named parameters exist.

Returning Data

Recordset

To return data with multiple records, use the recordset method:

cm = ConnectionManager()

result = cm.recordset(con_name="main", sql=sql, params=params)

Which has the methods:

>>> result.headings
("heading 1", "heading 2")

>>> result.data
[("something 1", 1), ("something 2", 2)

>>> result.as_dict()
[{"heading 1": "something 1", "heading 2": 1},
 {"heading 1": "something 2", "heading 2": 2}]

>>> result.column("heading 1")
["something 1", "something 2"]

Record

Single records can be conveniently returned with cm.record. Extra rows are discarded.

>>> result = cm.record(con_name="main", sql=sql, params=params)
>>> result.data
("something 1", 1)

>>> result.as_dict
{"heading 1": "something 1", "heading 2": 1},

>>> bool(result)  # if the record exists
True

RecordScalar

Single datapoints can be conveniently returned with cm.record_scalar. Extra rows and columns are discarded.

>>> result = cm.record_scalar(con_name="main", sql=sql, params=params)`
>>> result.datum
"something 1"

>>> bool(result)  # the truthiness of the datapoint
True

>>> result.sdatum("bar")  # a safe datum that returns "bar" if the record doesn't exist