[Sqlalchemy-tickets] Issue #4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is n

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is not iterable" with some drivers
https://bitbucket.org/zzzeek/sqlalchemy/issues/4331/dialects-mysql-typespy-bitresult_processor

mark grandi:

**python version:** `Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 26 2018, 23:26:24)
[Clang 6.0 (clang-600.0.57)] on darwin`

**mysql version:** `/usr/local/mysql/bin/mysql  Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)`

**sqlalchemy version:** `1.2.11`

**drivers used:** `mysql-connector-python` and `pymysql`

**Description:** 
There seems to be a curious inconsistency with the BIT class inside `dialects/mysql/types.py` , which is provided below:

```
    def result_processor(self, dialect, coltype):
        """Convert a MySQL's 64 bit, variable length binary string to a long.

        TODO: this is MySQL-db, pyodbc specific.  OurSQL and mysqlconnector
        already do this, so this logic should be moved to those dialects.

        """

        def process(value):
            if value is not None:
                v = 0
                for i in value:
                    if not isinstance(i, int):
                        i = ord(i)  # convert byte to int on Python 2
                    v = v << 8 | i
                return v
            return value
        return process
```

It seems that some mysql drivers end up calling this class, while others don't (as the comment suggests) , but that leads to a difference in behavior.

 It seems that if you are using a driver that ends up calling BIT.result_processor to process BIT types, and have a mysql table that has a column that is of type `BIT(1)`, but your SQLAlchemy declarative model class says the column is a `Boolean` (aka `tinyint`), SQLAlchemy throws an exception because a `int` is not iterable, and in `BIT.result_processor`, it assumes that the data type is iterable (because it is of type `bytes` I guess) . 

I noticed this when switching drivers because i wanted server side cursors for a large data migration project i'm doing for work, and the database we migrated from was using a old version of the schema where we didn't standardize on the data type we use for booleans. I solved this locally by changing the `if` statement to:

```
if value is not None and not isinstance(value, int):
```

Attached is a script that demonstrates the problem, to run it, create a virtualenv and `pip3 install SQLAlchemy, mysql-connector-python, PyMySQL` , and set the database host/port/username/password in the `connection_dict` variable at the top

This is the output when running it on my own instance of mysql:

```(.venv) [2018-09-10 12:47:26] markgrandi@Gypaetus:~/Temp/sqla_bit_testing$ python3 test.py
2018-09-10 12:47:33,857 MainThread           root                                INFO    : mysqlconnector test starting
2018-09-10 12:47:33,857 MainThread           root.mysqlconnector.boolean         INFO    : creating and inserting rows with a SQLAlchemy table that has a Boolean column
2018-09-10 12:47:33,857 MainThread           root.mysqlconnector.boolean         INFO    : url is: `mysql+mysqlconnector://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:33,899 MainThread           root.mysqlconnector.boolean         INFO    : engine: `Engine(mysql+mysqlconnector://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x108fdda58>`
2018-09-10 12:47:33,899 MainThread           root.mysqlconnector.boolean         INFO    : calling create_all on the metadata `MetaData(bind=None)`
2018-09-10 12:47:34,011 MainThread           root.mysqlconnector.boolean         INFO    : truncating the table to get rid of any existing rows
2018-09-10 12:47:34,077 MainThread           root.mysqlconnector.boolean         INFO    : adding rows to table
2018-09-10 12:47:34,079 MainThread           root.mysqlconnector.boolean         INFO    : adding row `<TestTableWithBoolean id=None colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=False >`
2018-09-10 12:47:34,079 MainThread           root.mysqlconnector.boolean         INFO    : adding row `<TestTableWithBoolean id=None colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=False >`
2018-09-10 12:47:34,110 MainThread           root.mysqlconnector.boolean         INFO    : querying for existing rows
2018-09-10 12:47:34,117 MainThread           root.mysqlconnector.boolean         INFO    : got row: `<TestTableWithBoolean id=1 colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=False >`
2018-09-10 12:47:34,118 MainThread           root.mysqlconnector.boolean         INFO    : got row: `<TestTableWithBoolean id=2 colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=False >`
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.boolean         INFO    : session closed, engine disposed
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.bit             INFO    : printing out rows that were created with a SQLAlchemy table that has a Boolean column, but now we use a SQLAlchemy table that has a BIT column
2018-09-10 12:47:34,120 MainThread           root.mysqlconnector.bit             INFO    : url is: `mysql+mysqlconnector://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,123 MainThread           root.mysqlconnector.bit             INFO    : engine: `Engine(mysql+mysqlconnector://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x10904dac8>`
2018-09-10 12:47:34,123 MainThread           root.mysqlconnector.bit             INFO    : querying for existing rows
2018-09-10 12:47:34,196 MainThread           root.mysqlconnector.bit             INFO    : got row: `<TestTableWithBit id=1 colone=1932 coltwo=37bca76085f54bcb8767889b567be586 colthree=0 >`
2018-09-10 12:47:34,196 MainThread           root.mysqlconnector.bit             INFO    : got row: `<TestTableWithBit id=2 colone=3932 coltwo=9f8fcc64d69e447ea860c183c800cd89 colthree=0 >`
2018-09-10 12:47:34,197 MainThread           root.mysqlconnector.bit             INFO    : session closed, engine disposed
2018-09-10 12:47:34,198 MainThread           root                                INFO    : pymysql test starting
2018-09-10 12:47:34,198 MainThread           root.pymysql.boolean                INFO    : creating and inserting rows with a SQLAlchemy table that has a Boolean column
2018-09-10 12:47:34,198 MainThread           root.pymysql.boolean                INFO    : url is: `mysql+pymysql://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,268 MainThread           root.pymysql.boolean                INFO    : engine: `Engine(mysql+pymysql://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x1091f0908>`
2018-09-10 12:47:34,268 MainThread           root.pymysql.boolean                INFO    : calling create_all on the metadata `MetaData(bind=None)`
2018-09-10 12:47:34,294 MainThread           root.pymysql.boolean                INFO    : truncating the table to get rid of any existing rows
2018-09-10 12:47:34,347 MainThread           root.pymysql.boolean                INFO    : adding rows to table
2018-09-10 12:47:34,348 MainThread           root.pymysql.boolean                INFO    : adding row `<TestTableWithBoolean id=None colone=4765 coltwo=e31402acaa6c44a7a606ee73062e1813 colthree=False >`
2018-09-10 12:47:34,349 MainThread           root.pymysql.boolean                INFO    : adding row `<TestTableWithBoolean id=None colone=5516 coltwo=c56e4c9c3113467eb63dc419fd1768da colthree=False >`
2018-09-10 12:47:34,361 MainThread           root.pymysql.boolean                INFO    : querying for existing rows
2018-09-10 12:47:34,364 MainThread           root.pymysql.boolean                INFO    : got row: `<TestTableWithBoolean id=1 colone=4765 coltwo=e31402acaa6c44a7a606ee73062e1813 colthree=False >`
2018-09-10 12:47:34,365 MainThread           root.pymysql.boolean                INFO    : got row: `<TestTableWithBoolean id=2 colone=5516 coltwo=c56e4c9c3113467eb63dc419fd1768da colthree=False >`
2018-09-10 12:47:34,366 MainThread           root.pymysql.boolean                INFO    : session closed, engine disposed
2018-09-10 12:47:34,367 MainThread           root.pymysql.bit                    INFO    : printing out rows that were created with a SQLAlchemy table that has a Boolean column, but now we use a SQLAlchemy table that has a BIT column
2018-09-10 12:47:34,367 MainThread           root.pymysql.bit                    INFO    : url is: `mysql+pymysql://root:SNIP@localhost:3306/testing`
2018-09-10 12:47:34,377 MainThread           root.pymysql.bit                    INFO    : engine: `Engine(mysql+pymysql://root:***@localhost:3306/testing)`, session: `<sqlalchemy.orm.session.Session object at 0x1091dc748>`
2018-09-10 12:47:34,378 MainThread           root.pymysql.bit                    INFO    : querying for existing rows
Traceback (most recent call last):
  File "test.py", line 171, in <module>
    app.test_with_pymysql()
  File "test.py", line 54, in test_with_pymysql
    self.print_out_rows_with_bit_column("mysql+pymysql", lg)
  File "test.py", line 90, in print_out_rows_with_bit_column
    for i in session.query(TestTableWithBit).all():
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 2836, in all
    return list(self)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 98, in instances
    util.raise_from_cause(err)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 79, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 79, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 511, in _instance
    loaded_instance, populate_existing, populators)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 611, in _populate_full
    dict_[key] = getter(row)
  File "/Users/markgrandi/Temp/sqla_bit_testing/.venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/types.py", line 371, in process
    for i in value:
TypeError: 'int' object is not iterable```

[Sqlalchemy-tickets] Issue #4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is n

[Sqlalchemy-tickets] Issue #4331: dialects/mysql/types.py:BIT.result_processor - "'int' object is not iterable" with some drivers (zzzeek/sqlalchemy)