Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add engine_kwargs argument to dask.dataframe.to_sql #8596

Closed
scharlottej13 opened this issue Jan 20, 2022 · 3 comments · Fixed by #8609
Closed

Add engine_kwargs argument to dask.dataframe.to_sql #8596

scharlottej13 opened this issue Jan 20, 2022 · 3 comments · Fixed by #8609
Labels
dataframe good first issue Clearly described and easy to accomplish. Good for beginners to the project.

Comments

@scharlottej13
Copy link
Contributor

This question came up on the Dask Discourse. We were able to come up with a solution, but noticed the dask.dataframe.read_sql_table accepts an engine_kwargs argument while dask.dataframe.to_sql does not. Adding an engine_kwargs argument would be a nice feature that would make using to_sql less confusing.
@scharlottej13 scharlottej13 mentioned this issue Jan 20, 2022
Closed
@ian-r-rose ian-r-rose added dataframe good first issue Clearly described and easy to accomplish. Good for beginners to the project. labels Jan 20, 2022
@amirkdv
Copy link
Contributor

amirkdv commented Jan 21, 2022

@scharlottej13 I looked into this a bit and I think there are some design decisions to be made here.

dask.dataframe.to_sql delegates db-facing logic to pandas.DataFrame.to_sql through a con argument. This is expected to be a full sqlalchemy URI for the db connection, and is exposed by dask.dataframe.to_sql as an argument called uri.

If we were to expose engine_kwargs in dask.dataframe.to_sql, we'd have to make it behave consistently with the existing uri argument. To not break existing code to_sql(..., uri=..., ...) should keep working. But now we could make uri optional and expose a new engine_kwargs argument that creates a connection if uri is not provided.

Alternatively, to go back to the motivating issue, I think SSL configuration can be passed as the SQLAlchemy connection string (i.e. the already exposed uri). For example for Postgres the user could add ?sslmode=require to the connection URI they're already providing to dask.dataframe.to_sql. A similar feature exists for MySQL but with different syntax.

p.s: first time dask contributor, let me know if I'm missing something here!

@scharlottej13
Copy link
Contributor Author

Hi @amirkdv and welcome! Thanks for having a look at this issue. I think you bring up some great points about design consideration. Specifically:

If we were to expose engine_kwargs in dask.dataframe.to_sql, we'd have to make it behave consistently with the existing uri argument. To not break existing code to_sql(..., uri=..., ...) should keep working. But now we could make uri optional and expose a new engine_kwargs argument that creates a connection if uri is not provided.

This is exactly what I was thinking-- to make dask.dataframe.to_sql more symmetrical to dask.dataframe.read_sql_table, for clarity.

Alternatively, to go back to the motivating issue, I think SSL configuration can be passed as the SQLAlchemy connection string (i.e. the already exposed uri).

Yes! That was exactly what we suggested as a solution. This issue is more of a feature request than a bug issue, in that sense.

@amirkdv amirkdv mentioned this issue Jan 22, 2022
Merged
4 tasks
@amirkdv
Copy link
Contributor

amirkdv commented Jan 22, 2022

Thanks @scharlottej13! I hadn't look at the original Discourse page and had missed your comments re SSL, sorry about that!

I've opened a PR for this. I used a simpler echo=True as the use case. The SSL use case turned out to be a bit more complicated, see PR for details.

jsignell pushed a commit that referenced this issue Feb 1, 2022
@amirkdv
Add engine_kwargs support to dask.dataframe.to_sql (#8609)
80f9821 
### Use case: SSL

This was the original [use case] that motivated #8596: to force SSL on the db connection. Whether the new `engine_kwargs` argument is helpful/necessary for this use case depends on the db driver used by SQLAlchemy:

* [MySQL] (helpful): either use `connect_args={'ssl': ...}` as engine kwarg or add `?ssl_cert=...&ssl_key=...` to URI.
* [psycopg2] (not helpful): must use `?sslmode=require` in connection URI, not supported as engine argument.
* [pg8000] (necessary): must use `connect_args={'ssl_context': ...}` as engine kwarg.

[use case]: coiled/dask-community#186
[MySQL]: https://docs.sqlalchemy.org/en/14/dialects/mysql.html#ssl-connections
[psycopg2]: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#ssl-connections
[pg8000]: https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#pg8000-ssl
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
dataframe good first issue Clearly described and easy to accomplish. Good for beginners to the project.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Add engine_kwargs support to dask.dataframe.to_sql amirkdv/dask
3 participants
@amirkdv @ian-r-rose @scharlottej13