All column names renames: best practice?

[Petr Blahoš]

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?

Thanks in advance

--

Petr

( attaching the code of the dialect - same as this: https://gist.github.com/4058539 )

[Michael Bayer]

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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
<sqlabaan.py>

[Petr Blahos]

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.

[...] 

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'))

Thanks for the pointers Mike. Just to clarify: The first argument to Column is

the real name of the column in the database, while key is an alternative name 

under which I can access it, right?

I have been unlucky with mssql, where the first part - the table definition 

and making queries worked, but not accessing data in RowProxy using the

key. I guess there is a bug in dialects/mssql/base.py 

in MSSQLCompiler.visit_column:

                 if result_map is not None:

                     result_map[column.name.lower()] = \

-                                    (column.name, (column, ),

+                                    (column.name, (column, column.name, column.key),

                                                     column.type)

 

                 return super(MSSQLCompiler, self).\

(also attaching...)

Thanks again.

--

Petr

[Michael Bayer]

On Nov 13, 2012, at 5:18 AM, Petr Blahos wrote:

I have been unlucky with mssql, where the first part - the table definition 

and making queries worked, but not accessing data in RowProxy using the

key. I guess there is a bug in dialects/mssql/base.py 

in MSSQLCompiler.visit_column:

                 if result_map is not None:

                     result_map[column.name.lower()] = \

-                                    (column.name, (column, ),

+                                    (column.name, (column, column.name, column.key),

                                                     column.type)

 

                 return super(MSSQLCompiler, self).\

thanks, this is http://www.sqlalchemy.org/trac/ticket/2607 in rcd9988751479 r0fe9fa12d4db (0.7) , you can get the tar.gz from "Development Versions" in :

http://www.sqlalchemy.org/download.html