Improve error handling for unsupported object types

[LukeWoodward]

If you pass pypyodbc a value of some type it doesn't know how to convert, the end result is an error message which doesn't give you much information about what you've done wrong.

Take for example the following code:

import pypyodbc as pyodbc

class SomeClass(object): pass

obj = SomeClass()

cnxn = pyodbc.connect('....')
cursor = cnxn.cursor()
cursor.execute("INSERT INTO some_table (a) VALUES (?)", [obj])

When I run this code, it generates the following traceback:

Traceback (most recent call last):
  File "C:\Users\Luke\StackOverflow\pypyodbctest.py", line 9, in <module>
    cursor.execute("INSERT INTO some_table (a) VALUES (?)", [obj])
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1470, in execute
    self._BindParams(param_types)
  File "C:\Python27\lib\site-packages\pypyodbc.py", line 1275, in _BindParams
    if param_types[col_num][0] == 'u':
TypeError: 'type' object has no attribute '__getitem__'

From this message alone it can be difficult to figure out what has been done wrong.

In my sample code I can fully expect that there will be a problem, as I haven't specified anything about how a SomeClass instance should be converted into a SQL datatype. However, the same error message appears in this StackOverflow question where it was less clear to the questioner what their mistake was.

[braian87b]

It says that you should pass an object with __ getitem __... Are you passing a string, boolean, integer, float? or something else?

[LukeWoodward]

@braian87b: I'm not sure we're quite understanding one another here.

That exception was thrown inside pypyodbc. I am not in control of param_types, so I'm not directly passing anything into the line from where the exception is raised.

Digging a bit further, it seems the problem may be in the get_type function. In most cases this returns a tuple, but if you pass it some object it doesn't recognise, such as an instance of my class SomeClass, then it returns the type of that object instead.

The _BindParams method appears to assume that all elements in the param_types list are tuples. If, however, one of the parameters was an unrecognised object, one entry in the param_types list will be a type object rather than a tuple, and the _BindParams method will then choke on that when it tries to find out if the first element of the tuple it thinks it has is 'u'.

I don't know what the best way to fix this issue is. I also appreciate it's not a particularly huge issue, as it doesn't affect the 'happy path' and only relates to error-handling. However, had there been better error-handling here, the StackOverflow question I linked to above might not have been necessary.

[braian87b]

@LukeWoodward sorry for misunderstood you. As far I currently understand your problem, again, I recommend you, you should ensure you pass parameters as tuple: cursor.execute("INSERT INTO some_table (a) VALUES (?)", (obj, )) or add many as: cursor.execute("INSERT INTO some_table (a) VALUES (?)", (obj, ob2)) you are just passing a obj: cursor.execute("INSERT INTO some_table (a) VALUES (?)", obj) you actually said: cursor.execute("INSERT INTO some_table (a) VALUES (?)", [obj]) but if you pass a variable inside brackets you are passing a list of one element with that element pointing to that variable. (I think you actually wanted to highlight in someway that variable, it was not necessary, it was defined a few lines before in your code snippet)

In order to this humble library function properly you must pass parameters as a tuple of python primitive objects (string, boolean, integer, float, etc).

When you pass a list of object (class definition by you) or something else you are actually dealing with Python Language and Syntax issues and you should write necessary def's to allow them work transparently returning a tuple or returning a native object (then enclose in a tuple).

Please be aware that pypyodbc are not exactly the same as pyodbc (the last one has C bindings)

I updated README.md to include this tuple parameter example to avoid future confusions.

Sorry we can't help much, try on StackOverflow again.

Anything else just let us know.

[LukeWoodward]

@braian87b: it seems we still are not quite understanding one another.

For one thing, if I replace the one-element list [obj] with a 1-tuple (obj,) I get exactly the same error message. It is clear from reading the code of the execute method it makes no difference whether you pass the parameters as a list or a tuple. If there is a difference, and we are supposed to only pass in a tuple, then why does an error message in the code suggest that we can pass in a list?

I can understand that I must pass primitive values as parameters to execute. However, as demonstrated in the StackOverflow question I linked to above, others may unintentionally pass in unsuitable values. In that situation, pypyodbc makes it difficult to figure out what has gone wrong. The user hasn't passed in a type object anywhere, yet they get a confusing error message about a type object not having a __getitem__ method. In order to answer the question I needed to read the pypyodbc source to figure out where the type object had come from. I don't see why I should have needed to do that.

I don't see why you can't simply replace line 1143,

    return type(v)

with a line that simply raises an exception complaining that the value v cannot be used as a parameter because its type is unsupported. That would address the problem satisfactorily.

[braian87b]

@LukeWoodward Sorry, I actually didn't see that part of the sourcecode nor the main reason of the issue. Then, the main problem is that the library should warn when parameter is an invalid type? If you succed on adding a proper error handling, please share it. You are welcome to submit a pull request anytime.

[keshavnagpal]

i'm having a similar error

 File "C:\Users\Keshav\Anaconda3\lib\site-packages\pypyodbc.py", line 1296, in _BindParams
    if param_types[col_num][0] == 'u':
TypeError: 'type' object is not subscriptable

whenever i try to insert some values like

values = [1,2,3]
insert = "insert into tableName(col1,col2,col3) values(?,?,?)"
c.execute(insert,values)

but if i replace the ?,?,? with values directly in the sql statement then no error with the same values and same sql just passing values directly in sql query.

[LukeWoodward]

@keshavnagpal: the only thing I can suggest is that you edit the pypyodbc.py file, find the line return type(v) within the get_type function and replace it with something like the following:

    raise TypeError("Value {0} of type {1} is not supported".format(v, type(v)))

That should improve the error handling and tell you the type of the value you are passing in that is causing the problem.

[keshavnagpal]

@LukeWoodward : Thanks a lot!

edit the pypyodbc.py file, find the line return type(v) within the get_type function and replace it with something like the following:

raise TypeError("Value {0} of type {1} is not supported".format(v, type(v))

This worked