civis.io.read_civis_sql

civis.io.read_civis_sql(sql, database, use_pandas=False, encoding=None, job_name=None, api_key=None, client=None, credential_id=None, polling_interval=None, archive=False, hidden=True, **kwargs)[source]

Read data from Civis using a custom SQL string.

The custom SQL string will be executed twice; once to attempt to retrieve headers and once to retrieve the data. This is done to use a more performant method for retrieving the data. The first execution of the custom SQL is controlled such that changes in state cannot occur (e.g., INSERT, UPDATE, DELETE, etc.).

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

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.

api_keyDEPRECATED str, optional

Your Civis API key. If not given, the CIVIS_API_KEY environment variable 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.

archivebool, optional (deprecated)

If True, archive the import job as soon as it completes.

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

>>> sql = "SELECT * FROM schema.table"
>>> df = read_civis_sql(sql, "my_database", use_pandas=True)
>>> col_a = df["column_a"]

>>> data = 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]