[sqlalchemy] Re: SQLAlchemy and scrollable cursors

[Frank Millman]

On Feb 19, 7:23 pm, Rick Morrison rickmorri...@gmail.com wrote:
  Unfortunately, AFAICT, MS-SQL does not have an OFFSET clause (it uses
  TOP instead of LIMIT). How does SQLA handle this situation?

 For mssql2005 and higher, (those versions of mssql that support window
 functions using OVER,  row_number(), rank(), etc.), we simulate an OFFSET by
 wrapping the query with an outer query, and using where row_number() =
 offset and row_number()  offset + limit

 For mssql 2000 and lower, you'll be limited to use of LIMIT, and the mssql
 dialect will know to use TOP instead of the LIMIT syntax.

 For this to work in SA 0.4 and 0.5, you'll need to add the engine keyword
 has_window_funcs=1 to your connection string. From what I understand, SA
 0.6+ will sniff out the mssql version and automatically toggle the behavior.

 Rick

Thanks for the info - much appreciated.

Frank

--~--~-~--~~~---~--~~
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] SQLAlchemy and scrollable cursors

[Frank Millman]

Hi all

I have not used SQLAlchemy before. I am comfortable with SQL and enjoy
the feeling of being in control. However, I can see that SA does bring
some major benefits, particularly in hiding the differences between
the dialects of various databases.

Before making a decision about switching to SA, I would like to
describe something I use a lot, and would like to know how SA handles
this, if at all.

My application supports PostgreSQL and MS-SQL. Both of these databases
have the concept of a 'scrollable cursor'. AFAICT the DB-API does not
support this concept, so I manage it by executing the relevant SQL
commands directly.

The commands include the ability to DECLARE the cursor, MOVE n rows
backwards or forwards, MOVE to the beginning or end of the cursor,
FETCH n rows or -n rows from an absolute position or relative
position, etc.

I use the cursors in read-only mode, and maintain separate lists of
any inserts/updates/deletes to the cursor to keep an up-to-date view.

It is a little complex, and there are some syntax differences between
PostgreSQL and MS-SQL, but overall it works well. The major benefit,
of course, is that I can give the appearance of presenting a full
table to the user, but only select the rows in small chunks, which is
a huge performance benefit for very large tables.

Does SQLAlchemy have anything built-in to support this concept?

Thanks

Frank Millman

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