SQL interface (sql)

The sql module provides access to relational databases from Orange. It currently supports:

• MySql through MySQL for Python,
• Postgres through Psycopg,
• sqlite through sqlite3.
• odbc through pyodbc.

SQLReader and SQLWriter classes require connection string based on standard format scheme://[user[:password]@]host[:port]/database[?parameters].

Examples of valid connection strings:

• sqlite://database.db/
• mysql://user:password@host/database
• mysql://host/database?debug=1
• postgres://user@host/database?debug=&cache=
• postgres://host:5432/database
• odbc://user:password@host:port/database?DSN=dsn

Attribute Names and Types

Rows returned by an SQL query have to be converted into Orange examples. Each column in a row has to be converted into a certain feature type. The following conversions between SQL and Orange types are used:

• STRING and DATETIME attributes are converted to Orange strings.
• The features listed in discrete_names are converted to Orange discrete features.
• Other features are converted to continuous Orange features.
• The attribute in class_name is set as the class features. If no class_name is set, the column with the name “class” in the returned SQL query is set as the class attribute. If no such column exists, the last column is set as the class features.

Note

When reading sqlite data table into Orange.data.Table all columns are cast into Orange.feature.String.

Examples

The following example populates the sqlite database with data from Orange.data.Table.

from Orange.data.sql import *

# load dataset and save it into sqlite database
data = Orange.data.Table("iris")
w = SQLWriter('sqlite://iris.db/')
w.create('iris', data)

Using the existing sqlite database one can fetch back the data into Orange.data.Table.

# create sql reader
from Orange.data.sql import *
r = SQLReader()
r.connect('sqlite://iris.db/')

# read iris dataset from database and convert it to Orange Table
r.execute("SELECT *  FROM iris;")
d = r.data()
print "\n%d instances returned" % len(d)
print "Output data domain:"
for a in d.domain.variables:
    print a
print "First instance :", d[0],

r.execute("SELECT `petal width`, `petal length` FROM iris WHERE `sepal length` < 5.0")
sel = r.data()
print "\n%d instances returned" % len(sel)

The output of the last example is:

150 instances returned
Output data domain:
StringVariable 'sepal length'
StringVariable 'sepal width'
StringVariable 'petal length'
StringVariable 'petal width'
StringVariable 'iris'
First instance : ['5.09999990463', '3.5', '1.39999997616', '0.20000000298', 'Iris-setosa']
22 instances returned

class Orange.data.sql.SQLReader(addr=None, domain_depot=None)

SQLReader establishes a connection with a database and provides the methods needed to fetch the data from the database into Orange.

__init__(addr=None, domain_depot=None)

Parameters:

• uri (str) – Connection string (scheme://[user[:password]@]host[:port]/database[?parameters]).
• domain_depot (orange.DomainDepot) – Domain depot

class_name

Name of class variable.

connect(uri)

Connect to the database.

Parameters:uri (str) – Connection string (scheme://[user[:password]@]host[:port]/database[?parameters])

data()

Return Orange.data.Table produced by the last executed query.

disconnect()

Disconnect from the database.

discrete_names

Names of discrete attributes.

domain

Orange domain.

execute(query, domain=None)

Executes an sql query.

meta_names

Names of meta attributes.

query

Query to be executed on the next execute().

update()

Execute a pending SQL query.

class Orange.data.sql.SQLWriter(uri=None)

Establishes a connection with a database and provides the methods needed to create an appropriate table in the database and/or write the data from an Orange.data.Table into the database.

__init__(uri=None)

Parameters:uri (str) – Connection string (scheme://[user[:password]@]host[:port]/database[?parameters])

connect(uri)

Connect to the database.

Parameters:uri (str) – Connection string (scheme://[user[:password]@]host[:port]/database[?parameters])

create(table, instances, rename_dict={}, type_dict={})

Create the required SQL table, then write the data into it.

Parameters:

• table (str) – Table name
• instances (Orange.data.Table) – Data to be written into the database.
• rename_dict (dict) – When rename_dict is provided the used names are remapped. The orange attribute “X” is written into the database column rename_dict[“X”] of the table.
• type_dict (dict) – When type_dict is provided the used variables are casted into new types. The type of orange attribute “X” is casted into the database column of type rename_dict[“X”].

disconnect()

Disconnect from the database.

write(table, instances, rename_dict=None)

Writes the data into the table.

Parameters:

• table (str) – Table name.
• instances (Orange.data.Table) – Data to be written into the database.
• rename_dict (dict) – When rename_dict is provided the used names are remapped. The orange attribute “X” is written into the database column rename_dict[“X”] of the table.