Datalore 2024.2 Help

SQL cells

Use SQL cells to query the following data sources:

  • Attached databases

  • Dataframes

  • Attached .csv files

Query a database in an SQL cell

  1. Add an SQL cell:

    • Go to Tools | Attached data and click Connect SQL cell for the attached database that you want to query.

      Opening an SQL cell from Attached data
    • Hover over the bottom border of a cell and select More | SQL. If there are several attached databases, in the cell, click Select database and select the required data source.

      Selecting a database for querying
  2. Enter an SQL statement.

  3. Run the query (Run icon or Ctrl+Enter shortcut). The result set will be shown in the output and saved to a DataFrame. The resulting DataFrame name is shown in the cell toolbar.

  4. (Optional) To rename the data object, click the object name next to RESULT SAVED TO on the SQL toolbar.

Query a DataFrame or .csv file in an SQL cell

The procedure queries a dataframe or a .csv file from notebook or workspace files and saves the result set to a dataframe.

  1. Hover over the bottom border of a cell and select More | SQL.

  2. In the added SQL cell, click Select database and select Dataframes.

  3. (Optional) To rename the resulting dataframe:

    1. Click the name next to RESULT SAVED TO on the SQL cell toolbar.

    2. Enter a new name and press Enter.

  4. Enter an SQL statement. Run the query. The result set will be shown in the output and saved to a new dataframe under the name shown on the SQL cell toolbar.

  5. (Optional) To get a visual representation of the retrieved data, switch to the Visualize tab. Find more details in Automatic plotting.

SQL cell examples

Querying a Snowflake database

Querying a Snowflake database

Querying a DataFrame

Querying a DataFrame

Querying a .csv file

Querying a csv file

Parameterized SQL queries

You can use Python variables in your SQL queries in Datalore. Such queries can be reused repeatedly with different values, which helps make your reports more interactive.

Supported variable types are: strings, numbers, booleans, and lists. Make sure you place your variable inside {} brackets.

Parameterized SQL query example

In the image below, you can see two cells:

  • Dropdown interactive control cell using the method variable.

  • SQL cell where the value in payment column equals the method variable value selected from the dropdown list.

Using a parameterized SQL query

Table parametrisation for SQL cells

Starting from this version (2023.5), Datalore allows for table paramterization by supporting unsafe parameters for SQL cell statements. The syntax is as follows:

select * from {table_name | unsafe} limit {n}

In the code example above, the {table_name | unsafe} part will assume the value of the table_name variable.

String variables for unsafe parameters

To pass a string variable to an unsafe parameter, use quote escaping quotes. See the examples below:

Unsafe parameter defined in a code cell
product = "'Product'"
Unsafe parameter referenced in an SQL cell
SELECT * from releases where jb_product in ({product | unsafe})
Resulting SQL query
SELECT * from releases where jb_product in ('Product')

Run SQL cells as functions from other cells

Datalore allows you to wrap queries from SQL cells into Python code and use them as functions with parameters. The function with an SQL cell statement has the following syntax: {dataframe_name}_execute_sql_cell, where {dataframe_name} is the name of the dataframe that saves the result of the SQL cell you want to use.

Example

SQL cell with parameters:

select * from small_iris_df where small_iris_df."Id" in {idd} limit {lim}

Python code cell with the above SQL statement:

frames = [await df_1_execute_sql_cell(i,5,2) for i in range(5) ] pd.concat(frames)
Last modified: 29 May 2024