Re: pyodbc - problem passing None as parameter

Discussion in 'Python' started by Tim Golden, Oct 23, 2009.

  1. Tim Golden

    Tim Golden Guest

    Frank Millman wrote:
    > Tim Golden wrote:
    >> Frank Millman wrote:
    >>>>>> cur.execute('select * from ctrl.dirusers where todate is ?', None)
    >>> Traceback (most recent call last):
    >>> File "<stdin>", line 1, in <module> pyodbc.ProgrammingError: ('42000',
    >>> "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
    >>> near @P1'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server
    >>> Driver][SQL Server]Statement(s) could not be prepared. (8180)")

    >> I would estimate that it's because you're using
    >> "where todate is ?" in your WHERE clause, which
    >> can only possibly be followed by a NULL -- thus making
    >> it a not-meaningfully parameterisable query.

    > Thanks for the response, Tim.
    > Why do you say that this is not-meaningfully parameterisable?
    > I want the final WHERE clause to show 'WHERE todate IS NULL'.

    Of course, I understand that. What I mean is that if a piece
    of SQL say:

    WHERE table.column IS ?

    then the only possible (meaningful) value ? can have is
    NULL (or None, in python-speak). In fact, the IS NULL is
    really a sort of unary operator-expression, not an operator
    with a value.

    As I'm sure you're aware, ANSI treatment of NULLs demands
    that neither:

    WHERE table.column = NULL


    WHERE table.column <> NULL

    will succeed, regardless of the value of table.column.

    You might just have expected that some combination of an
    RDBMS and its driver (ODBC or otherwise) and the Python
    dbapi module could get their respective acts together and
    -- when faced with a piece of parsed SQL like this:

    WHERE table.column = ?

    -- could translate that under the covers into:

    WHERE table.column IS ?

    when the parameter translates to NULL.

    But pyodbc / mssql doesn't seem to do that, even if psycopg2
    does. As I mentioned before, there doesn't seem to be anything
    in the dbapi PEP which mandates or forbids such behaviour, so
    I suppose it's implementation-dependent.

    > As I showed in my first example, pyodbc has no problem converting "'select
    > ?', None" into "select NULL". I don't see why this should be any different.

    I think I answered that above re semantics of comparing to NULL.
    Also, note that pyodbc isn't itself string-processing your query;
    it's calling several layers of db driver to ask MSSQL to do that,
    given a combination of parameterised SQL and parameter values.

    > For the record, psycopg2 on Postgresql has no problem with this.
    > As a workaround, I suppose I could scan the argument list, and if I find a
    > None, substitute the ? with NULL in the SQL statement itself.

    Well, the code I posted previously, although tedious taken to
    extremes, will do that. (I have seen and used code like that in
    a number of production systems).

    > It would be interesting to view the SQL statement that pyodbc passes to SQL
    > Server for execution. Does anyone know if it is possible to set a parameter
    > anywhere to enable this?

    See my comment above about what pyodbc doesn't do to your SQL.
    There's a bit of an explanation here, from the Microsoft end
    of things:

    but this is not an MS-specific concept. It's how most (all?)
    database engines work.

    Tim Golden, Oct 23, 2009
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frank Millman

    pyodbc - problem passing None as parameter

    Frank Millman, Oct 22, 2009, in forum: Python
    Frank Millman
    Oct 22, 2009
  2. Tim Golden
    Tim Golden
    Oct 22, 2009
  3. length power
    Rustom Mody
    Apr 10, 2014
  4. Skip Montanaro
    Skip Montanaro
    Apr 10, 2014
  5. Johannes Schneider

    Re: why i have the output of [None, None, None]

    Johannes Schneider, Apr 10, 2014, in forum: Python
    Johannes Schneider
    Apr 10, 2014

Share This Page