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

[Vernon Cole]

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 my
pattern. It includes the following snippets of code:

<code snippet 1>
import re
P_IS_QUERY = re.compile('^[ \r\n]*SELECT ',re.IGNORECASE)

<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?

It also seems to me that I should be able to detect a query by the fact that
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.
--
Vernon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20100802/4873bc1a/attachment.html>