skip to Main Content

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

2

Answers


  1. By using a cast: SELECT cast(tiny_int_value as signed) FROM table

    Login or Signup to reply.
  2. 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)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search