[sqlalchemy] Re: Calling a function (Stored Procedure) returning a recordset
On May 15, 2007, at 11:08 AM, Michael Bayer wrote: session.query(Person).count(surname='Patel') correction, session.query(Person).count_by(surname='Patel') --~--~-~--~~~---~--~~ 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] Re: Calling a function (Stored Procedure) returning a recordset
Thanks a lot, Michael and Eric! This long thread has been quite useful to me. Sanjay --~--~-~--~~~---~--~~ 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] Re: Calling a function (Stored Procedure) returning a recordset
On May 14, 2007, at 1:28 AM, Sanjay wrote:
Further findings and queries on SelectResults:
1. Is SelectResults still needed in certain situations:
a. Observed that SelectResults was having a count() method. If we
don't use SelectResults, either we have to query the database for
getting the count manually, or use len(list). Using 'len' may not be
recommended in paginated data grids.
query has a count() method as well.
b. Seeing the TurboGears code for 'paginate', it checks for the type
of variable. If it is a list, it just applies len(list)! Does that
mean, we have to explicitly use SelectResults with TurboGears?
Pylons has taken this issue into account with its own paginage function:
http://pylonshq.com/docs/0.9.5/module-webhelpers.pagination.html
the size of the list is passed separately (which is typically
achieved via a single count() call), if not present uses len(list).
TG should follow this example.
2. SelectResults not behaving properly:
I have some code which returns a list although I expect a
SelectResults. Here is the minimal version reproducing that. Can't
guess whether I am doing something wrong or it's a bug. Need help.
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
import sqlalchemy.mods.selectresults
context = SessionContext(create_session)
session = context.current
metadata = BoundMetaData('sqlite:///satest', echo=True)
# table definitions
person_table = Table('person', metadata,
Column('person_id', Integer, primary_key=True, autoincrement =
True),
Column('first_name', Unicode(30)),
Column('last_name', Unicode(30)))
metadata.drop_all()
metadata.create_all()
class Person(object):
pass
assign_mapper(context, Person, person_table)
p1 = Person(first_name=Sanjay, last_name=Patel)
p2 = Person(first_name=Ranjan, last_name=Naik)
session.flush()
del p1
del p2
session.clear()
# persons = Person.select_by(person_id=1)
# assert isinstance(persons,
sqlalchemy.ext.selectresults.SelectResults) # OK
persons = Person.select(Person.c.person_id.in_(1))
assert isinstance(persons,
sqlalchemy.ext.selectresults.SelectResults)
# Fails!
this was a small bug that was fixed in trunk a few weeks ago.
SelectResults is deprecated anyway and its easier to use query directly.
--~--~-~--~~~---~--~~
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] Re: Calling a function (Stored Procedure) returning a recordset
Further findings and queries on SelectResults:
1. Is SelectResults still needed in certain situations:
a. Observed that SelectResults was having a count() method. If we
don't use SelectResults, either we have to query the database for
getting the count manually, or use len(list). Using 'len' may not be
recommended in paginated data grids.
b. Seeing the TurboGears code for 'paginate', it checks for the type
of variable. If it is a list, it just applies len(list)! Does that
mean, we have to explicitly use SelectResults with TurboGears?
2. SelectResults not behaving properly:
I have some code which returns a list although I expect a
SelectResults. Here is the minimal version reproducing that. Can't
guess whether I am doing something wrong or it's a bug. Need help.
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
import sqlalchemy.mods.selectresults
context = SessionContext(create_session)
session = context.current
metadata = BoundMetaData('sqlite:///satest', echo=True)
# table definitions
person_table = Table('person', metadata,
Column('person_id', Integer, primary_key=True, autoincrement =
True),
Column('first_name', Unicode(30)),
Column('last_name', Unicode(30)))
metadata.drop_all()
metadata.create_all()
class Person(object):
pass
assign_mapper(context, Person, person_table)
p1 = Person(first_name=Sanjay, last_name=Patel)
p2 = Person(first_name=Ranjan, last_name=Naik)
session.flush()
del p1
del p2
session.clear()
# persons = Person.select_by(person_id=1)
# assert isinstance(persons,
sqlalchemy.ext.selectresults.SelectResults) # OK
persons = Person.select(Person.c.person_id.in_(1))
assert isinstance(persons, sqlalchemy.ext.selectresults.SelectResults)
# Fails!
Hi Michael,
The post being too long probably was not that clear to draw your
attention. In summary, it tries to justify the need of SelectResults
still in certain situations. Also, it tries to point out what seems
like a bug in SelectResults. Would need your comments help on this.
thanks
Sanjay
--~--~-~--~~~---~--~~
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] Re: Calling a function (Stored Procedure) returning a recordset
Also SelectResults is deprecated as
Query includes all of its functionality now.
So nice! Does this mean, in laymans term,
1. I do not have to bother writing import
sqlalchemy.mods.selectresults
2. Normal query, whether used raw or through ORM, can supply data to
paginated data grids as efficiently as SelectResults was doing?
Further findings and queries on SelectResults:
1. Is SelectResults still needed in certain situations:
a. Observed that SelectResults was having a count() method. If we
don't use SelectResults, either we have to query the database for
getting the count manually, or use len(list). Using 'len' may not be
recommended in paginated data grids.
b. Seeing the TurboGears code for 'paginate', it checks for the type
of variable. If it is a list, it just applies len(list)! Does that
mean, we have to explicitly use SelectResults with TurboGears?
2. SelectResults not behaving properly:
I have some code which returns a list although I expect a
SelectResults. Here is the minimal version reproducing that. Can't
guess whether I am doing something wrong or it's a bug. Need help.
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
import sqlalchemy.mods.selectresults
context = SessionContext(create_session)
session = context.current
metadata = BoundMetaData('sqlite:///satest', echo=True)
# table definitions
person_table = Table('person', metadata,
Column('person_id', Integer, primary_key=True, autoincrement =
True),
Column('first_name', Unicode(30)),
Column('last_name', Unicode(30)))
metadata.drop_all()
metadata.create_all()
class Person(object):
pass
assign_mapper(context, Person, person_table)
p1 = Person(first_name=Sanjay, last_name=Patel)
p2 = Person(first_name=Ranjan, last_name=Naik)
session.flush()
del p1
del p2
session.clear()
# persons = Person.select_by(person_id=1)
# assert isinstance(persons,
sqlalchemy.ext.selectresults.SelectResults) # OK
persons = Person.select(Person.c.person_id.in_(1))
assert isinstance(persons, sqlalchemy.ext.selectresults.SelectResults)
# Fails!
--~--~-~--~~~---~--~~
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] Re: Calling a function (Stored Procedure) returning a recordset
tasklist = func.get_tasklist(engine=engine).execute() # is this the correct way? looks fine to me. for the rest of it, im not very familiar with PG stored procedures. I think SQLAlchemy code is translating to SELECT get_tasklist(). But what is actually needed is SELECT * FROM get_tasklist(). So, probably the question is, What SQLAlchemy code will translate to SELECT * FROM get_tasklist(). Needing suggestions. thanks Sanjay --~--~-~--~~~---~--~~ 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] Re: Calling a function (Stored Procedure) returning a recordset
On May 2, 2007, at 2:25 AM, Sanjay wrote:
tasklist = func.get_tasklist(engine=engine).execute() # is this
the
correct way?
looks fine to me. for the rest of it, im not very familiar with PG
stored procedures.
I think SQLAlchemy code is translating to SELECT get_tasklist(). But
what is actually needed is SELECT * FROM get_tasklist().
So, probably the question is, What SQLAlchemy code will translate to
SELECT * FROM get_tasklist(). Needing suggestions.
been meaning to document this
s = select([*], from_obj=[func.get_tasklist()], engine=engine)
result = s.execute()
alternately, you can name the columns:
s = select([column('col1'), column('col2')], from_obj=
[func.get_tasklist()], engine=engine)
this select has columns like any other table, i.e. s.c.col1,
s.c.col2, etc. which can be used to embed within subqueries.
--~--~-~--~~~---~--~~
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] Re: Calling a function (Stored Procedure) returning a recordset
On May 2, 2007, at 10:00 AM, Sanjay wrote:
So, probably the question is, What SQLAlchemy code will translate to
SELECT * FROM get_tasklist(). Needing suggestions.
Is it like this?:
tasklist = engine.text('SELECT * FROM
get_tasklist()').execute().fetchall()
It returns a list. Any idea how to get a SelectResults, would help a
lot.
SelectResults is only used with the ORM. youd have to map a class to
your selectable function (the selectable function being created as i
noted in my previous email). also SelectResults is deprecated as
Query includes all of its functionality now.
--~--~-~--~~~---~--~~
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] Re: Calling a function (Stored Procedure) returning a recordset
On May 1, 2007, at 7:06 AM, Sanjay wrote: Hi, I have a postgres function returning a set of records (using RETURN NEXT). While calling the function like this: tasklist = func.get_tasklist(engine=engine).execute() # is this the correct way? looks fine to me. for the rest of it, im not very familiar with PG stored procedures. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
