Is there Orange support for MySQL and SQLite?

Question

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)

Answer 1

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()

Answer 2

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.