CODEWITHSUNDEEP
pythonsqlalchemy
Tanner Semerad
Tanner Semerad

Reputation: 12672

How to override reflected column only if it exists using SQLAlchemy?

I have a dictionary of column definitions that I want to use when reflecting a table using SQLAlchemy, but I only want to use each column definition if the column actually exists in the database. For example:

TABLE_DEFAULTS = {
    'my_table': {
        'my_column': Column(String),
        'other_column': Column(String),
        'third_column': Column(String, default='Sample')
    }
}

Let's say I'm reflecting my_table and it happens to have third_column and fourth_column. I have a default for third_column, so I'd like to use that. Since I have no default for fourth_column, I'd like to use the Column resulting from the reflection. The following pseudocode displays how the default columns are used to generate the model class.

tbl_name = 'my_table'
cls_dict = dict(
    __table_args__={'autoload': True,
                    'autoload_with': engine},
    __tablename__ = tbl_name
)
if tbl_name in TABLE_DEFAULTS:
    # TODO: Only want to add the default columns if they actually exist in db
    cls_dict.update(TABLE_DEFAULTS[tbl_name])

# Unfortunately, MyModel will have attributes for my_column and other_column 
# even though they don't exist
MyModel = type(tbl_name, Base, cls_dict)

To solve my problem, I feel like I have to inspect the database before doing the autoload, essentially inspecting the database twice. Or probably better, just don't autoload, and instead create the columns based off of the earlier inspection. Would this be the proper way to do this, or is there a simpler way? I was hoping maybe there would be something in the API to override column definitions only if they exist in the database.

Upvotes: 0

Views: 598

Answers (1)

van
van

Reputation: 76992

Assuming that reflection is done only once per application, I do not see a problem inspecting first the database only to get columns for the required tables. In fact, you can have a routine to create an updated dictionary from your original TABLE_DEFAULTS and the rest of your code would work without changes.

An alternative would be to listen to column_reflect event and modify the column as required there. But at that stage the Column instances are not created yet, and instead the column_info parameter to that listener is simply a dictionary of attributes, which you can overwrite from your original columns:

{'primary_key': 0, 
 'nullable': False, 
 'default': None, 
 'autoincrement': False,
 'type': VARCHAR(), 
 'name': u'third_column'}

Upvotes: 1

Related Questions