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)
2 Answers
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 :
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()
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.