civis.io.read_civis_sql

civis.io.read_civis_sql(sql, database, use_pandas=False, sql_params_arguments=None, encoding=None, job_name=None, client=None, credential_id=None, polling_interval=None, hidden=True, **kwargs)

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.

use_pandasbool, optional

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

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 use_pandas is True, this parameter is passed to the encoding kwarg of pandas.read_csv(). If use_pandas is False, 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 use_pandas is True or passed into csv.reader() if use_pandas is False.

Returns:

datapandas.DataFrame or list

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

Raises:

ImportError

If use_pandas is True and pandas 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", use_pandas=True)
>>> 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]