[IronPython] Fwd: [DB-SIG] How can I reliably detect whether an SQL statement is a Query?
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?
---------- 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
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
> 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
> return False
> <code snippet 2B>
> if self._is_query(operation):
> self.reader = command.ExecuteReader()
> self.description = None
> 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:
> def is_query(operation):
> return operation.split(' ').upcase in ['SELECT','WITH']
> 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...
More information about the Ironpython-users