KeyError: tinyint when autoloading a table from a MS SQL database

[Former user]

(original reporter: pacopablo) When trying to autoload a table from a MS SQL database that contains columns of type 'tinyint', I receive the following traceback:

Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 97, in __call__
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 488, in reflecttable
  File "build/bdist.linux-i686/egg/sqlalchemy/databases/mssql.py", line 399, in reflecttable
KeyError: 'tinyint'

The quick and dirty solution was to add a key 'tinyint' to the {{{ischema_names}}} dictionary in mssql.py. I simpy used an MSSmallInteger object. However, I'm not sure if this is really desireable. A tiny int is supposed to be a number value 0,255. Obviously a Smallinterger is larger than that. What I don't know is if any checking is done by sqlalchemy, etc.

[Former user]

(original author: pacopablo) Patch for tinyint in MS SQL

[Mike Bayer]

SQLALchemy doesnt do any checking except for whats in the TypeEngine subclass itself, which is usually very little, so this is good enough for now (assuming SMALLINT is synonymous with TINYINT in MS-SQL, otherwise you might want to add an MSTinyInt type...if so, reopen this ticket with a new patch). this one patched in changeset:1763

[Former user]

(original author: pacopablo) SMALLINT is not synonymous with TINYINT in MS SQL. As SMALLINT is integer data from -2^15 through 2^15 - 1 and TINYINT is integer data from 0 through 255. However, I'll admit my complete ignorance in how sqlalchemy handles types, etc.

If you could point me in the right direction, I'd be happy to create another patch.

[Mike Bayer]

oh, easy enough, it would be like:

class MSTinyInteger(sqltypes.Integer): 
    def get_col_spec(self):
        return "TINYINT"


ischema_names = {
    'tinyint' : MSTinyInteger,

    # ...

SA is not doing much here with the specifics of the types in the case of integers, its mostly just a mapping for the identifying keywords. just test out your patch to make sure it works; thanks !

[Former user]

(original author: pacopablo) Patch creating MSTinyInteger class

[Former user]

(original author: pacopablo) OK, used your code and created a patch. I guess that will be sufficient. Thanks for the help and response.

[Mike Bayer]

changeset:1794