On Nov 12, 2012, at 5:32 AM, Petr Blahoš wrote:
> Hi all,
> the company I worked for has decided to change a RDBMS behind our ERP.
> The side effect of this is that the columns will no longer be prefixed with t$
> but with t_ instead. I do not want to change all the occurences of column
> names in my code. I should also mention, that I use only selects, and no ORM.
> So what I did was I made a new dialect as a subclass of mssql.pyodbc and
> I overrode execution context and statement compiler. In statement compiler's
> visit_select I simply replace "t$" with "t_" in the select returned from the parent,
> and in execution context's get_result_proxy I return a proxy whose row proxy's
> keymap is slightly updated (don't worry, I am attaching the code).
> My question: Is this the right way to do it?
Dialects only exist to handle the task of interacting with a given DBAPI/database pair, and are not intended to be extensible for the purposes of satisfying particular use cases. SQLAlchemy supports an event API that can easily provide for search-and-replace features like these. Just use before_cursor_execute() along with retval=True:
http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=befor...
for the result side, there are several places this might be intercepted:
1. in after_cursor_execute(), you can modify the ".description" attribute on the cursor to match the changes in label name.
2. if the pyodbc cursor is disallowing modification of .description, alter the "context" passed to after_cursor_execute():
a. wrapping the immutable cursor with a wrapper that provides a new .description,
b. patching on a get_result_proxy() method with a new ResultProxy subclass that overrides _cursor_description()
3. or use the after_execute() event, where you're passed the ResultProxy which you could then change in place -
you could re-establish the "metadata" via "result._metadata = ResultMetaData(result, make_new_metadata(cursor.description))".
4. or given the ResultProxy in after_execute(), do the same rewriting of the keymap that you're doing now.
But I'd probably not be using that approach either. Column objects support a "key" field so that they need not be referenced in code in the same way the relational database does; one of the primary purposes of Column is to allow symbolic names to prevent the issue of needing to "change all occurrences" of any schema-related name in code:
my_table = Table('some_name', metadata, Column('t$somename', Integer, key='somename'))
generation of a "key" like the above can be automated using a simple function:
def column(name, *arg, **kw):
key = name.replace('t$', '', name)
kw.setdefault('key', key)
return Column(name, *arg, **kw)
my_table = Table('some_name', metadata, column('t$somename', Integer))
if OTOH you're using table reflection, you can use the column_reflect event, which provides a dictionary where you can place a new "key":
@event.listens_for(Table, 'column_reflect')
def evt(inspector, table, column_info):
key = column_info['name'].replace('t$', column_info['name'])
column_info['key'] = key
http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=colum...
note that the "inspector" argument above is new in 0.8 - if in 0.7, the arguments are just "table", and "column_info".
