Re: [sqlalchemy] All column names renames: best practice?

[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

-- 
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.



a.diff
Description: Binary data

Re: [sqlalchemy] All column names renames: best practice?

[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



-- 
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.

[sqlalchemy] 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 )

-- 
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.

import copy

import sqlalchemy.engine.base
import sqlalchemy.dialects.mssql.base
import sqlalchemy.dialects.mssql.pyodbc

class SQLABaanRowProxy(sqlalchemy.engine.base.RowProxy):
def __init__(self, parent, row, processors, keymap):

For all keys starting with t_ adds also a t$... key.

km = copy.deepcopy(keymap)
for (k, v) in keymap.iteritems():
if isinstance(k, basestring):
if k.startswith(t_):
km[t$ + k[2:]] = v
super(SQLABaanRowProxy, self).__init__(parent, row, processors, km)

class SQLABaanResultProxy(sqlalchemy.engine.base.ResultProxy):
_process_row = SQLABaanRowProxy

class SQLABaanCompiler(sqlalchemy.dialects.mssql.base.MSSQLCompiler):
def visit_select(self, select, **kwargs):

Brute-force replace of t$ to t_...:w

ret = super(SQLABaanCompiler, self).visit_select(select, **kwargs)
ret = ret.replace(t$, t_)
return ret

class SQLABaanExecutionContext(sqlalchemy.dialects.mssql.pyodbc.MSExecutionContext_pyodbc):
def get_result_proxy(self):
if self._result_proxy:
return self._result_proxy   #can never happen as we do only selects
else:
return SQLABaanResultProxy(self)


class SQLABaan(sqlalchemy.dialects.mssql.pyodbc.MSDialect_pyodbc):
execution_ctx_cls = SQLABaanExecutionContext
statement_compiler = SQLABaanCompiler

dialect = SQLABaan

Re: [sqlalchemy] All column names renames: best practice?

[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 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.

[sqlalchemy] all

[burgiduroy]

Are there any performance difference between the two?

for row in  query_object.all():
   do_something()

AND

for row in query_object:
   do_something()

-- 
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.

[sqlalchemy] from sqlalchemy all() to subquery to threaded process?

[Lukasz Szybalski]

Hello,

I am pulling a list of files I need to print from a database and I
have all the records after doing the sqlalchemy query with .all()

Now I have a list of 3000 files I need to print, around 3 files per
userID, I want to print all 3 at the same time in different threads,
and my only requirement is that all files are printed for user 1
before we start printing for user 2.

Because the process doesn't use full power of the CPU when I print one
by one, I want to thread the process so it prints all 3 files at the
same time in different threads. When its done it moves on to the next
userid.

How can I sub query by user Id?

results= 
Session.query(PrintTable).filter(PrintTable.Date='20081120').order_by(PrintTable.Username).all()

How can I from above result go to:

1. In a for loop,
 a.  get a list of files for userid=1   (userid=1 files to print
file1,file2,file3)
 b. pass the 3 filenames to a thread function that will print the files
 c. go to the next userid

Any idea how can this be done? I can find some tutorials on threading
but I'm not sure how can I subquery to get the 3 file names?

Thanks,
Lucas

-- 
Turbogears2 Manual
http://lucasmanual.com/mywiki/TurboGears2
Bazaar and Launchpad
http://lucasmanual.com/mywiki/Bazaar

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] *all* *new* *tutorials* !!!!

[Michael Bayer]

Hi gang -

The documentation for 0.4 is undergoing tremendous changes, and is now
released, in its almost-there format, at http://www.sqlalchemy.org/docs/04/
.  The goal with these docs is not just to update to new 0.4
paradigms, but to also raise the bar for accuracy and clarity.

Of major note is that the datamapping and sql construction sections,
as well as the old tutorial, have been entirely replaced by two new
and very comprehensive tutorials, one targeted at ORM and the other at
SQL Expression Language.  Both have no prerequisites to start, they
each can be the first thing you ever read about SQLAlchemy.  Both are
also fully executable doctest format, so they are guaranteed not to
have my usual array of mistakes.

Also here is a rewritten mapper configuration document to replace
advanced datamapping.  It includes clearer, up-to-date, and more
correct examples of virtually every major mapper pattern we have,
including all the new stuff like dynamic relations.

With recently updated engine and metadata sections, the only major
section left is sessions, which already includes information about
the new autoflush and transactional sessions, as well as two-phase
and SAVEPOINT recipes...I hope to simplify some of the older content
here as well as standardize on the new sessionmaker function and its
cousin, scoped_session, which replaces SessionContext as well as
assignmapper (both are deprecated in 0.4).

I hope everyone can check out the docs, come back with feedback/
corrections/questions, and start getting ready for 0.4 !

- mike


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] all() or list() ?

[Alexandre CONRAD]

Hello,

I'm realizing that I've been using .list() to query objects. But all the 
examples in the docs talk about .all().

What's the difference ? Should I switch to .all() rather than .list() ? 
Will list be deprecated in 0.4 ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---