Re: psql and tab-delimited output

On 09/08/2014 11:45 AM, Abelard Hoffman wrote:
> Hi Alban.
>
> On Sun, Sep 7, 2014 at 4:18 AM, Alban Hertroys <haramrae@gmail.com
> <mailto:haramrae@gmail.com>> wrote:
>
>     On 07 Sep 2014, at 10:45, Abelard Hoffman <abelardhoffman@gmail.com
>     <mailto:abelardhoffman@gmail.com>> wrote:
>
>     > For reports, everyone else mostly uses other tools? I'd like to stay away from GUI-tools, if possible.
>
>     For reporting, usually you use the data in the database directly.
>
>     A TSV or CSV file is not a report, it’s at best a data source for
>     your report. Going through an intermediate format is not a
>     particularly effective approach to create reports, but if you have
>     to (for example because you aren’t _allowed_ access to the
>     database), generally preferred formats seem to be CSV, XML or JSON;
>     as long as it’s a standard format.
>     TSV is not a common choice. Are you sure your boss actually cares
>     that it’s TSV and not, for example, CSV?
>
>
> Could you expand on that a bit? What sort of tools does management use
> to generate reports from the database directly?
> You're meaning a database warehouse? We just have an OLTP db, so we've
> always generated reports periodically through cron jobs.
>
> Or maybe "reports" is the wrong word. We generate a bunch of db stats
> which can then be used however they want (pulled into Excel, etc.).
> But would definitely be interested in learning about other approaches.

Ways I have done it:

1) In Excel use the data tools to run the query and return data directly
to the spreadsheet. This assumes a version of Excel that has the data tools.

2) I use Python, so use psycopg2 to pull data from the database and then
xlwt to write out a spreadsheet or reportlab to create a pdf or the csv
module to create whatever flavor of CSV.

>
> And yes, I'm sure we could convert everything over to CSV. Just an issue
> of inertia.
>
> Thanks.
>


--
Adrian Klaver
adrian.klaver@aklaver.com