[IronPython] Fwd: [DB-SIG] How can I reliably detect whether an SQL statement is a Query?

Vernon Cole vernondcole at gmail.com
Tue Aug 3 01:24:08 CEST 2010

Dear Iron Python people:

Earlier today I sent a question out the the Python DB-SIG.

I  got the following response, and I think Andy's last question
(highlightedbelow) is a good one.

What *would* happen?

Opinions, please.

---------- Forwarded message ----------
From: Andy Dustman <farcepest at gmail.com>
Date: Mon, Aug 2, 2010 at 7:55 AM
Subject: Re: [DB-SIG] How can I reliably detect whether an SQL statement is
a Query?
To: Vernon Cole <vernondcole at gmail.com>
Cc: "DB-SIG @ Python.org" <db-sig at python.org>

On Mon, Aug 2, 2010 at 5:57 AM, Vernon Cole <vernondcole at gmail.com> wrote:
> Dear Gurus:
> Please give your advice up front to help me avoid making a design error. I
> am asking for help because:
> 1) I am not confident in my ability to understand Regular Expression
> strings.
> 2) I do not know much about any dialect of SQL other than Microsoft T-SQL
> (and sometime precious little of that.)
> I am ready for the next step in development of adodbapi, which is to use
> real ADO.NET (rather than COM ADO-db) when running on Iron Python.
> My research indicates that, when using ADO.NET, one must choose to call
> either  an ExecuteReader() method, or an  ExecuteNonQuery() method.
> I am attempting to use a lightweight db-api implementation from FePy for
> pattern. It includes the following snippets of code:
> <code snippet 1>
> import re
> P_IS_QUERY = re.compile('^[ \r\n]*SELECT ',re.IGNORECASE)

A slightly better expression would be ^\w*SELECT

\w matches whitespace.

> <code snippet 2>
> class Cursor(object):
> <code snippet 2A>
>     def _is_query(self, operation):
>         '''Identify whether an operation is a query or not'''
>         if P_IS_QUERY.match(operation):
>             return True
>         else:
>             return False
> <code snippet 2B>
>         if self._is_query(operation):
>             self.reader = command.ExecuteReader()
>             self._set_description()
>         else:
>             command.ExecuteNonQuery()
>             self.description = None
> </code>
> It seems to me that this code could be confused by the substring 'SELECT'
> being included as part of a longer string, or in a string literal. Am
> reading it wrong?

The way your expression is written, it only matches SELECT at the
beginning of the line (after any whitespace).

> It also seems to me that I should be able to detect a query by the fact
> the first token in the command will be either 'SELECT' or 'WITH, but would
> that still be true for other dialects of SQL?'
> I am thinking of using something like:
> <code>
> def is_query(operation):
>     return operation.split(' ')[0].upcase in ['SELECT','WITH']
> </code>
> Good idea, or Bad idea?
> Any comments appreciated.

What are the consequences of using ExecuteReader() when there is
nothing to read? If none, i.e. you get an empty set of results, then I
would say to use that all the time, and don't bother to examine your
SQL at all.
Question the answers
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/ironpython-users/attachments/20100802/954c9a41/attachment.html>

More information about the Ironpython-users mailing list