Dear Iron Python people:<br><br>Earlier today I sent a question out the the Python DB-SIG.<br><br>I got the following response, and I think Andy's last question (<span style="background-color: rgb(255, 255, 51);">highlighted</span> below) is a good one.<br>
<br>What <b>would</b> happen? <br><br>Opinions, please.<br>--<br>Vernon<br><br><div class="gmail_quote">---------- Forwarded message ----------<br>From: <b class="gmail_sendername">Andy Dustman</b> <span dir="ltr"><<a href="mailto:farcepest@gmail.com">farcepest@gmail.com</a>></span><br>
Date: Mon, Aug 2, 2010 at 7:55 AM<br>Subject: Re: [DB-SIG] How can I reliably detect whether an SQL statement is a Query?<br>To: Vernon Cole <<a href="mailto:vernondcole@gmail.com">vernondcole@gmail.com</a>><br>Cc: "DB-SIG @ Python.org" <<a href="mailto:db-sig@python.org">db-sig@python.org</a>><br>
<br><br><div class="im">On Mon, Aug 2, 2010 at 5:57 AM, Vernon Cole <<a href="mailto:vernondcole@gmail.com">vernondcole@gmail.com</a>> wrote:<br>
> Dear Gurus:<br>
><br>
> Please give your advice up front to help me avoid making a design error. I<br>
> am asking for help because:<br>
> 1) I am not confident in my ability to understand Regular Expression<br>
> strings.<br>
> 2) I do not know much about any dialect of SQL other than Microsoft T-SQL<br>
> (and sometime precious little of that.)<br>
><br>
> I am ready for the next step in development of adodbapi, which is to use<br>
> real <a href="http://ADO.NET" target="_blank">ADO.NET</a> (rather than COM ADO-db) when running on Iron Python.<br>
><br>
> My research indicates that, when using <a href="http://ADO.NET" target="_blank">ADO.NET</a>, one must choose to call<br>
> either an ExecuteReader() method, or an ExecuteNonQuery() method.<br>
><br>
> I am attempting to use a lightweight db-api implementation from FePy for my<br>
> pattern. It includes the following snippets of code:<br>
><br>
> <code snippet 1><br>
> import re<br>
> P_IS_QUERY = re.compile('^[ \r\n]*SELECT ',re.IGNORECASE)<br>
<br>
</div>A slightly better expression would be ^\w*SELECT<br>
<br>
\w matches whitespace.<br>
<div class="im"><br>
> <code snippet 2><br>
> class Cursor(object):<br>
><br>
> <code snippet 2A><br>
> def _is_query(self, operation):<br>
> '''Identify whether an operation is a query or not'''<br>
> if P_IS_QUERY.match(operation):<br>
> return True<br>
> else:<br>
> return False<br>
><br>
> <code snippet 2B><br>
> if self._is_query(operation):<br>
> self.reader = command.ExecuteReader()<br>
> self._set_description()<br>
> else:<br>
> command.ExecuteNonQuery()<br>
> self.description = None<br>
> </code><br>
><br>
> It seems to me that this code could be confused by the substring 'SELECT'<br>
> being included as part of a longer string, or in a string literal. Am<br>
> reading it wrong?<br>
<br>
</div>The way your expression is written, it only matches SELECT at the<br>
beginning of the line (after any whitespace).<br>
<div class="im"><br>
> It also seems to me that I should be able to detect a query by the fact that<br>
> the first token in the command will be either 'SELECT' or 'WITH, but would<br>
> that still be true for other dialects of SQL?'<br>
><br>
> I am thinking of using something like:<br>
> <code><br>
> def is_query(operation):<br>
> return operation.split(' ')[0].upcase in ['SELECT','WITH']<br>
> </code><br>
><br>
> Good idea, or Bad idea?<br>
><br>
> Any comments appreciated.<br>
<br>
</div><span style="background-color: rgb(255, 255, 0);">What are the consequences of using ExecuteReader() when there is</span><br style="background-color: rgb(255, 255, 0);"><span style="background-color: rgb(255, 255, 0);">
nothing to read? If none, i.e. you get an empty set of results, then I</span><br style="background-color: rgb(255, 255, 0);"><span style="background-color: rgb(255, 255, 0);">
would say to use that all the time, and don't bother to examine your</span><br style="background-color: rgb(255, 255, 0);"><span style="background-color: rgb(255, 255, 0);">
SQL at all.</span><br>
<font color="#888888">--<br>
Question the answers<br>
</font></div><br>