reflecting (autoload=True) MySQL tinyint(1) fields

[Lloyd Kvam]

I've just discovered that some tinyint (8-bit) fields have had their
values limited to 0 and 1 regardless of actual value supplied. Digging
through the documentation, I've learned that when MySQL tables are
reflected, tinyint(1) fields are processed as booleans.

I did not find emails from others howling in pain, so I suppose most
people are either happy with this behavior or unaffected. I understand
why a bool column definition would be mapped to tinyint(1). However,
doing the reverse, mapping tinyint(1) to bool, discards bits that MySQL
would not discard.

For me this was a misfeature. I would think that supplying bools to an
integer field would work OK. In python 2 + True == 3. So people using
booleans should not have too much difficulty, would they? Is there any
chance you'd consider autoloading tinyint(1) as an integer field?

[Michael Bayer]

sure i will remove this behavior today.

>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Michael Bayer]

On Mar 27, 2010, at 5:17 PM, Michael Bayer wrote:

>
> On Mar 27, 2010, at 4:16 PM, Lloyd Kvam wrote:
>
>> I've just discovered that some tinyint (8-bit) fields have had their
>> values limited to 0 and 1 regardless of actual value supplied. Digging
>> through the documentation, I've learned that when MySQL tables are
>> reflected, tinyint(1) fields are processed as booleans.
>>
>> I did not find emails from others howling in pain, so I suppose most
>> people are either happy with this behavior or unaffected. I understand
>> why a bool column definition would be mapped to tinyint(1). However,
>> doing the reverse, mapping tinyint(1) to bool, discards bits that MySQL
>> would not discard.
>>
>> For me this was a misfeature. I would think that supplying bools to an
>> integer field would work OK. In python 2 + True == 3. So people using
>> booleans should not have too much difficulty, would they? Is there any
>> chance you'd consider autoloading tinyint(1) as an integer field?
>
> sure i will remove this behavior today.

it is out in r95ac46ca88ee.

[Brad Wells]

The docs for the MySQL dialect need to be updated to reflect this
change. See http://www.sqlalchemy.org/docs/reference/dialects/mysql.html#sqlalchemy.dialects.mysql.TINYINT

For what it's worth I'd really like to see this remain as an optional
behavior. The BOOL/BOOLEN column types in MySQL are synonyms for
TINYINT(1). Someone creating columns using BOOLs might reasonably
expect to reflect that intention when autoloading. Really though, I
just want a way to avoid manually overriding 200 column definitions.
Is there a reasonable way to do that as is?

On Mar 27, 5:29 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Mar 27, 2010, at 5:17 PM, Michael Bayer wrote:
>
>
>
>
>
> > On Mar 27, 2010, at 4:16 PM, Lloyd Kvam wrote:
>
> >> I've just discovered that some tinyint (8-bit) fields have had their
> >> values limited to 0 and 1 regardless of actual value supplied.  Digging
> >> through the documentation, I've learned that when MySQL tables are
> >> reflected, tinyint(1) fields are processed as booleans.
>
> >> I did not find emails from others howling in pain, so I suppose most
> >> people are either happy with this behavior or unaffected.  I understand
> >> why a bool column definition would be mapped to tinyint(1).  However,
> >> doing the reverse, mapping tinyint(1) to bool, discards bits that MySQL
> >> would not discard.
>
> >> For me this was a misfeature.  I would think that supplying bools to an
> >> integer field would work OK.  In python 2 + True == 3.  So people using
> >> booleans should not have too much difficulty, would they?  Is there any
> >> chance you'd consider autoloading tinyint(1) as an integer field?
>
> > sure i will remove this behavior today.
>
> it is out in r95ac46ca88ee.
>
>
>
> >> --
> >> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> >> To post to this group, send email to sqlal...@googlegroups.com.
> >> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

> >> For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.

>
> > --
> > You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> > To post to this group, send email to sqlal...@googlegroups.com.
> > To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

> > For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On May 4, 2010, at 1:22 PM, Brad Wells wrote:

> The docs for the MySQL dialect need to be updated to reflect this
> change. See http://www.sqlalchemy.org/docs/reference/dialects/mysql.html#sqlalchemy.dialects.mysql.TINYINT
>
> For what it's worth I'd really like to see this remain as an optional
> behavior. The BOOL/BOOLEN column types in MySQL are synonyms for
> TINYINT(1). Someone creating columns using BOOLs might reasonably
> expect to reflect that intention when autoloading. Really though, I
> just want a way to avoid manually overriding 200 column definitions.
> Is there a reasonable way to do that as is?

if you reflect as TINYINT you still get a 0/1 back in results and it still accepts True/False. You really need it to give you the "True"/ "False" tokens ?

this is not entirely "de facto" in its methodology but this should work for now:

# before create_engine is called

from sqlalchemy.dialects.mysql import base
base.ischema_names['tinyint'] = base.BOOLEAN

[Brad Wells]

0/1 generally works in Python but won't convert to formats with native
boolean values correctly, in my case JSON.

Just a note, your suggestion works for me but will fail for any
unsigned columns.

I have a working solution so I'm fine with moving on from the issue,
Overall however, with MySQL's lack of a true boolean data type this
change leaves MySQL users with no particularly clean way to represent
true boolean types. Just my two cents.

Thanks for the help.

On May 4, 2:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On May 4, 2010, at 1:22 PM, Brad Wells wrote:
>
> > The docs for the MySQL dialect need to be updated to reflect this

> > change. Seehttp://www.sqlalchemy.org/docs/reference/dialects/mysql.html#sqlalche...