Skip to content

sqlToTable

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

sqlToTable(
    databaseUrl,
    query,
    dataTypes=dict()
)

Duplicate column names

If there are multiple columns with the same name, only the left-most column is kept. Column names are case-insensitive. For more info, see the page about column names.

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. See SQL queries for details.
dataTypes

A mapping of column names to data type specifiers. See data types for details.

For columns not given in dataTypes, the data types returned by the SQL query will be used, and a default number format is chosen for each data type. However, not all SQL databases support all data types. A notable example is the lack of date/time and boolean support in SQLite. You may also want to override the data type of a column. In these cases, you can use this argument to explicitly specify the data types of the columns.

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"
)

Note

SQLite has some oddities compared to other SQL databases, most notably with regard to data types and query syntax.

Example workbook

See the example workbook.