Re: A PGsendQuery API design for your review

On 02/02/2016 10:34 AM, Karl O. Pinc wrote:
> On Tue, 2 Feb 2016 10:15:40 -0800
> Christophe Pettus <xof@thebuild.com> wrote:
>
>>
>> On Feb 2, 2016, at 10:04 AM, Karl O. Pinc <kop@meme.com> wrote:
>>
>>> OHow?  In order to submit multiple statements to libpq
>>> and get back results for all of them PGsendQuery() must
>>> be called.
>>
>> The same way the other clients do it; split the text into queries and
>> send them over.
>
> That requires my application contain an SQL parser.  This seems
> onerous.  The whole point is that I'm getting unfiltered SQL directly
> from a user.  Multiple statements.  "Splitting the text into queries"
> is non-trivial.

How about using the statement terminator and doing:

sql_str = 'select * from cell_per; select count(*) from cell_per;'

sql_str.split(';')
['select * from cell_per', ' select count(*) from cell_per', '']

for sql in sql_str.split(';'):
     if sql:
         cur.execute(sql)
         rs = cur.fetchall()
         print rs

>
> I can call libpq directly.  First PQsendQuery(), then repeatedly
> call PQgetResult() (optionally calling PQgetSingleRowMode().
> But I like the psycopg2 API.  Seems like it ought to be able
> to make these calls for me.
>
> By the by.  The other clients probably don't split the text
> into queries.  psql does, but to do it it uses the SQL
> parser internal to pg.  phpPgAdmin does not.  And I don't
> know what pgAdmin does.  My point in mentioning these interfaces
> is that they all allow the user to submit arbitrary sql.
> And the only one that does it "right" is psql, and it's
> got "special access" to a SQL parser.
>
> psql is the only client
> I know of that delivers query results when multiple queries
> are supplied (on stdin, say).  I want to be able to write
> a similar application.
>
> (Thanks for taking your time to look at this by the way.)
>
> (And, oops.  The "buffered" argument must be to the
> submitquery() method in my design.)
>
> Regards,
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                   -- Robert A. Heinlein
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com