SQL table (data.sql)

class Orange.data.sql.table.SqlTable(connection_params, table_or_sql, backend=None, type_hints=None, inspect_values=False)[source]

SqlTable represents a table with the data which is stored in the database. Besides the inherited attributes, the object stores a connection to the database and row filters.

Constructor connects to the database, infers the variable types from the types of the columns in the database and constructs the corresponding domain description. Discrete and continuous variables are put among attributes, and string variables are meta attributes. The domain does not have a class.

SqlTable overloads the data access methods for random access to rows and for iteration (__getitem__ and __iter__). It also provides methods for fast computation of basic statistics, distributions and contingency matrices, as well as for filtering the data. Filtering the data returns a new instance of SqlTable. The new instances however differs only in that an additional filter is added to the row_filter.

All evaluation is lazy in the sense that most operations just modify the domain and the list of filters. These are used to construct an SQL query when the data is actually needed, for instance to retrieve a data row or compute a distribution of values for a certain column.

connection

The object that holds the database connection. An instance of a class compatible with Python DB API 2.0.

host

The host name of the database server

database

The name of the database

table_name

The name of the table in the database

row_filters

A list of filters that are applied when constructing the query. The filters in the should have a method to_sql. Module Orange.data.sql.filter contains classes derived from filters in Orange.data.filter with the appropriate implementation of the method.

__init__(connection_params, table_or_sql, backend=None, type_hints=None, inspect_values=False)[source]

Create a new proxy for sql table.

To create a new SqlTable, specify the connection parameters for psycopg2 and the name of the table/sql query used to fetch the data.

table = SqlTable(‘database_name’, ‘table_name’) table = SqlTable(‘database_name’, ‘SELECT * FROM table’)

For complex configurations, dictionary of connection parameters can be used instead of the database name. For documentation about connection parameters, see: http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Data domain is inferred from the columns of the table/query.

The (very quick) default setting is to treat all numeric columns as continuous variables and everything else as strings and placed among meta attributes.

If inspect_values parameter is set to True, all column values are inspected and int/string columns with less than 21 values are intepreted as discrete features.

Domains can be constructed by the caller and passed in type_hints parameter. Variables from the domain are used for the columns with the matching names; for columns without the matching name in the domain, types are inferred as described above.

__getitem__(key)[source]

Indexing of SqlTable is performed in the following way:

If a single row is requested, it is fetched from the database and returned as a SqlRowInstance.

A new SqlTable with appropriate filters is constructed and returned otherwise.

__iter__()[source]

Iterating through the rows executes the query using a cursor and then yields resulting rows as SqlRowInstances as they are requested.

copy()[source]

Return a copy of the SqlTable

__bool__()[source]

Return True if the SqlTable is not empty.

__len__()[source]

Return number of rows in the table. The value is cached so it is computed only the first time the length is requested.

download_data(limit=None, partial=False)[source]

Download SQL data and store it in memory as numpy matrices.

X

Numpy array with attribute values.

Y

Numpy array with class values.

metas

Numpy array with class values.

W

Numpy array with class values.

ids

Numpy array with class values.

class Orange.data.sql.table.SqlRowInstance(domain, data=None)[source]

Extends Orange.data.Instance to correctly handle values of meta attributes.