On 12/9/06, jose <[EMAIL PROTECTED]> wrote: > > Thank you Michael for your explanation. > > Since I can do (on sqlalchemy), the same thing in more than one ways, > I'm very confused and I would like ask you, about > Constructing SQL Queries via _table objects_ or _mapper classes_ > > Seems to me that *table object* is more flexible than *classe*s to > construct a query, > but I wonder what the difference between those and when/why using one or > another...
I'll try to explain. > c = select(users) With this method, what you get back when you call c.execute() is a ResultProxy. You then would call result.fetchall() to get a list of RowProxy objects, which you can then access in several ways: row[0] # Returns the value of the first column in the database table row['name'] # Returns the value of the "name" column, regardless of its position row.address # Ditto for the "address" column This is just like doing "result = cursor.fetchall(); for row in result: ..." from any DBAPI module, except that the rows have some slightly nicer ways to access them. See http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_select_resultproxy for more details about this usage. > c = session.query(Users) Using this approach, when you call c.select(), you'll get a list (not a ResultProxy, but a plain old Python list) of User instances, each of which has its attributes already filled-in and modifiable. Thus: result = c.select_by(name='Robin') u = result[0] print u.name # Prints "Robin" u.name = 'Robin Munn' # This marks the row as "dirty" session.flush() # This flushes all "dirty" rows, thus doing the appropriate UPDATE statement Notice how by simply setting the "name" attribute, I've caused an UPDATE statement to be prepared. (It won't be actually *run* until you call session.flush()). If you were using the "c = select(users)" method of constructing SQL queries, you'd have needed to run the UPDATE statement by hand, by doing: update(users.c.name=='Robin').execute(name='Robin Munn') (Or by doing "connection.execute('UPDATE users SET name ...')" and so on. As with the SELECT statements, SQLAlchemy doesn't force you to use its features. If you want to issue raw SQL, you can.) The advantage of using SQLAlchemy's mapped objects, therefore, can be seen in the fact that you didn't have to run the UPDATE statements yourself. Nor did you have to keep track of which objects have been changed (are "dirty"). SQLAlchemy does all that for you. By contrast, when you use the bare "c = select(users)" approach (selecting against the table object, not against the mapped class) then what you get are plain old database rows, that do *not* track updates and so on -- which means you have to issue your UPDATE statements (and your INSERT and DELETE statements) by hand. http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_query will tell you more about issuing queries against mapped objects. -- Robin Munn [EMAIL PROTECTED] GPG key 0x4543D577 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
