Hello,
I am using Plotly to connect to a SQL Database from Azure Databricks. I am following this tutorial, however, when I ran the code and clicked on the link where Dash is running, I got this
Here is the code I ran:
from dash import Dash, dash_table
from databricks import sql
app = Dash(__name__)
server = app.server
# Set these as environment variables in Dash Enterprise or locally
SERVER_HOSTNAME = 'yadadadadada'
HTTP_PATH = 'i know this works'
ACCESS_TOKEN = 'check123'
# Configure according to your table and database names in Databricks
DB_NAME = "bronze_aupe"
TABLE_NAME = "df_aupe_global"
with sql.connect(
server_hostname=SERVER_HOSTNAME, http_path=HTTP_PATH, access_token=ACCESS_TOKEN
) as connection:
with connection.cursor() as cursor:
cursor.execute(f"SELECT * FROM {DB_NAME}.{TABLE_NAME} LIMIT 10")
df = cursor.fetchall_arrow()
df = df.to_pandas()
app.layout = dash_table.DataTable(df.to_dict('records'), [{"name": i, "id": i} for i in df.columns])
if __name__ == '__main__':
app.run(debug=True)
I know I was able to connect to the SQL Database, since I did a print statement and was able to see the pandas dataframe. My issue is with this line
app.layout = dash_table.DataTable(df.to_dict(‘records’), [{“name”: i, “id”: i} for i in df.columns])
Thank you!
Hello @chozillla,
Welcome to the community!
What I do is have pandas run_sql_query directly, passing the connection as the second argument and then passing a list of params for passing variables to the query. This keeps from sql injection.
@jinnyzor I am a noob. Can you write it out for me? I know I have the dataframe successfully grabbed from the SQL server. And it is a Pandas dataframe. Are you talking about pandasql?
Ok, this is pretty weird. I ran the same code on MacOS and it ran fine…
@jinnyzor
from dash import Dash, dash_table
from databricks import sql
pip install databricks-sql-connector use this to install databricks
This works on MacOS but not on Windows?
app = Dash(name)
server = app.server
Set these as environment variables in Dash Enterprise or locally
SERVER_HOSTNAME = blah
HTTP_PATH = check
ACCESS_TOKEN = works
Configure according to your table and database names in Databricks
DB_NAME = “bronze_aupe”
TABLE_NAME = “df_aupe_global”
with sql.connect(
server_hostname=SERVER_HOSTNAME, http_path=HTTP_PATH, access_token=ACCESS_TOKEN
) as connection:
with connection.cursor() as cursor:
cursor.execute(f"SELECT * FROM {DB_NAME}.{TABLE_NAME} LIMIT 100")
df = cursor.fetchall_arrow()
df = df.to_pandas()
app.layout = dash_table.DataTable(
data=df.to_dict(‘records’),
columns=[{“name”: i, “id”: i} for i in df.columns]
)
if name == ‘main’:
app.run(debug=True)
Very strange.
Did you compare your libraries between the two?
This is for my MacOS
adjustText 0.8
alabaster 0.7.12
alembic 1.11.1
anaconda-client 1.11.0
anaconda-navigator 2.3.2
anaconda-project 0.11.1
anyio 3.5.0
appdirs 1.4.4
applaunchservices 0.3.0
appnope 0.1.2
appscript 1.1.2
argon2-cffi 21.3.0
argon2-cffi-bindings 21.2.0
arrow 1.2.3
asn1crypto 1.5.1
astroid 2.14.2
astropy 5.1
atomicwrites 1.4.0
attrs 22.1.0
Automat 20.2.0
autopep8 1.6.0
Babel 2.11.0
backcall 0.2.0
backports.functools-lru-cache 1.6.4
backports.tempfile 1.0
backports.weakref 1.0.post1
bcrypt 3.2.0
beautifulsoup4 4.11.1
binaryornot 0.4.4
bitarray 2.5.1
bkcharts 0.2
black 22.6.0
bleach 4.1.0
bokeh 2.4.3
boto3 1.24.28
botocore 1.27.59
Bottleneck 1.3.5
Brotli 1.0.9
brotlipy 0.7.0
certifi 2022.12.7
cffi 1.15.1
chardet 4.0.0
charset-normalizer 2.0.4
click 8.0.4
cloudpickle 2.0.0
clyent 1.2.2
colorama 0.4.6
colorcet 3.0.1
colourmap 1.1.10
conda 23.1.0
conda-build 3.22.0
conda-content-trust 0.1.3
conda-pack 0.6.0
conda-package-handling 2.0.2
conda_package_streaming 0.7.0
conda-repo-cli 1.0.20
conda-token 0.4.0
conda-verify 3.4.2
constantly 15.1.0
cookiecutter 1.7.3
cryptography 38.0.4
cssselect 1.1.0
cycler 0.11.0
Cython 0.29.33
cytoolz 0.12.0
daal4py 2021.6.0
dacite 1.8.1
dash 2.8.1
dask 2022.7.0
databricks 0.2
databricks-sql-connector 2.8.0
datashader 0.14.1
datashape 0.5.4
debugpy 1.5.1
decorator 5.1.1
defusedxml 0.7.1
diff-match-patch 20200713
dill 0.3.6
distributed 2022.7.0
docutils 0.18.1
entrypoints 0.4
et-xmlfile 1.1.0
fastjsonschema 2.16.2
filelock 3.9.0
flake8 4.0.1
Flask 1.1.2
Flask-Compress 1.13
flit_core 3.6.0
fonttools 4.25.0
fqdn 1.5.1
fsspec 2022.11.0
future 0.18.2
gensim 4.3.0
glob2 0.7
gmpy2 2.1.2
greenlet 2.0.1
h5py 3.7.0
HeapDict 1.0.1
holoviews 1.15.0
htmlmin 0.1.12
hvplot 0.8.0
hyperlink 21.0.0
idna 3.4
imagecodecs 2021.8.26
ImageHash 4.3.1
imageio 2.19.3
imagesize 1.4.1
importlib-metadata 4.11.3
incremental 21.3.0
inflection 0.5.1
iniconfig 1.1.1
intake 0.6.5
intervaltree 3.1.0
ipykernel 6.15.2
ipython 7.31.1
ipython-genutils 0.2.0
ipywidgets 7.6.5
isoduration 20.11.0
isort 5.9.3
itemadapter 0.3.0
itemloaders 1.0.4
itsdangerous 2.0.1
jdcal 1.4.1
jedi 0.18.1
jellyfish 0.9.0
Jinja2 2.11.3
jinja2-time 0.2.0
jmespath 0.10.0
joblib 1.1.1
json5 0.9.6
jsonpointer 2.3
jsonschema 4.17.3
jupyter 1.0.0
jupyter_client 8.0.3
jupyter-console 6.4.3
jupyter_core 5.2.0
jupyter-events 0.6.3
jupyter_server 2.3.0
jupyter_server_terminals 0.4.4
jupyterlab 3.4.4
jupyterlab-link-share 0.2.5
jupyterlab-pygments 0.1.2
jupyterlab-server 2.10.3
jupyterlab-widgets 1.0.0
kaleido 0.2.1
keyring 23.4.0
kiwisolver 1.4.4
lazy-object-proxy 1.6.0
libarchive-c 2.9
llvmlite 0.39.1
locket 1.0.0
lxml 4.9.1
lz4 4.3.2
Mako 1.2.4
Markdown 3.4.1
MarkupSafe 2.0.1
matplotlib 3.5.2
matplotlib-inline 0.1.6
mccabe 0.7.0
mistune 0.8.4
mkl-fft 1.3.1
mkl-random 1.2.2
mkl-service 2.4.0
mock 4.0.3
mpmath 1.2.1
msgpack 1.0.3
multimethod 1.9.1
multipledispatch 0.6.0
munkres 1.1.4
mypy-extensions 0.4.3
navigator-updater 0.3.0
nbclassic 0.3.5
nbclient 0.5.13
nbconvert 6.4.4
nbformat 5.5.0
nest-asyncio 1.5.6
networkx 2.8.4
nltk 3.7
nose 1.3.7
notebook 6.4.12
numba 0.56.4
numexpr 2.8.4
numpy 1.21.5
numpydoc 1.5.0
oauthlib 3.2.2
olefile 0.46
openpyxl 3.0.10
oscrypto 1.3.0
packaging 22.0
pandas 1.4.4
pandocfilters 1.5.0
panel 0.13.1
param 1.12.3
parsel 1.6.0
parso 0.8.3
partd 1.2.0
pathlib 1.0.1
pathspec 0.10.3
patsy 0.5.3
pca 1.9.1
pep8 1.7.1
pexpect 4.8.0
phik 0.12.3
pickleshare 0.7.5
Pillow 9.3.0
pip 22.3.1
pkginfo 1.8.3
platformdirs 2.5.2
plotly 5.9.0
pluggy 1.0.0
ply 3.11
pooch 1.4.0
poyo 0.5.0
prometheus-client 0.14.1
prompt-toolkit 3.0.36
Protego 0.1.16
psutil 5.9.0
ptyprocess 0.7.0
py 1.11.0
pyarrow 12.0.1
pyasn1 0.4.8
pyasn1-modules 0.2.8
pycodestyle 2.8.0
pycosat 0.6.4
pycparser 2.21
pycryptodomex 3.17
pyct 0.5.0
pycurl 7.45.1
pydantic 1.10.11
PyDispatcher 2.0.5
pydocstyle 6.3.0
pyerfa 2.0.0
pyflakes 2.4.0
Pygments 2.11.2
PyHamcrest 2.0.2
PyJWT 2.4.0
pylint 2.16.2
pyls-spyder 0.4.0
pyobjc-core 8.5
pyobjc-framework-Cocoa 8.5
pyobjc-framework-CoreServices 8.5
pyobjc-framework-FSEvents 8.5
pyodbc 4.0.34
pyOpenSSL 22.0.0
pyparsing 3.0.9
PyQt5-sip 12.11.0
pyrsistent 0.18.0
PySocks 1.7.1
pytest 7.1.2
python-dateutil 2.8.2
python-json-logger 2.0.6
python-lsp-black 1.2.1
python-lsp-jsonrpc 1.0.0
python-lsp-server 1.5.0
python-slugify 5.0.2
python-snappy 0.6.1
pytoolconfig 1.2.5
pytz 2022.7
pyviz-comms 2.0.2
PyWavelets 1.4.1
PyYAML 6.0
pyzmq 25.0.0
QDarkStyle 3.0.2
qstylizer 0.2.2
QtAwesome 1.2.2
qtconsole 5.3.2
QtPy 2.2.0
queuelib 1.5.0
regex 2022.7.9
requests 2.28.1
requests-file 1.5.1
rfc3339-validator 0.1.4
rfc3986-validator 0.1.1
rope 1.7.0
Rtree 1.0.1
ruamel.yaml 0.17.21
ruamel.yaml.clib 0.2.6
ruamel-yaml-conda 0.17.21
s3transfer 0.6.0
scatterd 1.2.5
scikit-image 0.19.3
scikit-learn 1.0.2
scikit-learn-intelex 2021.20221004.121333
scipy 1.10.0
Scrapy 2.6.2
seaborn 0.11.2
Send2Trash 1.8.0
service-identity 18.1.0
setuptools 65.6.3
sip 6.6.2
six 1.16.0
smart-open 5.2.1
sniffio 1.2.0
snowballstemmer 2.2.0
snowflake-connector-python 3.0.0
sortedcollections 2.1.0
sortedcontainers 2.4.0
soupsieve 2.3.2.post1
Sphinx 5.0.2
sphinxcontrib-applehelp 1.0.2
sphinxcontrib-devhelp 1.0.2
sphinxcontrib-htmlhelp 2.0.0
sphinxcontrib-jsmath 1.0.1
sphinxcontrib-qthelp 1.0.3
sphinxcontrib-serializinghtml 1.1.5
spyder 5.3.3
spyder-kernels 2.3.3
SQLAlchemy 1.4.39
statsmodels 0.13.5
sympy 1.11.1
tables 3.7.0
tabulate 0.8.10
tangled-up-in-unicode 0.2.0
TBB 0.2
tblib 1.7.0
tenacity 8.0.1
terminado 0.13.1
testpath 0.6.0
text-unidecode 1.3
textdistance 4.2.1
threadpoolctl 2.2.0
three-merge 0.1.1
thrift 0.16.0
tifffile 2021.7.2
tinycss 0.4
tinycss2 1.2.1
tldextract 3.2.0
toml 0.10.2
tomli 2.0.1
tomlkit 0.11.1
toolz 0.12.0
tornado 6.2
tqdm 4.64.1
traitlets 5.9.0
Twisted 22.2.0
typeguard 2.13.3
typing_extensions 4.4.0
ujson 5.4.0
Unidecode 1.2.0
uri-template 1.2.0
urllib3 1.26.14
visions 0.7.5
w3lib 1.21.0
watchdog 2.1.6
wcwidth 0.2.5
webcolors 1.12
webencodings 0.5.1
websocket-client 0.58.0
Werkzeug 2.0.3
wget 3.2
whatthepatch 1.0.2
wheel 0.38.4
widgetsnbextension 3.5.2
wordcloud 1.9.2
wrapt 1.14.1
wurlitzer 3.0.2
xarray 2022.11.0
xlrd 2.0.1
XlsxWriter 3.0.3
xlwings 0.27.15
yapf 0.31.0
ydata-profiling 4.3.1
zict 2.1.0
zipp 3.11.0
zope.interface 5.4.0
zstandard 0.18.0
