source: orange/docs/reference/rst/Orange.data.sql.rst @ 10162:fa36353462d4

Revision 10162:fa36353462d4, 2.5 KB checked in by janezd <janez.demsar@…>, 2 years ago (diff)

Few minor fixes in Orange.data.sql

Line 
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
12:class:`SQLReader` and :class:`SQLWriter` classes require connection string based on
13standard format scheme://[user[:password]@]host[:port]/database[?parameters].
14
15Examples of valid connection strings:
16
17- sqlite://database.db/
18- mysql://user:password@host/database
19- mysql://host/database?debug=1
20- postgres://user@host/database?debug=&cache=
21- postgres://host:5432/database
22
23Attribute Names and Types
24-------------------------
25
26Rows returned by an SQL query have to be converted into Orange examples.
27Each column in a row has to be converted into a certain feature type. The
28following conversions between SQL and Orange types are used:
29
30- STRING and DATETIME attributes are converted to Orange strings.
31
32- The features listed in ``discrete_names`` are converted to Orange
33  discrete features.
34
35- Other features are converted to continuous Orange features.
36
37- The attribute in ``class_name`` is set as the class features. If no
38  ``class_name`` is set, the column with the name "class" in the
39  returned SQL query is set as the class attribute. If no such column
40  exists, the last column is set as the class features.
41
42.. note:: When reading ``sqlite`` data table into :class:`Orange.data.Table` all columns are cast into :class:`Orange.feature.String`.
43
44**Examples**
45
46The following example populates the `sqlite <http://www.sqlite.org/>`_ database with data from :class:`Orange.data.Table`.
47
48.. literalinclude:: code/sql-example.py
49   :lines: 1-6
50
51Using the existing `sqlite <http://www.sqlite.org/>`_ database one can fetch back the data into :class:`Orange.data.Table`.
52
53.. literalinclude:: code/sql-example.py
54   :lines: 8-24
55
56The output of the last example is::
57
58  150 instances returned
59  Output data domain:
60  StringVariable 'sepal length'
61  StringVariable 'sepal width'
62  StringVariable 'petal length'
63  StringVariable 'petal width'
64  StringVariable 'iris'
65  First instance : ['5.09999990463', '3.5', '1.39999997616', '0.20000000298', 'Iris-setosa']
66  22 instances returned
67
68.. autoclass:: Orange.data.sql.SQLReader
69   :members:
70
71.. autoclass:: Orange.data.sql.SQLWriter
72   :members:
Note: See TracBrowser for help on using the repository browser.