civis.io.read_civis_sql

civis.io.read_civis_sql(sql: str, database: str | int, return_as: str = 'list', use_pandas=DEPRECATED, sql_params_arguments: dict | None = None, encoding: str | None = None, job_name: str | None = None, client: APIClient | None = None, credential_id: int | None = None, polling_interval: int | float | None = None, hidden: bool = True, **kwargs) → list | pd.DataFrame | pl.DataFrame

Read data from Civis using a custom SQL string.

If no data is expected to return from the query, consider query_civis() instead.

Parameters:

sqlstr

The SQL select string to be executed.

databasestr or int

Execute the query against this database. Can be the database name or ID.

return_asstr, {“list”, “pandas”, “polars”}

If "list" (the default), return a list. If "pandas", return a pandas.DataFrame. If "polars", return a polars.DataFrame.

use_pandasbool, optional

If True, return a pandas.DataFrame. Otherwise, return a list of results from csv.reader().

Deprecated since version 2.6.0: use_pandas will be removed at civis-python v3.0.0. Please use return_as instead.

sql_params_argumentsdict, optional

A dictionary of SQL query parameters to pass directly to civis.APIClient.scripts.post_sql. The only allowed keys are "params" (whose value is a list[dict]) and "arguments" (whose value is a dict). Please refer to the linked API documentation for how to format these two keys’ values.

encodingstr, optional

If return_as is "pandas" or "polars", this parameter is passed to the encoding kwarg of pandas.read_csv() or polars.read_csv(), respectively. If return_as is "list", and if this parameter isn’t provided, then the UTF-8 encoding is assumed. In case you encounter a UnicodeDecodeError, consider choosing an encoding suitable for your data; see the list of standard encodings.

job_namestr, optional

A name to give the job. If omitted, a random job name will be used.

clientcivis.APIClient, optional

If not provided, an civis.APIClient object will be created from the CIVIS_API_KEY.

credential_idstr or int, optional

The database credential ID. If None, the default credential will be used.

polling_intervalint or float, optional

Number of seconds to wait between checks for query completion.

hiddenbool, optional

If True (the default), this job will not appear in the Civis UI.

**kwargskwargs

Extra keyword arguments are passed into pandas.read_csv() if return_as is "pandas", or passed into csv.reader() if return_as is "list", or passed into polars.read_csv() if return_as is "polars".

Returns:

datalist | pandas.DataFrame | polars.DataFrame

A list of rows (with header as first row) if return_as is "list", or pandas.DataFrame if return_as is "pandas", or polars.DataFrame if return_as is "polars". Note that if return_as is "list", no parsing of types is performed and each row will be a list of strings.

Raises:

ImportError

If return_as is "pandas" and pandas is not installed. If return_as is "polars" and polars is not installed.

EmptyResultError

If no rows were returned as a result of the query.

See also

civis.io.read_civis

Read directly into memory without SQL.

civis.io.civis_to_csv

Write directly to a CSV file.

Notes

This reads the data into memory.

Examples

>>> import civis
>>> sql = "SELECT * FROM schema.table"
>>> df = civis.io.read_civis_sql(sql, "my_database", return_as="pandas")
>>> col_a = df["column_a"]

>>> data = civis.io.read_civis_sql(sql, "my_database")
>>> columns = data.pop(0)
>>> col_a_index = columns.index("column_a")
>>> col_a = [row[col_a_index] for row in data]