2
$\begingroup$

I would like to be able to directly access both MySQL and SQLite databases as part of an Orange workflow. I see there is support for PostgreSQL and (possibly)

$\endgroup$

2 Answers 2

2
$\begingroup$

In order to be more precise, I have just finished the example of a Python (ver. 3.7) Script to include into the workflow to extract data from a MySQL server via an ODBC connector as following :

from Orange.data import Table, Domain, ContinuousVariable
from pyodbc import connect

connecteur = connect(DSN='basepython') # "table1" from a MySQL server via ODBC connector.
curseur = connecteur.cursor() # Creation of the cursor for data swept.

# Execution of the SQL Query.
curseur.execute("SELECT id, champ1, champ2, champ3 FROM table1")

# All data of "table1" are saved in "donnees".
donnees = curseur.fetchall()

liste_globale = [] # Final list : It will contain the final data.
liste_intermediaire = [] # Temporary list : Only used for conversion.

# Whereas having tuples as list elements, you must convert them into lists included into the main list.
for partie in donnees:
    for i in range(len(partie)):
        liste_intermediaire.append(partie[i])
    liste_globale.append(liste_intermediaire)
    liste_intermediaire = []

# Creation of the data domain : Names of the variables seen in Orange WorkFlow.
identite = ContinuousVariable("Identification")
colonne1 = ContinuousVariable("Colonne1")
colonne2 = ContinuousVariable("Colonne2")
colonne3 = ContinuousVariable("Colonne3")

domaine = Domain([identite, colonne1, colonne2, colonne3])

# Final data
out_data = Table(domaine, liste_globale) # Data seen at the output of the Python_Script widget.
print(out_data) # This line is only used for debugging, you can comment it.

# Don't forget to close the connection at the end.
connecteur.close()

This example is used with the following workflow : Example of WorkFlow

Of course, if you want to connect both MySQL server and SQLite in the same time, you can adapt this script with two connectors, for example :

connecteur1 = connect(DSN="MySQL_ODBC_DSN")
connecteur2 = connect(DSN="SQLite_ODBC_DSN")

And you can work with these two independent connectors as following :

curseur1 = connecteur1.cursor()
curseur2 = connecteur2.cursor()

curseur1.execute("SELECT * FROM mysql_table")
curseur2.execute("SELECT * FROM sqlite3_table WHERE id='2'")

donnees1 = curseur1.fetchall() # If there are several rows to save.
donnees2 = curseur2.fetchone() # If there is only one row to save.

And at the end, don't forget to close each connection :

# Closing of the two opened database connectors.
connecteur1.close()
connecteur2.close()
$\endgroup$
0
$\begingroup$

I would like accessing MySQL or SQLite too but the direct supports are only PostgreSQL and MSSQL.

Nevertheless, I need to create access to a MySQL Server, so another way can be used.

Indeed, a Python Script can be inserted in the workflow so you can call either the pyodbc module (to access directly your DSN from your OS) or sqlite3 / pymysql module.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.