source: orange/docs/reference/rst/Orange.data.sql.rst @ 11832:d3f10458113d

Revision 11832:d3f10458113d, 2.8 KB checked in by Slach@…, 3 months ago (diff)

adding ODBC support for SQL select widget

RevLine 
[11832]1##################################
2SQL interface (``sql``)
3##################################
4
5The :class:`sql` module provides access to relational databases from Orange.
6It currently supports:
7
8- `MySql <http://www.mysql.com/>`_  through `MySQL for Python <http://sourceforge.net/projects/mysql-python/>`_,
9- `Postgres <http://www.postgresql.org>`_ through `Psycopg <http://initd.org/psycopg/>`_,
10- `sqlite <http://www.sqlite.org/>`_ through `sqlite3 <http://docs.python.org/library/sqlite3.html>`_.
11- `odbc <http://en.wikipedia.org/wiki/ODBC>`_ through `pyodbc <https://code.google.com/p/pyodbc/>`_.
12
13:class:`SQLReader` and :class:`SQLWriter` classes require connection string based on
14standard format scheme://[user[:password]@]host[:port]/database[?parameters].
15
16Examples of valid connection strings:
17
18- sqlite://database.db/
19- mysql://user:password@host/database
20- mysql://host/database?debug=1
21- postgres://user@host/database?debug=&cache=
22- postgres://host:5432/database
23- odbc://user:password@host:port/database?DSN=dsn
24
25Attribute Names and Types
26-------------------------
27
28Rows returned by an SQL query have to be converted into Orange examples.
29Each column in a row has to be converted into a certain feature type. The
30following conversions between SQL and Orange types are used:
31
32- STRING and DATETIME attributes are converted to Orange strings.
33
34- The features listed in ``discrete_names`` are converted to Orange
35  discrete features.
36
37- Other features are converted to continuous Orange features.
38
39- The attribute in ``class_name`` is set as the class features. If no
40  ``class_name`` is set, the column with the name "class" in the
41  returned SQL query is set as the class attribute. If no such column
42  exists, the last column is set as the class features.
43
44.. note:: When reading ``sqlite`` data table into :class:`Orange.data.Table` all columns are cast into :class:`Orange.feature.String`.
45
46**Examples**
47
48The following example populates the `sqlite <http://www.sqlite.org/>`_ database with data from :class:`Orange.data.Table`.
49
50.. literalinclude:: code/sql-example.py
51   :lines: 1-6
52
53Using the existing `sqlite <http://www.sqlite.org/>`_ database one can fetch back the data into :class:`Orange.data.Table`.
54
55.. literalinclude:: code/sql-example.py
56   :lines: 8-24
57
58The output of the last example is::
59
60  150 instances returned
61  Output data domain:
62  StringVariable 'sepal length'
63  StringVariable 'sepal width'
64  StringVariable 'petal length'
65  StringVariable 'petal width'
66  StringVariable 'iris'
67  First instance : ['5.09999990463', '3.5', '1.39999997616', '0.20000000298', 'Iris-setosa']
68  22 instances returned
69
70.. autoclass:: Orange.data.sql.SQLReader
71   :members:
72
73.. autoclass:: Orange.data.sql.SQLWriter
[9992]74   :members:
Note: See TracBrowser for help on using the repository browser.