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=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute 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=column_reflect#sqlalchemy.events.DDLEvents.column_reflect note that the "inspector" argument above is new in 0.8 - if in 0.7, the arguments are just "table", and "column_info". > Thanks in advance > > -- > Petr > > ( attaching the code of the dialect - same as this: > https://gist.github.com/4058539 ) > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/nXkzhvJiwysJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > <sqlabaan.py> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
