`sqlToTable`¶

Load data from a SQL source and return the table name.

sqlToTable(
    databaseUrl,
    query
)

Arguments¶

databaseUrl: A string describing the database to connect to. This is more fully documented below.
query: A SQL query string to send to the database.

Database URLs¶

Database URLs typically take this form:

protocol://username:password@hostname:port/databasename

The username, password and port are sometimes optional, depending on the type of database.

When the database requires a password, but it is not given in the URL, Autory will ask for the password when running the model. It can optionally store the password securely.

Secure credential storage¶

When Autory prompts for a username and/or password, they are stored using Python's keyring package. On Windows machines, this will cause the credentials to be stored in Windows Credential Locker.

Note

For best security, do not include your database passwords in the database URL.

Examples¶

MySQL or MariaDB¶

MySQL and MariaDB are hosted databases, which run on a server somewhere. These databases are normally password protected, with fine-grained access controls per user.

Here are some examples of database URLs:

mysql://username:password@hostname:port/databasename
mysql://username:password@127.0.0.1:port/databasename (Using IP address instead of host name.)
mysql://username:password@hostname/databasename (The default port is 3306.)
mysql://username@hostname/databasename (Autory will prompt for the password.)
mysql://hostname/databasename (Autory will prompt for the username and the password.)

To run a query on a database called policies on server1 using user bob, do this:

sqlToTable(
    "mysql://bob@server1/policies",
    "SELECT column1 FROM table1 WHERE column2 > 3"
)

SQLite¶

SQLite databases are stored in files, and have no access control. If you can access the file, you can access the database. Each file contains exactly one database. The database URL is always very simple:

sqlite:///relative/path/to/file
sqlite:///relative\path\to\file
sqlite:///C:/absolute/path/to/file
sqlite:///C:\absolute\path\to\file

Note

Note the three slashes ///. Since username:password@hostname:port in protocol://username:password@hostname:port/databasename is an empty string, you end up with protocol:///databasename.

To run a query on a database stored at C:\data\db.sqlite, do this:

sqlToTable(
    "sqlite:///C:/data/db.sqlite",
    "SELECT column1 FROM table1 WHERE column2 > 3"
)

sqlToTable¶