How to provide SSL details in the ddf.to_sql function?

Dask DataFrame
1

I have just started exploring the dask dataframes. My use-case is fairly simple as it involves reading data from a database table, transforming the data, and then loading the transformed data into another table.
The database I am connecting to requires SSL. The function, dask.dataframe.read_sql_table, provides me with a parameter engine_kwargs which can be used to provide the necessary details for SSL and hence successfully connects and reads data from the database into a dataframe. However, the function, dask.dataframe.to_sql, does not have the option to provide this parameter. Due to this reason, I am unable to establish a connection with the database and subsequently unable to write my dask dataframe into the database.

Kindly help me with the alternatives for the same. Thanks in advance.

2

Hi @10PriyaA and welcome! Thanks for your question, I’m still working on this, but will follow-up soon!

1 Like
3

I think I found a solution-- have you tried passing the SSL details via the uri argument? Looking at these SQLAlchemy docs, you can pass in the SSL arguments directly into the URI string, which can then be passed into the uri argument in dask.dataframe.to_sql. I wasn’t able to test this with an SSL encrypted database, but here’s a minimal example using the uri arg:

import pandas as pd
import dask.dataframe as dd
from sqlalchemy import create_engine

# create some fake data
df = pd.DataFrame([ {'i':i, 's':str(i)*2 } for i in range(4) ])
ddf = dd.from_pandas(df, npartitions=2)

# create a fake db
uri = 'sqlite:///my-sqlite.db'
create_engine(uri)

# upload, then read from test table
ddf.to_sql("test", uri=uri)
data = dd.read_sql_table(
    "test", uri=uri, npartitions=2, index_col='i'
)

One thing to note about this solution, is that your SSL details would have to exist somewhere for your workers to be able to access them.

I think adding engine_kwargs to dask.dataframe.to_sql would be a nice feature request, and I would encourage you to open up an issue!

2 Likes
4

Hi @scharlottej13 , thank you so much for your response. It solved my issue and I am able to establish a connection with the database now!

3 Likes
5

Glad this fixed your problem @10PriyaA! I also opened up an issue for this feature.

1 Like