Re: A PGsendQuery API design for your review

On 02/02/2016 12:04 PM, Karl O. Pinc wrote:
> On Tue, 02 Feb 2016 19:58:16 +0100
> Federico Di Gregorii <fog@dndg.it> wrote:
>
>> I can say with some confidence that we'll never implement such an
>> API. Even the
>> ux, i.e., accepting multi-queries from the user is wrong, imho. Let
>> the user
>> write single queries, organize them, exrcute them using bound
>> variables and NOT
>> mogrify() and then return the result. Anything less is lazy.
>
> I'm don't think you've a complete picture of my users, what
> they're doing, and why it is useful to submit multiple queries
> at once.
>
> I support these people:
> http://amboselibaboons.nd.edu/
> http://gombechimpanzees.org/
>
> Who use interfaces like:
> http://papio.biology.duke.edu/babasewiki/MyStartingPage
> https://gombemi.ccas.gwu.edu/gombemiwiki/Main_Page
>
> My users are scientists, from undergraduates to senior
> researchers and American Academy of Science members, with a wide
> variety of SQL expertise.  They have direct access to their
> databases, writing SQL to explore their data.  They want a
> simple, comprehensive interface.  (One that not only provides
> support for SQL but also allows the unsophisticated to create
> their own tables, etc.  Which is neither here nor there.)
>
> They repeatedly try different queries and compare results,
> iterating until they tease the data apart.  They might write a
> query, run it, modify it, and then run both the modified and the
> original version.  They want to see the results in a single
> window so they can compare.  They want to be able to edit and
> tweak either or both and hit a "resubmit" button and again review
> the results.
>
> Some are sophisticated SQL users; regardless most don't want to
> think about SQL.  It's easier for them to cut and paste the same
> query 3 times and tweak the SQL to get 3 results (adult males,
> adult females, juveniles) than to try to write a single query
> that does a GROUP BY.  But they do want to be able to see and
> compare the results of the 3 queries.
>
> They use SQL post-validate their data and check for sanity after
> a bulk data upload.  They have a file containing from a few to
> 10s of SQL queries that they cut and paste into a "SQL window"
> and run.  Usually many of the queries produce a single number as
> output.  Then they examine the output and see if the numbers add
> up.
>
> They use sets of SELECT INTO queries to copy, or pre-join, or
> summarize data in the master schema, moving it into their own
> schema for further analysis.  They again use cut-and-paste to
> replay these operations when there's new data from a fresh bulk
> data upload.  (They likewise have full permissions, within their
> own schemas, to create whatever tables they need to support their
> analysis.)

I think what they are looking for is this:

http://ipython.org/

and this:

http://pandas.pydata.org/

>
> Etc.
>
> Of course everything _could_ be done by submitting a single query
> at a time.  (Or an interface that requires such parsing by the
> end-user, and then runs them in bulk.  Or some sort of arbitrary
> file format that contains easily parse-able statement delimiters.
> Etc.)  But an interface that allows submission of multiple
> statements in a single go, and review of all the results at once,
> greatly simplifies their work flow.  (And it's nice that their
> interface has point-and-click table creation, deletion, etc.)
>
> I'm surprised (Shocked I say!  Shocked! :-) that you'd want to
> place arbitrary restrictions on the user interfaces that can be
> created with psycopg2.  And Sir!  I must take offense at the
> implication that my users are lazy.  They work hard to press the
> semicolon key to end their statements!  ;-)
>
> Seriously, the real world is complex.  If PG allows me to create
> a user interface where the SQL parsing into separate statements
> is done by PG then psycopg2 should let me do the same.  There's
> a reason you can pipe multiple SQL statements to psql without
> having to pre-parse them into separate statements.
>
> If you really want to reject the idea of accepting multiple
> queries from the user let me know and I'll not pursue the
> matter.
>
> Regards,
>
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com