Implement sql writer

[roll]

Overview

For now tabulator support only stream.save(format='csv') to csv format. It's pretty easy to implement sql writer just porting writers.csv.CSVWriter to writers.sql.SQLWrter.

What we're aiming for:

from tabulator import Stream

with Stream('data.xls', headers=1) as stream:
  stream.save('postgresql://user:pass@host:5432/database', table='excel_export')

And of course it will be a pretty cool and useful feature 👍

Plan

• port writers.csv.CSVWriter to writers.sql.SQLWrter
• register new writer in config.py
• add writing tests to tests.formats.sql
• mention writing ability in readme sql format section

[pwalsh]

@roll @akariv how about an SQL reader too? I thought a reader had already been discussed, but I can't find it. We need it for some Frictionless Data piloting work, and @danfowler has expressed interest in implementing it.

[akariv]

I think @roll already implemented it and it's in master.

…

On Tue, 23 May 2017 at 08:52 Paul Walsh ***@***.***> wrote: @roll <https://github.com/roll> @akariv <https://github.com/akariv> how about an SQL reader too? I thought a reader had already been discussed, but I can't find it. We need it for some Frictionless Data piloting work, and @danfowler <https://github.com/danfowler> has expressed interest in implementing it. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#164 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAQMde08u1tuzWu82H05EGmCd9iurqC1ks5r8nQjgaJpZM4NWZT2> .

[akariv]

https://github.com/frictionlessdata/tabulator-py/blob/master/tabulator/parsers/sql.py

[pwalsh]

@danfowler see above

[pwalsh]

thanks @roll and @akariv

[roll]

@pwalsh
@danfowler
@CallMeAlien
We now have readme withh all schemes, formats, options etc in details - https://github.com/frictionlessdata/tabulator-py/blob/master/README.md#sql

[danfowler]

@roll @akariv @CallMeAlien @pwalsh to clarify: for the DM4T pilot, one of the datasets (ENLITEN) was provided as a MySQL dump. Given that one of the goals of this DM4T project more generally is to "make your data public to the rest of the public and beyond" (and that publishing a SQL dump is not super friendly), I thought there might be value in going straight from a SQL database directly to a Data Package.

I initially tried to use jsontableschema-sql-py directly, but:

1. There were some issues with the conversion
2. SQL type support needs to be better (I manually dropped/edited some of the source tables to make it sort of work)

Given that the publisher of this kind of data would want do make some edits to the published Data Package (like dropping user tables, adding metadata, etc.) without needing to do much programming directly, I suppose what probably makes more sense is to do this with some higher level tool, like datapackage-pipelines where you can have an SQL connection as the source. I suppose what one would need to implement is a datapackage_pipelines_sql plugin. How long do you think that would take for a new person to the codebase @akariv?

[akariv]

@danfowler there's no need for a datapackage_pipelines_sql plugin, as now you can specify resource URLs which are SQL connection strings directly (using tabulator's built-in support)

[danfowler]

@akariv thanks! That helps so much with understanding how these pieces fit together 😄 .

/me rushing off to add some SQL connections strings to some YAML

[eyalhei]

Hi, I would like to take a crack at this, is that OK?

[akariv]

Go ahead @eyalhei !

[roll]

That's great @eyalhei

Please take a look at #273 (comment) (and this comment especially) to ensure that the issue is properly described (probably it wasn't for the JSON writer).

The test from the comment I linked could be easily updated to be a POC SQL writer test (round-trip using SQL as an intermediate format)

[roll]

DONE in #276