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

[Michael Bayer]

SQLA does not provide any direct support or abstractions over
scrollable cursors. If you had a SQLAlchemy application and wanted
to access these methods, you can get access to the DBAPI connection as
well as a cursor, but the operations would not be integrated with the
rest of the API.

SQLA, as well as most other data abstraction tools outside of .NET,
provide the "standard" way to go about presenting a "window" on a
table by using LIMIT/OFFSET, which both postgres and mysql support
natively. These methods also work better for the typical web
application flow since the approach is essentially stateless, whereas
a scrollable cursor is very stateful.

[Frank Millman]

On Feb 18, 5:28 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Feb 18, 2009, at 2:23 AM, Frank Millman wrote:
>
> > Hi 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.

[...]

> > Does SQLAlchemy have anything built-in to support this concept?
>
> SQLA does not provide any direct support or abstractions over  
> scrollable cursors.    If you had a SQLAlchemy application and wanted  
> to access these methods, you can get access to the DBAPI connection as  
> well as a cursor, but the operations would not be integrated with the  
> rest of the API.
>
> SQLA, as well as most other data abstraction tools outside of .NET,  
> provide the "standard" way to go about presenting a "window" on a  
> table by using LIMIT/OFFSET, which both postgres and mysql support  
> natively.   These methods also work better for the typical web  
> application flow since the approach is essentially stateless, whereas  

> a scrollable cursor is very stateful.- Hide quoted text -
>

Interesting - I had not thought of using LIMIT/OFFSET for this, but it
makes a lot of sense.

Unfortunately, AFAICT, MS-SQL does not have an OFFSET clause (it uses
TOP instead of LIMIT). How does SQLA handle this situation?

Thanks

Frank

[Rick Morrison]

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

[Frank Millman]

Thanks for the info - much appreciated.

Frank

[Michael Bayer]

On Feb 19, 2009, at 12:23 PM, Rick Morrison wrote:
>
> 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.

well, in 0.4/0.5, if you dont set the flag, and try to do LIMIT/
OFFSET, it raises an exception. So we just took the whole thing
out. The effect is, if you try to do LIMIT/OFFSET and the DB doesn't
support it, it raises an exception. Which is close enough to the
previous behavior minus the flag ;).