[sqlalchemy] Re: how to run a stored procedure?
Hello,
What if there is a column that is called state
When I try to add use_labels inside the execute statement I get below
error. Should I be using the use_labels somewhere else?:
a[0].State
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1943, in
__getattr__
return self[name]
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1934, in
__getitem__
try 'use_labels' option on select statement. % key)
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'State' in
result set! try 'use_labels' option on select statement.
# ---unexpected keyword error
a=session.execute(assp_ReportPolicyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719},use_labels=True).fetchall()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in
execute
engine = self.get_bind(mapper, clause=clause, **kw)
TypeError: get_bind() got an unexpected keyword argument 'use_labels'
Thank you,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
how is calling query(colmames)...all() significantly different from simply
saying execute(..).fetchall() ? you get a list of named-tuple like objects
in both cases.
You are correct, execute().fetchall() does already returns a list of
rows, where each row has attributes. I was initially using execute()
and couldn't pass in the myresults[5] so I assumed this was returning
a list of results strings and not tuple, but they do.
I will use fetchall, and I am able to use myresults[5].customername
I'll update my docs with these example.
http://lucasmanual.com/mywiki/PythonManual#sqlalchemyandmssql
I wonder if passing arguments like this is mssql specific
(@some_input_parameter) , or it can be reused in other databases, like
mysql or postgre?
myresults=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks a lot.
Lucas
--
How to setup unix odbc for use with sqlalchemy and mssql
http://lucasmanual.com/mywiki/unixODBC
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1453, in all
return list(self)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1565, in
__iter__
return self._execute_and_instances(context)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1570, in
_execute_and_instances
mapper=self._mapper_zero_or_none())
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 276, in
_mapper_zero_or_none
if not getattr(self._entities[0], 'primary_entity', False):
IndexError: list index out of range
Thanks,
Lucas
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1453, in all
return list(self)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1565, in
__iter__
return self._execute_and_instances(context)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1570, in
_execute_and_instances
mapper=self._mapper_zero_or_none())
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 276, in
_mapper_zero_or_none
if not getattr(self._entities[0], 'primary_entity', False):
IndexError: list index out of range
Thanks,
Lucas
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
a=session.execute(assp_ReportDailyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719})
b=session.query(a.keys()).from_statement(exec
assp_ReportDailyTransactions @start_date=:start,
@end_date=:end).params({'start':20100701,'end':20100719}).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 873, in
query
return self._query_cls(entities, self, **kwargs)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 92, in
__init__
self._set_entities(entities)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 99, in
_set_entities
entity_wrapper(self, ent)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 2584, in
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 6:19 PM, Lukasz Szybalski wrote:
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
Its not clear what you are asking for.execute().fetchall() already returns
a list of rows, where each row has attributes, so that you can say
row.attrname, so it is already like an object.If you use a query(), you
have the choice of specifying an ORM mapped class or individual columns as
well, though if you are querying for all individual columns there's not much
difference between query(x, y, z).all() versus using execute().fetchall().
When you say change it, if that means, I'd like to set attributes on the
resulting objects and they go back to the database, that's clearly not
possible unless you can relate your stored procedure rows to an ORM mapped
class, since SQLAlchemy knows nothing about how your stored procedure gets
data or how that data would be modified.
If you can illustrate fully what kind of interface to the data you'd like to
see that be helpful.
a=session.execute(assp_ReportDailyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719})
b=session.query(a.keys()).from_statement(exec
assp_ReportDailyTransactions @start_date=:start,
@end_date=:end).params({'start':20100701,'end':20100719}).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 873, in
query
return self._query_cls(entities, self, **kwargs)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 92, in
__init__
self._set_entities(entities)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 99, in
_set_entities
entity_wrapper(self, ent)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 2584, in
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 20, 6:02 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 6:19 PM, Lukasz Szybalski wrote:
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
Its not clear what you are asking for. execute().fetchall() already
returns a list of rows, where each row has attributes, so that you can say
row.attrname, so it is already like an object. If you use a query(),
you have the choice of specifying an ORM mapped class or individual columns
as well, though if you are querying for all individual columns there's not
much difference between query(x, y, z).all() versus using
execute().fetchall().
When you say change it, if that means, I'd like to set attributes on the
resulting objects and they go back to the database, that's clearly not
possible unless you can relate your stored procedure rows to an ORM mapped
class, since SQLAlchemy knows nothing about how your stored procedure gets
data or how that data would be modified.
If you can illustrate fully what kind of interface to the data you'd like to
see that be helpful.
Sorry for not being clear.
Instead of typing manually column names
(column1,column2,...column38 inside the query() I would like to
use previous query .keys() to list them there
Instead doing:
myresults=session.query('column1','column2','column3').from_statement
I would like to do a=session.execute(...) and then
myresults=session.query(a.keys()).from_statement() where a.keys()
returns a list of all the column names from the stored procedure, but
unfortunately passing a list like a.keys() gives me an error. If I
type it in it works fine. How can I pass in these column names ?
Should I convert a.keys() to dictionary, or some other type?
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
I tried fetchall but when I loop over the rows, I was getting a
dictionary and instead of doing row.column1, I had to use row[0].
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 9:39 PM, Lukasz Szybalski wrote:
Instead of typing manually column names
(column1,column2,...column38 inside the query() I would like to
use previous query .keys() to list them there
Instead doing:
myresults=session.query('column1','column2','column3').from_statement
I would like to do a=session.execute(...) and then
myresults=session.query(a.keys()).from_statement() where a.keys()
returns a list of all the column names from the stored procedure, but
unfortunately passing a list like a.keys() gives me an error. If I
type it in it works fine. How can I pass in these column names ?
Should I convert a.keys() to dictionary, or some other type?
how is calling query(colmames)...all() significantly different from simply
saying execute(..).fetchall() ? you get a list of named-tuple like objects in
both cases.
Anyway, the column names are not available until you execute your string
statement and cursor.description is accessed. So if you really were in the
mood for this , you could say:
result = Session.execute(stmt)
query = Session.query(*[column(name) for name in
result.keys()]).instances(result)
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
I tried fetchall but when I loop over the rows, I was getting a
dictionary and instead of doing row.column1, I had to use row[0].
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 15, 3:35 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Any idea what the proper format should be? I was able to connect to the mssql database vi instance name on linux. I've updated the docs on how to setup your odbc dsn connection string: http://lucasmanual.com/mywiki/unixODBC Now that I'm connected how do I execute, pass in variables like start and end date? Thanks, Lucas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 19, 10:29 am, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 3:35 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Any idea what the proper format should be? I was able to connect to the mssql database vi instance name on linux. I've updated the docs on how to setup your odbc dsn connection string:http://lucasmanual.com/mywiki/unixODBC Now that I'm connected how do I execute, pass in variables like start and end date? Hello, I can do this: l=session.execute(assp_ReportDailyTransactions) but this returns all the data, In both pyODBC and ceODBC I can run the following which will return proper data range based on start and end date parameters. a=cursor.execute(assp_ReportDailyTransactions @start_date=?, @end_date=?,20100701,20100719) but how do I convert that to sqlalchemy like format: This gives the following error: session.execute(assp_ReportDailyTransactions @start_date=?, @end_date=?,20100701,20100719) Traceback (most recent call last): File stdin, line 1, in module File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in execute engine = self.get_bind(mapper, clause=clause, **kw) File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 837, in get_bind c_mapper = mapper is not None and _class_to_mapper(mapper) or None File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/util.py, line 636, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class ''20100719'' is not mapped Let me know, Thanks, Lucas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 19, 2010, at 5:38 PM, Lukasz Szybalski wrote:
On Jul 19, 10:29 am, Lukasz Szybalski szybal...@gmail.com wrote:
On Jul 15, 3:35 pm, Lukasz Szybalski szybal...@gmail.com wrote:
On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote:
take a look
at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?...
Now, Could you give me an example of it ? I understand how to run func
to get values like current timestamp, or countbut how to run a
stored proc?
print
func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715')
??
Is this the format? or?
Any idea what the proper format should be?
I was able to connect to the mssql database vi instance name on linux.
I've updated the docs on how to setup your odbc dsn connection
string:http://lucasmanual.com/mywiki/unixODBC
Now that I'm connected how do I execute, pass in variables like start
and end date?
Hello,
I can do this:
l=session.execute(assp_ReportDailyTransactions)
but this returns all the data,
In both pyODBC and ceODBC I can run the following which will return
proper data range based on start and end date parameters.
a=cursor.execute(assp_ReportDailyTransactions @start_date=?,
@end_date=?,20100701,20100719)
but how do I convert that to sqlalchemy like format:
Session.execute() accepts strings that are converted to text():
session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
This gives the following error:
session.execute(assp_ReportDailyTransactions @start_date=?,
@end_date=?,20100701,20100719)
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in
execute
engine = self.get_bind(mapper, clause=clause, **kw)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 837, in
get_bind
c_mapper = mapper is not None and _class_to_mapper(mapper) or None
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/util.py, line 636, in
_class_to_mapper
raise exc.UnmappedClassError(class_or_mapper)
sqlalchemy.orm.exc.UnmappedClassError: Class ''20100719'' is not
mapped
Let me know,
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Re: how to run a stored procedure?
On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Thanks, Lucas On 07/15/2010 12:29 PM, Lukasz Szybalski wrote: Hello, I need to get data out of sqlserver by running this stored procedure. From there I convert few fields and add the processed rows to a mysql table that I have created. How can I do the following in sqlalchemy? Is there a pythonic way? Assuming I already have the database connection? What would be the syntax to run a stroed procedure? And what object would the results be in? USE [Reports] GO DECLARE �...@return_value int EXEC @return_value = [dbo].[assp_Report_DailyTransactions] �...@start_date = N'07/01/2010', �...@end_date = N'07/15/2010' SELECT 'Return Value' = @return_value Thanks, Lucas -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Re: How to run a stored procedure?
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use
it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm
not
sure how to access the return values? They exactly match one of
the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Email on the pyodbc mailing list for instructions on how to execute a
stored procedure.The information there will guide how this is done
with SQLAlchemy.
Daniel wrote:
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they
are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS
claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in
function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function().
On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how
to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but
I'm not
sure how to access the return values? They exactly match one
of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct
which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Michael,
I can execute a stored procedure from SQLAlchemy, but I can't get a
result set back out of SQLAlchemy. I've verified that the SP executes
as expected and I know that it's returning a result set. I'm
following what's been suggested on this forum
http://groups.google.com/group/sqlalchemy/browse_thread/thread/12e3360ee7efe15d
and http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html.
What type of question do you think I should ask on the pyodbc forum?
It seems the issue is that a bug that was fixed in a previous version
of SQLAlchemy has crept back in. I might be able to fix it with a
little help, but things have changed quite a bit since the changeset
that fixed it last time(see previous post on this thread) and I'm not
sure where to start.
On May 11, 9:32 am, Michael Bayer mike...@zzzcomputing.com wrote:
Email on the pyodbc mailing list for instructions on how to execute a
stored procedure. The information there will guide how this is done
with SQLAlchemy.
Daniel wrote:
Any reply on this? Should I submit a new bug report?
On May 8, 11:49 am, Daniel daniel.watr...@gmail.com wrote:
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they
are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py,
line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS
claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in
function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function().
On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com
wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how
to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but
I'm not
sure how to access the return values? They exactly match one
of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct
which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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
[sqlalchemy] Re: How to run a stored procedure?
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
On mssql, when I run the query, either calling it by name or calling
EXEC and then the procedure name, it returns the result. The problem
is that if I try to call result.fetchall(), it give this error:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
I know that when I execute the query in the MSSQL environment it
returns a result set, but sqlalchemy doesn't seem to get the result
set.
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Daniel wrote:
On mssql, when I run the query, either calling it by name or calling
EXEC and then the procedure name, it returns the result. The problem
is that if I try to call result.fetchall(), it give this error:
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
I know that when I execute the query in the MSSQL environment it
returns a result set, but sqlalchemy doesn't seem to get the result
set.
its again a DBAPI interaction issue which either some of the MSSQL folks
here could chime in to help with or if you could figure out how to get
what you want from a raw pyodbc connection
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases,
running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use
it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as
follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm
not
sure how to access the return values? They exactly match one of
the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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] Re: How to run a stored procedure?
I've just been looking through the code in mssql.py and the change
mentioned in the changeset I mentioned isn't there anymore. I also
can't see that's it's been abstracted to a parent class. Is there a
possibility that this bug has crept back in?
If so, let me know where the sensible place would be to include the
EXEC keyword in order to return result sets for MSSQL stored
procedures, or if there would be a better approach.
Thanks.
On May 8, 11:24 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I just found this
thread:http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg08048.html
which corresponds to this
changeset:http://www.sqlalchemy.org/trac/changeset/4159
It seems that this issue has come up in the past. I've tried the
following modified query:
result = conn.execute('EXEC claim_highest_priority_work')
which should satisfy the regexp, but it still produces the closed
cursor error.
Not sure if this helps you help me...
On May 8, 11:09 am, Daniel daniel.watr...@gmail.com wrote:
Michael,
I'm not sure if this helps, but I've noticed that if my stored
procedure returns all null values, then I can fetch them. If they are
non-null values I get an error:
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
1668, in fetchone
self.connection._handle_dbapi_exception(e, None, None,
self.cursor, self.context)
File C:\Python25\Lib\site-packages\sqlalchemy\engine\base.py, line
931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) Attempt to use a closed cursor.
None None
[Dbg] result = conn.execute('claim_highest_priority_work')
[Dbg] print result.fetchone()
(None, None, None, None, None)
Any idea why this would be?
On May 8, 10:46 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Thanks Michael,
When I try that it produces this SQL
SELECT claim_highest_priority_work() AS claim_highest_priority_work_1
and this error
'claim_highest_priority_work' is not a recognized built-in function
name.
this is more of an MSSQL how to issue then. on most databases, running
a function and returning results looks like SELECT function(). On
oracle, its SELECT function() FROM DUAL. What is it on MSSQL ?
On May 8, 10:20 am, Michael Bayer mike...@zzzcomputing.com wrote:
Daniel wrote:
Hello,
I've created a stored procedure in MSSQL. I'm not sure how to use it
in SQLAlchemy. The stored procedure is called
'claim_highest_priority_work' and I can call it directly as follows:
result = conn.execute('claim_highest_priority_work')
I know it runs because I see the result in the database, but I'm not
sure how to access the return values? They exactly match one of the
tables I've defined.
What's the right way to map/execute a stored procedure and
subsequently access what it returns?
you probably want to select from it. an expression construct which
achieves this would be:
select([func.claim_highest_priority_work()])
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
