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(at)gmail(dot)com
> <mailto:haramrae(at)gmail(dot)com>> wrote:
>
> On 07 Sep 2014, at 10:45, Abelard Hoffman <abelardhoffman(at)gmail(dot)com
> <mailto:abelardhoffman(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com