civis.io.read_civis_sql

civis.io.read_civis_sql(sql, database, use_pandas=False, 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:

sql : str, optional

The SQL select string to be executed.

database : str or int

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

use_pandas : bool, optional

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

job_name : str, optional

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

api_key : DEPRECATED str, optional

Your Civis API key. If not given, the CIVIS_API_KEY environment variable will be used.

client : civis.APIClient, optional

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

credential_id : str or int, optional

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

polling_interval : int or float, optional

Number of seconds to wait between checks for query completion.

archive : bool, optional (deprecated)

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

hidden : bool, optional

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

**kwargs : kwargs

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:

data : pandas.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.

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]