Using BI/SQL interface with python

Support
1

How do I access the BI/SQL port via python?
I have successfully connected to the BI/SQL port using DBeaver.

But no luck using python 3.8 with pymysql and sqlalchemy

from sqlalchemy import create_engine
import pymysql
import pandas as pd
engine = create_engine("mysql+pymysql://pass@host:port/database")
sql = "show tables"
df = pd.read_sql(sql, engine)
df

The server log has an exception:
ERROR 2022-03-24 11:45:44,047 [Stardog.Executor-187] com.complexible.stardog.serf.mysql.SerfServer:handleClient(288): BI/SQL server frontend error
java.lang.NullPointerException: utf8mb4

The client side:

OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
/var/folders/rg/jrpg2c9d2wsc593l608kj85h0000gp/T/ipykernel_29493/3433910095.py in <module>
      3 engine = create_engine("mysql+pymysql://anonymous:password@host.com:5806/Topology")
      4 sql = "show tables"
----> 5 df = pd.read_sql(sql, engine)
      6 df
2

Hi Johnz,

As the name implies, the BI/SQL interface is indented to be used with BI tools. From your code snippet you posted, it looks like you're trying to read Stardog data into a dataframe. We'd recommend doing this using pystardog, our Python library, to do so. You can find the documentation for it here: pystardog — pystardog documentation. On its Github page, you can also find an example Jupyter notebook.

Best,
Clark

3

Is there more information in the stardog.log after:

ERROR 2022-03-24 11:45:44,047 [Stardog.Executor-187] com.complexible.stardog.serf.mysql.SerfServer:handleClient(288): BI/SQL server frontend error
java.lang.NullPointerException: utf8mb4

In particular a stacktrace including CausedBy blocks?

Thanks,
-Paul

4

bi-sql-access-from-python-server-log.txt (11.4 KB)

5

I have pystardog working, but my understanding is that interface is for SPARQL and not SQL.

My users will want to access stardog via the SQL interface using python in a jupyter notebook.

Does pystardog support the BI/SQL mapping?

6

Thanks for the log file. The issue we're having here is related to some MySQL-specific calls made by sqlalchemy around character sets and data types that we currently do not support.

-Paul

7

Good to know, I will look for a non-sqlachemy solution

8

mysql-connector-python works with stardog BI/SQL.

The code snippet to read a MySQL query into a pandas data frame:

import mysql.connector as connection
import pandas as pd
try:
    mydb = connection.connect(host="localhost", database = 'Student',user="root", passwd="root",use_pure=True)
    query = "Select * from studentdetails;"
    result_dataFrame = pd.read_sql(query,mydb)
    mydb.close() #close the connection
except Exception as e:
    mydb.close()
    print(str(e))
10

BI/SQL JDBC connector support for sqlalchemy is a very desirable feature.
The recent Release of Pandas only support sqlalchemy and has deprecated the above example code.

So without sqlalchemy support, Stardog would not be able to claim it supported a python interface.