how to autocast in sqlalchemy tinyint(1) to bool?

Question

My app is creating sqlalchemy.Tables from a MySQL database and as it use tinyint(1) as boolean after select.select_from.all i am getting values with int instead of boolean. After that i am using json.dumps to pass it to a js receiver and that is the problem because js can't distinguish int from bool as they has the same type. so my question is how can i cast tinyint to bool in moment of execution .all() function in the simplest and most pythonic way?

i have tables with columns which datatype is tinyint. when i am executing select().select_from().all() it returns values with ints, not with bools

Answer 1

If you want to transparently convert the TINYINTs to booleans (and the reverse), there are a couple of approaches you could take, depending on how you are reflecting the tables.

Let's assume we have this table:

MariaDB [test]> describe t77148375;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| c     | tinyint(1) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

If you are not reflecting, you can just define the table in the Python layer with the right type:

tbl = sa.Table(
    't77148375', sa.MetaData(), sa.Column('id', sa.Integer, primary_key=True), sa.Column('c', sa.Boolean)
)
tbl.create(engine, checkfirst=True)  # Creates table in Python but not in the db.

If you are reflecting the tables via autoload_with, you can override the column type when you load the table.

tbl = sa.Table('t77148375', sa.MetaData(), sa.Column('c', sa.Boolean), autoload_with=engine)

If you are reflecting multiple tables at once, via metadata.reflect, you can use a listener to automatically change the column type using the reflection process:

@sa.event.listens_for(sa.Table, 'column_reflect')
def receive_column_reflect(inspector, table, column_info):
    # You may need to refine this test if you have TINYINT(1)s 
    # that aren't bools in the app layer.
    if isinstance(column_info['type'], mysql.TINYINT):
        if column_info['type'].display_width == 1:
            column_info['type'] = sa.Boolean


metadata = sa.MetaData()
metadata.reflect(engine)