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(at)dndg(dot)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(at)meme(dot)com>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com