Database connectivity¶

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

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

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

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

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

>>> import datarobot as dr
>>> connector = dr.Connector.get('5fe1063e1c075e0245071446')
>>> connector
Connector('Google Drive')

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

>>> import datarobot as dr
>>> data_stores = dr.DataStore.list()
>>> data_stores
[DataStore('Demo'), DataStore('Airlines')]

>>> import datarobot as dr
>>> data_store = dr.DataStore.get('5a8ac90b07a57a0001be501e')
>>> data_store
DataStore('Demo')

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

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

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

>>> 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']}

>>> 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'}

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

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

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

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

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

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

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

>>> import datarobot as dr
>>> data_source = dr.DataSource.get('5a8ac9ab07a57a0001be501f')
>>> data_source
DataSource('Diagnostics')

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

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

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)

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

>>> 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'],
... )

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

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

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

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

>>> 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 (?, (?, ?))

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

>>> 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"},
... )

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

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

>>> 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(),
... )

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

>>> 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(),
... )