Database connectivity

class datarobot.DataDriver

A data driver.

Variables:

  • id (str) – The ID of the driver.

  • class_name (str) – The Java class name for the driver.

  • canonical_name (str) – The user-friendly name of the driver.

  • creator (str) – The ID of the user who created the driver.

  • base_names (List[str]) – A list of the filenames of the jar files.

classmethod list(typ=None)

Returns a list of available drivers.

Parameters:

typ (DataDriverListTypes) – If specified, filters by the specified driver type.

Returns:

drivers – Contains a list of available drivers.

Return type:

list of DataDriver instances

Examples

>>> import datarobot as dr
>>> drivers = dr.DataDriver.list()
>>> drivers
[DataDriver('mysql'), DataDriver('RedShift'), DataDriver('PostgreSQL')]
classmethod get(driver_id)

Gets the driver.

Parameters:

driver_id (str) – The identifier of the driver.

Returns:

driver – The required driver.

Return type:

DataDriver

Examples

>>> import datarobot as dr
>>> driver = dr.DataDriver.get('5ad08a1889453d0001ea7c5c')
>>> driver
DataDriver('PostgreSQL')
classmethod create(class_name, canonical_name, files=None, typ=None, database_driver=None)

Creates the driver. Only available to admin users.

Parameters:

  • class_name (str) – The Java class name for the driver. Specify None if typ is DataDriverTypes.DR_DATABASE_V1.

  • canonical_name (str) – The user-friendly name of the driver.

  • files (List[str]) – A list of file paths on the file system for the driver.

  • typ (str) – Optional. Specifies the type of the driver. Defaults to DataDriverTypes.JDBC. May also be DataDriverTypes.DR_DATABASE_V1.

  • database_driver (str) – Optional. Specify when typ is DataDriverTypes.DR_DATABASE_V1 to create a native database driver. See the DrDatabaseV1Types enumeration for some of the types, but that list may not be exhaustive.

Returns:

driver – The created driver.

Return type:

DataDriver

Raises:

ClientError – Raised if the user is not granted the Can manage JDBC database drivers feature.

Examples

>>> import datarobot as dr
>>> driver = dr.DataDriver.create(
...     class_name='org.postgresql.Driver',
...     canonical_name='PostgreSQL',
...     files=['/tmp/postgresql-42.2.2.jar']
... )
>>> driver
DataDriver('PostgreSQL')
update(class_name=None, canonical_name=None)

Updates the driver. Only available to admin users.

Parameters:

  • class_name (str) – The Java class name for the driver.

  • canonical_name (str) – The user-friendly name of the driver.

Raises:

ClientError – Raised if the user is not granted the Can manage JDBC database drivers feature.

Return type:

None

Examples

>>> import datarobot as dr
>>> driver = dr.DataDriver.get('5ad08a1889453d0001ea7c5c')
>>> driver.canonical_name
'PostgreSQL'
>>> driver.update(canonical_name='postgres')
>>> driver.canonical_name
'postgres'
delete()

Removes the driver. Only available to admin users.

Raises:

ClientError – Raised if the user is not granted the Can manage JDBC database drivers feature.

Return type:

None

class datarobot.Connector

A connector.

Variables:

  • id (str) – The ID of the connector.

  • creator_id (str) – The ID of the user who created the connector.

  • base_name (str) – The filename of the jar file.

  • canonical_name (str) – The user-friendly name of the connector.

  • configuration_id (str) – The ID of the configuration of the connector.

classmethod list(data_type=None)

Returns a list of available connectors.

Parameters:

data_type (DataTypes) – If specified, returns the connectors that support the specified data type. If not specified, defaults to DataTypes.ALL.

Returns:

connectors – Contains a list of available connectors.

Return type:

list of Connector instances

Examples

>>> import datarobot as dr
>>> connectors = dr.Connector.list()
>>> connectors
[Connector('Google Drive'), Connector('S3')]
classmethod get(connector_id)

Gets the connector.

Parameters:

connector_id (str) – The identifier of the connector.

Returns:

connector – The required connector.

Return type:

Connector

Examples

>>> import datarobot as dr
>>> connector = dr.Connector.get('5fe1063e1c075e0245071446')
>>> connector
Connector('Google Drive')
classmethod create(*, connector_type)

Creates the connector from a jar file. Only available to administrator users.

Parameters:

connector_type (str) – The type of the native connector to create.

Returns:

connector – The created connector.

Return type:

Connector

Raises:

ClientError – Raised if the user is not granted the Can manage connectors feature.

Examples

>>> import datarobot as dr
>>> connector = dr.Connector.create(connector_type='gdrive')
>>> connector
Connector('Google Drive')
delete()

Removes the connector. Only available to administrator users.

Raises:

ClientError – Raised if the user is not granted the Can manage connectors feature.

Return type:

None

class datarobot.DataStore

A data store. Represents a database.

Variables:

  • id (str) – The ID of the data store.

  • data_store_type (str) – The data store type.

  • canonical_name (str) – The user-friendly name of the data store.

  • creator (str) – The ID of the user who created the data store.

  • updated (datetime.datetime) – The time of the last update.

  • params (DataStoreParameters) – The data store parameters.

  • role (str) – Your access role for this data store.

classmethod list(typ=None, name=None, substitute_url_parameters=False, data_type=None)

Returns a list of available data stores.

Parameters:

  • typ (str) – If specified, filters by the specified data store type. If not specified, the default is DataStoreListTypes.JDBC.

  • name (str) – If specified, filters by data store names that match or contain this name. The search is case-insensitive.

  • substitute_url_parameters (bool) – If specified, substitutes dynamic parameters in the URL.

  • data_type (DataTypes) – If specified, filters data stores that support the specified data type. If not specified, defaults to DataTypes.ALL.

Returns:

data_stores – Contains a list of available data stores.

Return type:

list of DataStore instances

Examples

>>> import datarobot as dr
>>> data_stores = dr.DataStore.list()
>>> data_stores
[DataStore('Demo'), DataStore('Airlines')]
classmethod get(data_store_id, substitute_url_parameters=False)

Returns the data store.

Parameters:

  • data_store_id (str) – The identifier of the data store.

  • substitute_url_parameters (bool) – If specified, substitutes dynamic parameters in the URL.

Returns:

data_store – The required data store.

Return type:

DataStore

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5a8ac90b07a57a0001be501e')
>>> data_store
DataStore('Demo')
classmethod create(data_store_type, canonical_name, driver_id=None, jdbc_url=None, fields=None, connector_id=None)

Creates the data store.

Parameters:

  • data_store_type (str or DataStoreTypes) – The data store type.

  • canonical_name (str) – The user-friendly name of the data store.

  • driver_id (str) – Optional. The identifier of the DataDriver when data_store_type is DataStoreListTypes.JDBC or DataStoreListTypes.DR_DATABASE_V1.

  • jdbc_url (str) – Optional. The full JDBC URL (for example: jdbc:postgresql://my.dbaddress.org:5432/my_db).

  • fields (list) – Optional. If the type is dr-database-v1, then the fields specify the configuration.

  • connector_id (str) – Optional. The identifier of the Connector when data_store_type is DataStoreListTypes.DR_CONNECTOR_V1.

Returns:

data_store – The created data store.

Return type:

DataStore

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.create(
...     data_store_type='jdbc',
...     canonical_name='Demo DB',
...     driver_id='5a6af02eb15372000117c040',
...     jdbc_url='jdbc:postgresql://my.db.address.org:5432/perftest'
... )
>>> data_store
DataStore('Demo DB')
update(canonical_name=None, driver_id=None, connector_id=None, jdbc_url=None, fields=None)

Updates the data store.

Parameters:

  • canonical_name (str) – Optional; the user-friendly name of the data store.

  • driver_id (str) – Optional. The identifier of the DataDriver. if the type is one of DataStoreTypes.DR_DATABASE_V1 or DataStoreTypes.JDBC.

  • connector_id (str) – Optional. The identifier of the Connector. if the type is DataStoreTypes.DR_CONNECTOR_V1.

  • jdbc_url (str) – Optional. The full JDBC URL (for example: jdbc:postgresql://my.dbaddress.org:5432/my_db).

  • fields (list) – Optional. If the type is dr-database-v1, then the fields specify the configuration.

Return type:

None

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5ad5d2afef5cd700014d3cae')
>>> data_store
DataStore('Demo DB')
>>> data_store.update(canonical_name='Demo DB updated')
>>> data_store
DataStore('Demo DB updated')
delete()

Removes the DataStore

Return type:

None

test(username=None, password=None, credential_id=None, use_kerberos=None, credential_data=None, set_default_credential=False)

Tests database connection.

Changed in version v3.2: Added credential_id, use_kerberos, and credential_data optional parameters and made username and password optional.

Changed in version v3.9: When you provide credential_id and set set_default_credential to True and the connection test succeeds, DataRobot sets the credential as the default for this data store.

Parameters:

  • username (str) – Optional. The username for database authentication.

  • password (str) – Optional. The password for database authentication. The server encrypts the password during the request and never saves or stores it.

  • credential_id (str) – Optional. The ID of the credentials to use instead of username and password.

  • use_kerberos (bool) – Optional. Whether to use Kerberos for data store authentication.

  • credential_data (dict) – Optional. The credentials to authenticate with the database, to use instead of username/password or credential ID.

  • set_default_credential (bool) – Optional. If True and you provide credential_id, sets the credential as the default for this data store. Defaults to False.

Returns:

message – Message with status.

Return type:

dict

Raises:

CredentialsError – If unable to set the provided credential_id as default for this data store.

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5ad5d2afef5cd700014d3cae')
>>> data_store.test(username='db_username', password='db_password')
{'message': 'Connection successful'}
schemas(username, password)

Returns a list of available schemas.

Parameters:

  • username (str) – The username for database authentication.

  • password (str) – The password for database authentication. The server encrypts the password during the request and never saves or stores it.

Returns:

response – A dictionary with the database name and a list of available schemas.

Return type:

dict

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5ad5d2afef5cd700014d3cae')
>>> data_store.schemas(username='db_username', password='db_password')
{'catalog': 'perftest', 'schemas': ['demo', 'information_schema', 'public']}
tables(username, password, schema=None)

Returns a list of available tables in a schema.

Parameters:

  • username (str) – Optional. The username for database authentication.

  • password (str) – Optional. The password for database authentication. The server encrypts the password during the request and never saves or stores it.

  • schema (str) – Optional. The schema name.

Returns:

response – A dictionary with the catalog name and table information.

Return type:

dict

Examples

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5ad5d2afef5cd700014d3cae')
>>> data_store.tables(username='db_username', password='db_password', schema='demo')
{'tables': [{'type': 'TABLE', 'name': 'diagnosis', 'schema': 'demo'}, {'type': 'TABLE',
'name': 'kickcars', 'schema': 'demo'}, {'type': 'TABLE', 'name': 'patient',
'schema': 'demo'}, {'type': 'TABLE', 'name': 'transcript', 'schema': 'demo'}],
'catalog': 'perftest'}
classmethod from_server_data(data, keep_attrs=None)

Instantiate an object of this class using the data directly from the server, meaning that the keys may have the wrong camel casing

Parameters:

  • data (dict) – The directly translated dict of JSON from the server. DataRobot has not applied casing fixes yet.

  • keep_attrs (iterable) – A list, set, or tuple of the dotted namespace notations for attributes to keep within the object structure even if their values are None.

Return type:

DataStore

get_shared_roles()

Retrieve what users have access to this data store

Added in version v3.2.

Return type:

list of SharingRole

share(access_list)

Modify the ability of users to access this data store

Added in version v2.14.

Parameters:

access_list (list of SharingRole) – The modifications to make.

Return type:

None

Raises:

datarobot.ClientError : – if you do not have permission to share this data store, if the user you’re sharing with doesn’t exist, if the same user appears multiple times in the access_list, or if these changes would leave the data store without an owner.

Examples

The SharingRole class is needed in order to share a Data Store with one or more users.

For example, suppose you had a list of user IDs you wanted to share this DataStore with. You could use a loop to generate a list of SharingRole objects for them, and bulk share this Data Store.

>>> import datarobot as dr
>>> from datarobot.models.sharing import SharingRole
>>> from datarobot.enums import SHARING_ROLE, SHARING_RECIPIENT_TYPE
>>>
>>> user_ids = ["60912e09fd1f04e832a575c1", "639ce542862e9b1b1bfa8f1b", "63e185e7cd3a5f8e190c6393"]
>>> sharing_roles = []
>>> for user_id in user_ids:
...     new_sharing_role = SharingRole(
...         role=SHARING_ROLE.CONSUMER,
...         share_recipient_type=SHARING_RECIPIENT_TYPE.USER,
...         id=user_id,
...         can_share=True,
...     )
...     sharing_roles.append(new_sharing_role)
>>> dr.DataStore.get('my-data-store-id').share(access_list)

Similarly, a SharingRole instance can be used to remove a user’s access if the role is set to SHARING_ROLE.NO_ROLE, like in this example:

>>> import datarobot as dr
>>> from datarobot.models.sharing import SharingRole
>>> from datarobot.enums import SHARING_ROLE, SHARING_RECIPIENT_TYPE
>>>
>>> user_to_remove = "foo.bar@datarobot.com"
... remove_sharing_role = SharingRole(
...     role=SHARING_ROLE.NO_ROLE,
...     share_recipient_type=SHARING_RECIPIENT_TYPE.USER,
...     username=user_to_remove,
...     can_share=False,
... )
>>> dr.DataStore.get('my-data-store-id').share(roles=[remove_sharing_role])
preview_table(table_name, *, max_rows=100, catalog=None, schema=None, credential_id=None, use_kerberos=None)

Preview data from a table in the data store.

Parameters:

  • table_name (str) – Name of the table to preview.

  • max_rows (Optional[int]) – Maximum number of rows to preview.

  • catalog (Optional[str]) – Catalog of the table to preview.

  • schema (Optional[str]) – Schema of the table to preview.

  • credential_id (Optional[str]) – ID of the credential to use instead of default credentials.

  • use_kerberos (Optional[bool]) – Whether to use Kerberos for authentication.

Returns:

Object with preview data and result schema.

Return type:

JdbcPreviewData

Examples

>>> from datarobot.models.data_store import DataStore
>>> data_store = DataStore.get("my_data_store_id")
>>> credential_id = "my_credential_id"
>>> preview = data_store.preview_table(
...     "my_table_name",
...     credential_id=credential_id,
...     schema="my_schema",
...     catalog="my_catalog",
...     max_rows=10,
... )
>>> preview.columns
['id', 'name', 'email']
>>> preview.records
[
    {'id': 1, 'name': 'John Doe', 'email': 'john.doe@example.com'},
    {'id': 2, 'name': 'Jane Doe', 'email': 'jane.doe@example.com'},
]
>>> preview.df.head()
    id  name  email
0   1   John  john.doe@example.com
1   2   Jane  jane.doe@example.com
preview_query(sql, *, max_rows=100, credential_id=None, bind_parameters=None)

Execute a SQL query statement against a data store and return a preview of the results.

Parameters:

  • sql (str) – The SQL query statement to execute.

  • max_rows (Optional[int]) – The maximum number of rows to return.

  • credential_id (Optional[str]) – The ID of the credential to use. If not provided, the default credential will be used.

  • bind_parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – List of values to bind to the SQL statement. Each value is bound to a ? placeholder in the SQL statement. Binding is in-order.

Returns:

Object with preview data and result schema.

Return type:

JdbcPreviewData

Examples

>>> from datarobot.models.data_store import DataStore
>>> data_store = DataStore.get("my_data_store_id")
>>> preview = data_store.preview_query(
...     "SELECT * FROM my_catalog.my_schema.my_table WHERE name LIKE ?",
...     credential_id="my_credential_id",
...     max_rows=10,
...     bind_parameters=['%Doe%'],
... )
>>> preview.columns
['id', 'name', 'email']
>>> preview.records
[
    {'id': 1, 'name': 'John Doe', 'email': 'john.doe@example.com'},
    {'id': 2, 'name': 'Jane Doe', 'email': 'jane.doe@example.com'},
]
>>> preview.df.head()
    id  name      email
0   1   John Doe  john.doe@example.com
1   2   Jane Doe  jane.doe@example.com
execute_update(sql, *, credential_id=None, bind_parameters=None)

Execute a SQL update statement against a data store. Returns the message from the server.

Parameters:

  • sql (str) – The SQL update statement to execute.

  • credential_id (Optional[str]) – The ID of the credential to use. If not provided, the default credential will be used.

  • bind_parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – List of values to bind to the SQL statement. Each value is bound to a ? placeholder in the SQL statement. Binding is in-order.

Returns:

The message from the server. Returns “OK” if successful.

Return type:

str

Examples

>>> from datarobot.models.data_store import DataStore
>>> data_store = DataStore.get("my_data_store_id")
>>> data_store.execute_update(
...     "UPDATE my_table SET name = ? WHERE id = ?",
...     credential_id="my_credential_id",
...     bind_parameters=['John', 1],
... )
"OK"
classmethod is_execute_update_success(message)

Check if the message from the server indicates a successful execute update.

Parameters:

message (str) – The message from the server.

Returns:

True if the message indicates a successful execute update, False otherwise.

Return type:

bool

Examples

>>> from datarobot.models.data_store import DataStore
>>> ds = DataStore.get("my_data_store_id")
>>> DataStore.is_execute_update_success(
...     ds.execute_update("UPDATE my_table SET name = 'John Doe' WHERE id = 1")
... )
True
class datarobot.DataSource

A data source. Represents a data request.

Variables:

  • id (str) – The ID of the data source.

  • type (str) – The data source type.

  • canonical_name (str) – The user-friendly name of the data source.

  • creator (str) – The ID of the user who created the data source.

  • updated (datetime.datetime) – The time of the last update.

  • params (DataSourceParameters) – The data source parameters.

  • role (str or None) – If a string, represents a particular level of access and should be one of datarobot.enums.SHARING_ROLE. For more information on the specific access levels, see the sharing documentation. Pass None to a share function to revoke access for a specific user.

classmethod list(typ=None)

Returns a list of available data sources.

Parameters:

typ (DataStoreListTypes) – If specified, filters by the specified data source type. If not specified, defaults to DataStoreListTypes.DATABASES.

Returns:

data_sources – Contains a list of available data sources.

Return type:

list of DataSource instances

Examples

>>> import datarobot as dr
>>> data_sources = dr.DataSource.list()
>>> data_sources
[DataSource('Diagnostics'), DataSource('Airlines 100mb'), DataSource('Airlines 10mb')]
classmethod get(data_source_id)

Returns the data source.

Parameters:

data_source_id (str) – The identifier of the data source.

Returns:

data_source – The requested data source.

Return type:

DataSource

Examples

>>> import datarobot as dr
>>> data_source = dr.DataSource.get('5a8ac9ab07a57a0001be501f')
>>> data_source
DataSource('Diagnostics')
classmethod create(data_source_type, canonical_name, params)

Creates the data source.

Parameters:

  • data_source_type (str or DataStoreTypes) – The data source type.

  • canonical_name (str) – The user-friendly name of the data source.

  • params (DataSourceParameters) – The data source parameters.

Returns:

data_source – The created data source.

Return type:

DataSource

Examples

>>> import datarobot as dr
>>> params = dr.DataSourceParameters(
...     data_store_id='5a8ac90b07a57a0001be501e',
...     query='SELECT * FROM airlines10mb WHERE "Year" >= 1995;'
... )
>>> data_source = dr.DataSource.create(
...     data_source_type='jdbc',
...     canonical_name='airlines stats after 1995',
...     params=params
... )
>>> data_source
DataSource('airlines stats after 1995')
update(canonical_name=None, params=None)

Creates the data source.

Parameters:

  • canonical_name (str) – Optional; the user-friendly name of the data source.

  • params (DataSourceParameters) – Optional; the identifier of the DataDriver.

Return type:

None

Examples

>>> import datarobot as dr
>>> data_source = dr.DataSource.get('5ad840cc613b480001570953')
>>> data_source
DataSource('airlines stats after 1995')
>>> params = dr.DataSourceParameters(
...     query='SELECT * FROM airlines10mb WHERE "Year" >= 1990;'
... )
>>> data_source.update(
...     canonical_name='airlines stats after 1990',
...     params=params
... )
>>> data_source
DataSource('airlines stats after 1990')
delete()

Removes the DataSource

Return type:

None

classmethod from_server_data(data, keep_attrs=None)

Instantiate an object of this class using the data directly from the server, meaning that the keys may have the wrong camel casing

Parameters:

  • data (dict) – The directly translated dict of JSON from the server. DataRobot has not applied casing fixes yet.

  • keep_attrs (iterable) – A list, set, or tuple of the dotted namespace notations for attributes to keep within the object structure even if their values are None.

Return type:

TypeVar(TDataSource, bound= DataSource)

get_access_list()

Retrieve what users have access to this data source

Added in version v2.14.

Return type:

list of SharingAccess

share(access_list)

Modify the ability of users to access this data source

Added in version v2.14.

Parameters:

access_list (list of SharingAccess) – The modifications to make.

Return type:

None

Raises:

datarobot.ClientError: – If you do not have permission to share this data source, if the user you’re sharing with doesn’t exist, if the same user appears multiple times in the access_list, or if these changes would leave the data source without an owner.

Examples

Transfer access to the data source from old_user@datarobot.com to new_user@datarobot.com

from datarobot.enums import SHARING_ROLE
from datarobot.models.data_source import DataSource
from datarobot.models.sharing import SharingAccess

new_access = SharingAccess(
    "new_user@datarobot.com",
    SHARING_ROLE.OWNER,
    can_share=True,
)
access_list = [
    SharingAccess("old_user@datarobot.com", SHARING_ROLE.OWNER, can_share=True),
    new_access,
]

DataSource.get('my-data-source-id').share(access_list)
create_dataset(username=None, password=None, do_snapshot=None, persist_data_after_ingestion=None, categories=None, credential_id=None, use_kerberos=None)

Create a Dataset from this data source.

Added in version v2.22.

Parameters:

  • username (string, optional) – The username for database authentication.

  • password (string, optional) – The password (in cleartext) for database authentication. The password will be encrypted on the server side in scope of HTTP request and never saved or stored.

  • do_snapshot (Optional[bool]) – If unset, uses the server default: True. If true, creates a snapshot dataset; if false, creates a remote dataset. Creating snapshots from non-file sources requires an additional permission, Enable Create Snapshot Data Source.

  • persist_data_after_ingestion (Optional[bool]) – If unset, uses the server default: True. If true, will enforce saving all data (for download and sampling) and will allow a user to view extended data profile (which includes data statistics like min/max/median/mean, histogram, etc.). If false, will not enforce saving data. The data schema (feature names and types) still will be available. Specifying this parameter to false and doSnapshot to true will result in an error.

  • categories (list[string], optional) – An array of strings describing the intended use of the dataset. The current supported options are “TRAINING” and “PREDICTION”.

  • credential_id (string, optional) – The ID of the set of credentials to use instead of user and password. Note that with this change, username and password will become optional.

  • use_kerberos (Optional[bool]) – If unset, uses the server default: False. If true, use kerberos authentication for database authentication.

Returns:

response – The Dataset created from the uploaded data.

Return type:

Dataset

class datarobot.DataSourceParameters

Data request configuration.

Variables:

  • data_store_id (str) – The ID of the DataStore.

  • table (str) – Optional. The name of the specified database table.

  • schema (str) – Optional. The name of the schema associated with the table.

  • partition_column (str) – Optional. The name of the partition column.

  • query (str) – Optional. The user-specified SQL query.

  • fetch_size (int) – Optional. A user-specified fetch size in the range [1, 20000]. By default, DataRobot assigns a fetchSize to balance throughput and memory usage.

  • path (str) – Optional. The user-specified path for binary large object (BLOB) storage.

  • filter (str) – Optional. A connector-specific filter string, for example JQL for Jira. Only supported for DataRobot Connector v1, where applicable.

JDBC data preview

Preview data from a JDBC URL by executing SQL without creating a data store.

class datarobot.JdbcPreview

JDBC data preview API.

Run SQL against a JDBC URL and get a row-limited preview without creating a data store.

classmethod preview(jdbc_url, sql, max_rows=1000, parameters=None, bind_parameters=None)

Preview data from a JDBC URL by executing SQL without creating a data store.

Executes the given SQL against the JDBC URL and returns a row-limited preview. Connection credentials and parameters may be specified in the JDBC URL and/or in the parameters dict (e.g., user, password, ssl, timeout).

Parameters:

  • jdbc_url (str) – The JDBC URL (e.g. jdbc:postgresql://host:5432/dbname).

  • sql (str) – The SQL to execute (e.g. SELECT * FROM my_table LIMIT 10).

  • max_rows (int) – Row limit for the preview. Default is 1,000; maximum is 10,000.

  • parameters (Optional[Dict[str, str]]) – Optional connection parameters and credentials as key-value pairs (e.g. {"user": "u", "password": "p"}).

  • bind_parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – List of values to bind to the SQL statement. Each value is bound to a ? placeholder in the SQL statement. Binding is in-order.

Returns:

Object with columns (list of column names), records (list of rows), and result_schema (list of JdbcResultSchemaEntry), if returned by the server.

Return type:

JdbcPreviewData

Examples

>>> from datarobot.models.jdbc_data_preview import JdbcPreview
>>> preview = JdbcPreview.preview(
...     jdbc_url='jdbc:postgresql://localhost:5432/mydb',
...     sql='SELECT * FROM public.users WHERE id = ?',
...     max_rows=5,
...     parameters={'user': 'dbuser', 'password': 'secret'},
...     bind_parameters=[4],
... )
>>> preview.columns
['id', 'name', 'email']
>>> len(preview.records)
5
classmethod execute_update(jdbc_url, sql, parameters=None, bind_parameters=None)

Execute a SQL statement against a JDBC URL without creating a data store. Returns the message from the server.

Connection credentials and parameters may be specified in the JDBC URL and/or in the parameters dict (e.g. user, password, ssl, timeout).

Parameters:

  • jdbc_url (str) – The JDBC URL (e.g. jdbc:postgresql://host:5432/dbname).

  • sql (str) – The SQL statement to execute (e.g. INSERT INTO my_table (id, name) VALUES (1, 'John')).

  • parameters (Optional[Dict[str, str]]) – Optional connection parameters and credentials as key-value pairs (e.g. {"user": "u", "password": "p"}).

  • bind_parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – List of values to bind to the SQL statement. Each value is bound to a ? placeholder in the SQL statement. Binding is in-order.

Returns:

The message from the server. Returns “OK” if successful.

Return type:

str

Examples

>>> from datarobot.models.jdbc_data_preview import JdbcPreview
>>> JdbcPreview.execute_update(
...     jdbc_url='jdbc:postgresql://localhost:5432/mydb',
...     sql='INSERT INTO my_table (id, name) VALUES (?, ?)',
...     parameters={'user': 'dbuser', 'password': 'secret'},
...     bind_parameters=[1, 'John'],
... )
class datarobot.JdbcPreviewData

A JDBC data preview: columns, records, and optional result schema from running SQL against a JDBC URL.

property df: DataFrame

DataFrame representation of the preview data. Best-efforts parsing of records based on the result schema.

Return type:

pandas.DataFrame

class datarobot.JdbcResultSchemaEntry

Column metadata for one column in a JDBC data preview result schema.

Returned as elements of the result_schema attribute of JdbcPreviewData. Built via validation in JdbcPreviewData.

Variables:

  • name (str) – Column name.

  • data_type (str) – SQL/data type name (e.g., INTEGER, VARCHAR).

  • precision (int or None) – Optional numeric precision.

  • scale (int or None) – Optional numeric scale.

  • data_type_int (int or None) – Optional integer code for the data type.

Query Engine

To use DataRobot’s Query Engine, ensure you have installed the datarobot[query-engine] package extra.

class datarobot.query_engine.engine.QueryEngine

Execute SQL statements against a database through DataRobot. Supports statements as strings or SQLAlchemy constructs.

Parameters:

  • connection_manager (IConnectionManager) – The connection manager to use to execute statements against a database.

  • dialect (Optional[Dialect]) – The SQL dialect to use to compile statements. Modifies how SQLAlchemy constructs are compiled to their database-specific SQL strings. Defaults to sqlite.dialect().

  • paramstyle (Optional[str]) – The parameter style used to bind parameters to the statement. By default, “qmark” is used, which uses ? placeholders for parameters. Named parameters will be substituted in the SQL string according to the parameter name.

  • **kwargs – Additional keyword arguments for future-proofing.

Notes

When executing statements using SQLAlchemy constructs, dialect should be provided to ensure correct compilation.

Examples

Execute a query against a DataStore using QueryEngine:

>>> from datarobot.query_engine import QueryEngine
>>> engine = QueryEngine.from_data_store(
...     data_store_id="my_data_store_id",
...     credential_id="my_credential_id",
... )
>>> result: IteratorResult = engine.execute(
...     "SELECT * FROM my_table WHERE name = :name AND status IN :statuses",
...     params={
...         "name": "John Doe",
...         "statuses": ["active", "pending"]
...     }
... )
>>> result.all()
[(1, "John Doe", "active"), (2, "Jane Doe", "pending")]

Execute an update against a MS SQL Server database through a JDBC connection using QueryEngine and SQLAlchemy constructs. Note the dialect parameter is provided to ensure correct compilation:

>>> from sqlalchemy import insert, table, bindparam, column
>>> from sqlalchemy.dialects import mssql
>>> USER_TABLE = table("users", column("name"), column("status"))
>>> engine = QueryEngine.from_jdbc_connection(
...     jdbc_url="jdbc:sqlserver://localhost:1433;databaseName=mydb",
...     jdbc_params={"user": "sa", "password": "myPassword"},
...     dialect=mssql.dialect(),
... )
>>> engine.execute(
...     insert(USER_TABLE).values(name="John Doe", status=bindparam("status")),
...     params={"status": "active"},
... )
execute(stmt, params=None, *, max_rows=1000, mode=None, **kwargs)

Execute a SQL statement against a database. Supports string statements and SQLAlchemy constructs. Supports named parameters only.

Uses best-efforts to determine if the statement will return rows. Use mode to override this behavior. No results are returned for non-query statements.

Notes

If a parameter is a list or tuple, it will always be expanded. Replacement of a single parameter with a list or tuple is not supported. See examples below for more details.

Parameters:

  • stmt (Union[str, Executable]) – The SQL statement to execute. Supports string statements and SQLAlchemy constructs.

  • params (Optional[Dict[str, Union[str, int, float, bool, datetime, date, None, List[Union[str, int, float, bool, datetime, date, None]], Tuple[Union[str, int, float, bool, datetime, date, None], ...]]]]) – Named parameters to bind to the statement. Supports scalar, list, and tuple values.

  • max_rows (int) – The maximum number of rows to return. Only used for query-type statements.

  • mode (Optional[QueryMode]) – The mode to execute the statement. Overrides best-efforts to determine the mode.

  • **kwargs (Any) – Additional keyword arguments for future-proofing.

Returns:

The result of the statement. If the statement is a query, returns an IteratorResult with the result of the query. If the statement is an update, returns an IteratorResult with an empty result.

Return type:

sqlalchemy.engine.result.IteratorResult

Examples

Execute plain SQL string:

>>> from datarobot.query_engine import QueryEngine
>>> engine = QueryEngine.from_jdbc_connection(jdbc_url="jdbc:postgresql://localhost:5432/mydb")
>>> results = engine.execute("SELECT * FROM users")
>>> results.all()
[(1, "John Doe")]

Execute SQL query with named parameters:

>>> engine.execute("SELECT * FROM users WHERE name = :name", params={"name": "John Doe"})
>>> # Compiles to: SELECT * FROM users WHERE name = ?

>>> results.all()
[(1, "John Doe")]

Execute SQL query with named parameter that will be expanded. Note the expansion of the age parameter to (?, ?, ?):

>>> engine.execute(
...     "SELECT * FROM users WHERE name = :name AND age IN :ages",
...     params={"name": "John Doe", "ages": (30, 40, 50)},
... )
>>> # Compiles to: SELECT * FROM users WHERE name = ? AND age IN (?, ?, ?)
>>> results.all()
[(1, "John Doe", 30), (1, "John Doe", 40), (1, "John Doe", 50)]

Execute SQL statement to insert record with named parameter and parameter that will be expanded:

>>> engine.execute(
...     "INSERT INTO users (name, brothers) VALUES (:name, :brother_names)",
...     params={
...         "name": "John Doe",
...         "brother_names": ["Jim Doe", "Jack Doe"]
...     },
... )
>>> # Compiles to: INSERT INTO users (name, brothers) VALUES (?, (?, ?))

Execute SQLAlchemy select with named and bound parameters:

>>> from sqlalchemy import select, bindparam, column, table
>>> USER_TABLE = table("users", column("name"), column("status"))
>>> results = engine.execute(
...     select(USER_TABLE)
...         .where(USER_TABLE.c.name == "John Doe")
...         .where(USER_TABLE.c.status == bindparam("status")),
...     params={"status": "active"},
... )
>>> results.all()
[("John Doe", "active")]

Execute SQLAlchemy insert statement with bound parameter:

>>> from sqlalchemy import insert
>>> USER_TABLE = table("users", column("name"), column("status"))
>>> results = engine.execute(
...     insert(USER_TABLE).values(name="John Doe", status=bindparam("status")),
...     params={"status": "active"},
... )
classmethod from_jdbc_connection(jdbc_url=None, jdbc_url_generator=None, jdbc_params=None, dialect=None, paramstyle=None, **kwargs)

Create a QueryEngine from credentials for a JDBC database connection.

Parameters:

  • jdbc_url (Optional[str]) – The JDBC URL of the database.

  • jdbc_params (Optional[Dict[str, str]]) – The JDBC parameters to use for the connection.

  • jdbc_url_generator (Optional[Callable[[], str]]) – A function that returns a JDBC URL. Used to generate a JDBC URL for each connection if required.

  • **kwargs (Any) – Additional keyword arguments to pass to the QueryEngine constructor.

  • dialect (sqlalchemy.dialects.Dialect) – The SQL dialect to use to compile statements. Modifies how SQLAlchemy constructs are compiled to their database-specific SQL strings.

  • paramstyle (str) – The parameter style used to bind parameters to the statement. By default, “qmark” is used, which uses ? placeholders for parameters. Named parameters will be substituted in the SQL string according to the parameter name.

Return type:

QueryEngine

Notes

When constructing a QueryEngine to execute statements using SQLAlchemy constructs, dialect should be provided to ensure correct compilation. For example, for MS SQL Server, pass dialect=mssql.dialect().

Examples

Create a QueryEngine from a JDBC URL:

>>> from datarobot.query_engine import QueryEngine
>>> engine = QueryEngine.from_jdbc_connection(
...     jdbc_url="jdbc:postgresql://localhost:5432/mydb",
...     jdbc_params={"user": "postgres", "password": "postgres"},
... )

Create a QueryEngine with a JDBC URL that has to be generated dynamically:

>>> engine = QueryEngine.from_jdbc_connection(
...     jdbc_url_generator=my_function_here",
...     jdbc_params={"user": "postgres", "password": "postgres"},
... )

Create a QueryEngine with a JDBC URL for an MS SQL Server:

>>> from sqlalchemy.dialects import mssql
>>> engine = QueryEngine.from_jdbc_connection(
...     jdbc_url="jdbc:sqlserver://localhost:1433;databaseName=mydb",
...     jdbc_params={"user": "sa", "password": "myPassword"},
...     dialect=mssql.dialect(),
... )
classmethod from_data_store(data_store_id, credential_id=None, dialect=None, paramstyle=None, **kwargs)

Create a QueryEngine for a DataStore database connection.

Notes

Not all DataStores support statement execution through QueryEngine (e.g. Blob Storage).

Parameters:

  • data_store_id (str) – The ID of the DataStore to use for the connection.

  • credential_id (Optional[str]) – The ID of the credential to use for the connection. If not provided, the default credential for the DataStore will be used.

  • **kwargs (Any) – Additional keyword arguments to pass to the QueryEngine constructor.

  • dialect (sqlalchemy.dialects.Dialect) – The SQL dialect to use to compile statements. Modifies how SQLAlchemy constructs are compiled to their database-specific SQL strings.

  • paramstyle (str) – The parameter style used to bind parameters to the statement. By default, “qmark” is used, which uses ? placeholders for parameters. Named parameters will be substituted in the SQL string according to the parameter name.

Return type:

QueryEngine

Notes

When constructing a QueryEngine to execute statements using SQLAlchemy constructs, dialect should be provided to ensure correct compilation. For example, for MS SQL Server, pass dialect=mssql.dialect().

Examples

Create a QueryEngine for a DataStore using default sqlite SQL dialect:

>>> from datarobot.query_engine import QueryEngine
>>> engine = QueryEngine.from_data_store(
...     data_store_id="my_data_store_id",
...     credential_id="my_credential_id"
... )

Create a QueryEngine for an MS SQL Server DataStore:

>>> from sqlalchemy.dialects import mssql
>>> engine = QueryEngine.from_data_store(
...     data_store_id="my_data_store_id",
...     credential_id="my_credential_id",
...     dialect=mssql.dialect(),
... )
class datarobot.query_engine.engine.QueryMode

Mode with which to execute a SQL statement. Can be used to override the best-efforts heuristic for determining the mode.

Examples

Override QueryEngine’s guess at determining the mode to ensure you get results back:

>>> from datarobot.query_engine import QueryMode, QueryEngine
>>> engine = QueryEngine.from_jdbc_connection(jdbc_url="jdbc:postgresql://localhost:5432/mydb")
>>> results = engine.execute(
...     "UPDATE users SET status = 'active' WHERE name = 'John Doe' RETURNING id, name, status",
...     mode=QueryMode.QUERY, # overrides QueryEngine's guess of the mode to ensure you get results back
... )
>>> results.all()
[(1, "John Doe", "active")]
QUERY = 'query'

Assumed to return rows.

EXECUTE_UPDATE = 'execute_update'

Executes the statement without returning any results.

class datarobot.query_engine.engine.IConnectionManager

Interface for connection managers

query(sql, parameters=None, max_rows=1000)

Execute a SQL query and return the result as a JdbcPreviewData object.

Parameters:

  • sql (str) – The SQL query to execute.

  • parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – Parameters to bind to the statement.

  • max_rows (int) – The maximum number of rows to return.

Returns:

Data returned from the query.

Return type:

JdbcPreviewData

execute_update(sql, parameters=None)

Execute an SQL statement.

Parameters:

  • sql (str) – The SQL statement to execute.

  • parameters (Optional[List[Union[str, int, float, bool, datetime, date, None]]]) – Parameters to bind to the statement.

Raises:

StatementError: – If the statement is not successful.

Return type:

None

Types, Helpers & Enums

class datarobot.models.data_store.TestResponse

The result of testing a data store’s connection.

Variables:

message (str) – A human-readable description of the test result.

class datarobot.models.data_store.SchemasResponse

The schemas and catalogs available through a data store.

Variables:

  • schemas (list[str]) – The names of the schemas available in the catalog.

  • catalogs (list[str] or None) – The names of the catalogs available on the data store, if applicable.

  • catalog (str) – The catalog that schemas belongs to.

class datarobot.models.data_store.TablesResponse

The tables available through a data store.

Variables:

  • catalog (str) – The catalog that tables belongs to.

  • tables (list[TableDescription]) – The tables available in catalog.

class datarobot.models.data_store.TableDescription

Metadata describing a single table available through a data store.

Variables:

  • catalog (str or None) – The catalog the table belongs to, if applicable.

  • name (str) – The name of the table.

  • schema (str or None) – The schema the table belongs to, if applicable.

  • type (DATA_STORE_TABLE_TYPE) – The type of the table. One of datarobot.enums.DATA_STORE_TABLE_TYPE.

class datarobot.enums.DATA_STORE_TABLE_TYPE

The type of a table available through a data store.

TABLE = 'TABLE'

A table.

VIEW = 'VIEW'

A view.