programming error with pyodbc

Question

I am attempting to query a SQL Server 2012 database using the following code:

import pyodbc

class sqlserverConnector:
    def __init__(self, connectionString):

        """
        this is a typical connection string using windows authentication and the DSN manager:

            'DSN=python;Trusted_Connection=yes'
        """
        self._conn = pyodbc.connect(connectionString)
        self._curs = self._conn.cursor()
        self.fetchall = self._curs.fetchall
        self.description = self._curs.description
        self.columns = dict()

    def __del__(self):
        self._conn.close()

    def __iter__(self):
        return self._curs.__iter__()

    # executes SQL statements
    def execute(self, statement, **params):
        if params is None:
            self._curs.execute(statement)
        else:
            self._curs.execute(statement,params)

        # creates a dictionary of column names and positions
        if self._curs.description != None:
            self.columns = dict((field[0], pos) for pos, field in enumerate(self._curs.description))
        else:
            None

And:

from sqlutil import *

sqlcnxn = sqlserverConnector('DSN=python;Trusted_Connection=yes')
rows = sqlcnxn.execute("select * from demographics", params=None)

for row in rows:
    print row
    break

The goal is to print out a single row (the table has 80k+ rows). However I always get this error message:

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

I have googled around and it seems like this pops up for different people for different reasons and none of the solutions I have found fit my error. I think what is happening is that the execute method is defaulting to the first else statement instead of the first if statement.

Martijn Pieters · Accepted Answer · 2013-11-27 17:28:34Z

2

When you use the **params notation, then params is always a dictionary.

Calling that function with params=None means you now have a dictionary with:

>>> def func(**params):
...     print params
... 
>>> func()
{}
>>> func(params=None)
{'params': None}
>>> func(foo='bar')
{'foo': 'bar'}

The syntax is meant to accept arbitrary keyword parameters, illustrated by the foo keyword argument above.

Either remove the ** or test for an empty dictionary, and don't set params=None when calling .execute():

def execute(self, statement, **params):
    if not params:
        self._curs.execute(statement)
    else:
        self._curs.execute(statement, params)
    # ...

and:

rows = sqlcnxn.execute("select * from demographics")

Note that your execute() function has no return statement, which means that rows will be set to None (the default return value for functions). Add return self if you meant to return the connection object so that it can be iterated over.

edited Nov 27, 2013 at 17:28

answered Nov 27, 2013 at 14:06

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user845888 Over a year ago

I did not know that it always created a dictionary. Thank you. A follow up question, when I test for an empty dictionary like you recommend rows becomes None. Why is this?

Martijn Pieters Over a year ago

@mnky9800n: Your function doesn't return anything; you forgot to return self at the end.

Collectives™ on Stack Overflow

programming error with pyodbc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related