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(at)thebuild(dot)com> wrote:
>
>>
>> On Feb 2, 2016, at 10:04 AM, Karl O. Pinc <kop(at)meme(dot)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(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