[sqlalchemy] Querying from MS SQL Server functions
Hello,
I have been trying to find the appropriate syntax to query from a user
defined function. I am dealing with a SQL Server 2008 database, and I am
using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is
running on is Windows 7 64 bit, but I am using 32 bit python. I followed
the pointers in this
threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ,
but it seems that I can't get sqlalchemy to behave properly. The sql I am
trying to execute looks like the following
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT)
Note that there are no parameter names and the usage of the Default
keyword. Null can be used in place of the Default, but that could be
problematic if a parameter's default value isn't null. Furthermore, this
function can be joined to tables within the database like so
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT) uf
left outer join db_table dt on uf.table_id = dt.id
Is there anyway to handle this situation? Below are the attempts that I
have tried
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'),
bindparam('effective_date_end', value=None,quote=False),
bindparam('widget_type_list', value='3'),
bindparam('company_id', value=638),
bindparam('widget_id', value=None,quote=False))])
This generates the following SQL
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:effective_date_start, :effective_date_end,
:widget_type_list, :company_id, :widget_id)
however I get no results from executing it. I have also tried not using
bindparam
function_call =
select([column('emp_id'),column('plan_type'),column('effective_date'),column('history_answersheet_id')],
from_obj=[func.some_user_defined_function('2013-12-01', None, '1', 1591,
None)])
and then I get
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:some_user_defined_function_1, NULL,
:some_user_defined_function_2, :some_user_defined_function_3, NULL)
but again no results. (As a side note I am attempting to use a session to
execute these objects.
To eliminate the possiblity that the issue is within pyodbc I tried the
following
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass')
cursor = cnxn.cursor()
cursor.execute(select * from some_user_defined_function('2013-12-29',
NULL, '3', 638, NULL))
for row in cursor:
print row
and that did work.
So I have two questions.
1. Why can't I see my results from the select objects I am using?
2. How can I pass Default as a parameter to the function?
Thanks in advance
--Dan Clark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SQLAlchemy 0.9.0 Released
Hey list - SQLAlchemy release 0.9.0 is now available. After about a year's worth of development, 0.9.0 is the first official release of the 0.9 series of SQLAlchemy. As always, 0.9 includes many major architectural improvements and new features. To get an overview of SQLAlchemy 0.9's new features, see the What's New in 0.9 document at http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html. This document should also serve as a guide for those behavioral changes which may require adjustments to existing applications. Highlights of SQLAlchemy 0.9.0 include in-place Python 3 support, major improvements to the eager loading system including a more expressive API and critical performance improvements to the rendering of more complex joins, support for Postgresql JSON types, SQL expression improvements such as textual selectable objects, INSERT from SELECT support, improved APIs for SELECT..FOR UPDATE, schema and DDL improvements, and many other refinements. Architecturally, the move to Python 3 in place has allowed lots of consolidation to take place, and there has also been a major reorganization of module layout both within Core and ORM. Existing production deployments which have not yet been tested in SQLAlchemy 0.9.0 should ensure that they specify a version less than 0.9.0 in their requirements files, as version 0.9.0 now becomes the default version of SQLAlchemy downloaded from Pypi when no version specifics are given. Full changelog for the SQLAlchemy 0.9 series is at http://docs.sqlalchemy.org/en/rel_0_9/changelog/changelog_09.html; this document will also link out in many cases to the migration document mentioned above. Download SQLAlchemy 0.9.0 at: http://www.sqlalchemy.org/download.html and on Pypi at https://pypi.python.org/pypi/SQLAlchemy/0.9.0. Happy new year! - mike signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] Re: Querying from MS SQL Server functions
Nevermind about question 2. I really thought I was using parameters that
would return something. Apparently not. However, question 2 still remains.
--Dan
On Monday, December 30, 2013 7:12:04 PM UTC-5, Dan wrote:
Hello,
I have been trying to find the appropriate syntax to query from a user
defined function. I am dealing with a SQL Server 2008 database, and I am
using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is
running on is Windows 7 64 bit, but I am using 32 bit python. I followed
the pointers in this
threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ,
but it seems that I can't get sqlalchemy to behave properly. The sql I am
trying to execute looks like the following
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT)
Note that there are no parameter names and the usage of the Default
keyword. Null can be used in place of the Default, but that could be
problematic if a parameter's default value isn't null. Furthermore, this
function can be joined to tables within the database like so
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT) uf
left outer join db_table dt on uf.table_id = dt.id
Is there anyway to handle this situation? Below are the attempts that I
have tried
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'),
bindparam('effective_date_end', value=None,quote=False),
bindparam('widget_type_list', value='3'),
bindparam('company_id', value=638),
bindparam('widget_id', value=None,quote=False))])
This generates the following SQL
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:effective_date_start,
:effective_date_end, :widget_type_list, :company_id, :widget_id)
however I get no results from executing it. I have also tried not using
bindparam
function_call =
select([column('emp_id'),column('plan_type'),column('effective_date'),column('history_answersheet_id')],
from_obj=[func.some_user_defined_function('2013-12-01', None, '1', 1591,
None)])
and then I get
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:some_user_defined_function_1, NULL,
:some_user_defined_function_2, :some_user_defined_function_3, NULL)
but again no results. (As a side note I am attempting to use a session to
execute these objects.
To eliminate the possiblity that the issue is within pyodbc I tried the
following
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass')
cursor = cnxn.cursor()
cursor.execute(select * from some_user_defined_function('2013-12-29',
NULL, '3', 638, NULL))
for row in cursor:
print row
and that did work.
So I have two questions.
1. Why can't I see my results from the select objects I am using?
2. How can I pass Default as a parameter to the function?
Thanks in advance
--Dan Clark
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Querying from MS SQL Server functions
On Dec 30, 2013, at 7:12 PM, Dan wpu.cl...@gmail.com wrote:
Hello,
I have been trying to find the appropriate syntax to query from a user
defined function. I am dealing with a SQL Server 2008 database, and I am
using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is
running on is Windows 7 64 bit, but I am using 32 bit python. I followed the
pointers in this thread, but it seems that I can't get sqlalchemy to behave
properly. The sql I am trying to execute looks like the following
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT)
Note that there are no parameter names and the usage of the Default keyword.
Null can be used in place of the Default, but that could be problematic if a
parameter's default value isn't null. Furthermore, this function can be
joined to tables within the database like so
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT) uf
left outer join db_table dt on uf.table_id = dt.id
Is there anyway to handle this situation? Below are the attempts that I have
tried
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'),
bindparam('effective_date_end', value=None,quote=False),
bindparam('widget_type_list', value='3'),
bindparam('company_id', value=638),
bindparam('widget_id', value=None,quote=False))])
“DEFAULT” is a fixed keyword here so you’d be looking specifically to not bind
any value from the application into a bound placeholder (e.g. a question mark
?).
To deliver a fixed keyword, use a construct like “literal_column()”. In
addition, you don’t typically need to use bindparam() explicitly unless you are
looking to re-use the statement repeatedly . Using a literal Python value will
automatically be coerced into a bound parameter.
from sqlalchemy.sql import column, select, func, literal_column
function_call = select([
column('table_id'),
column('widget_type'),
column('effective_date'),
column('widget_id')]).\
select_from(
func.some_user_defined_function(
2013-12-01,
literal_column(DEFAULT),
3,
638,
literal_column(DEFAULT)
)
)
result = session.execute(function_call)
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:effective_date_start, :effective_date_end,
:widget_type_list, :company_id, :widget_id)
this is the SQL that you’d see from printing the statement or turning it into a
string. however, if you’re passing the construct to session.execute(), and are
watching the SQL output using echo=True (which I recommend when debugging these
things), you’d see question marks with Pyodbc, not parameters like
:effective_date_start.
if you set echo=‘debug’ on your create_engine() you’ll see not just the SQL
emitted but also the rows that are received in the raw.
To eliminate the possiblity that the issue is within pyodbc I tried the
following
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass')
cursor = cnxn.cursor()
cursor.execute(select * from some_user_defined_function('2013-12-29',
NULL, '3', 638, NULL))
for row in cursor:
print row
if you’re still testing against pyodbc, please try it like this:
cursor.execute(select * from some_user_defined_function(?, NULL, ?, ?, NULL)”,
[‘2013-12-29’, ‘3’, 638])
as that is more accurately what would be sent from SQLAlchemy.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Querying from MS SQL Server functions
Yes, column_literal did the trick.
So this is what I have
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function('2013-12-01',
literal_column(DEFAULT), '1', 1550, literal_column(DEFAULT))])
Then I was able to figure out how to join it to my widgets table. Since
the from clause isn't a typical one the selectable must be aliased
otherwise sqlalchemy throws up an error.
a = alias(function_call, 'test')
results = DBSession.query(Widget).join(a, Widget.id==
a.c.widget_id).all()
My code is pretty rough but it works. Thanks again Michael.
--Dan
On Monday, December 30, 2013 7:36:40 PM UTC-5, Michael Bayer wrote:
On Dec 30, 2013, at 7:12 PM, Dan wpu@gmail.com javascript: wrote:
Hello,
I have been trying to find the appropriate syntax to query from a user
defined function. I am dealing with a SQL Server 2008 database, and I am
using sqlalchemy 0.8.4 with pyodbc 3.0.7. The machine the python app is
running on is Windows 7 64 bit, but I am using 32 bit python. I followed
the pointers in this
threadhttps://groups.google.com/forum/#!searchin/sqlalchemy/call$20database$20function/sqlalchemy/tYVxitn9j1A/L5URn6ryHWUJ,
but it seems that I can't get sqlalchemy to behave properly. The sql I am
trying to execute looks like the following
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT)
Note that there are no parameter names and the usage of the Default
keyword. Null can be used in place of the Default, but that could be
problematic if a parameter's default value isn't null. Furthermore, this
function can be joined to tables within the database like so
select * from some_user_defined_function('2013-12-29', DEFAULT, '3', 638,
DEFAULT) uf
left outer join db_table dt on uf.table_id = dt.id
Is there anyway to handle this situation? Below are the attempts that I
have tried
function_call =
select([column('table_id'),column('widget_type'),column('effective_date'),column('widget_id')],
from_obj=[func.some_user_defined_function(bindparam('effective_date_start',value='2013-12-01'),
bindparam('effective_date_end', value=None,quote=False),
bindparam('widget_type_list', value='3'),
bindparam('company_id', value=638),
bindparam('widget_id', value=None,quote=False))])
“DEFAULT” is a fixed keyword here so you’d be looking specifically to not
bind any value from the application into a bound placeholder (e.g. a
question mark ?).
To deliver a fixed keyword, use a construct like “literal_column()”. In
addition, you don’t typically need to use bindparam() explicitly unless you
are looking to re-use the statement repeatedly . Using a literal Python
value will automatically be coerced into a bound parameter.
from sqlalchemy.sql import column, select, func, literal_column
function_call = select([
column('table_id'),
column('widget_type'),
column('effective_date'),
column('widget_id')]).\
select_from(
func.some_user_defined_function(
2013-12-01,
literal_column(DEFAULT),
3,
638,
literal_column(DEFAULT)
)
)
result = session.execute(function_call)
SELECT table_id, widget_type, effective_date, widget_id
FROM some_user_defined_function(:effective_date_start,
:effective_date_end, :widget_type_list, :company_id, :widget_id)
this is the SQL that you’d see from printing the statement or turning it
into a string. however, if you’re passing the construct to
session.execute(), and are watching the SQL output using echo=True (which I
recommend when debugging these things), you’d see question marks with
Pyodbc, not parameters like :effective_date_start.
if you set echo=‘debug’ on your create_engine() you’ll see not just the
SQL emitted but also the rows that are received in the raw.
To eliminate the possiblity that the issue is within pyodbc I tried the
following
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=10.0.0.555;DATABASE=yup;UID=user;PWD=pass')
cursor = cnxn.cursor()
cursor.execute(select * from some_user_defined_function('2013-12-29',
NULL, '3', 638, NULL))
for row in cursor:
print row
if you’re still testing against pyodbc, please try it like this:
cursor.execute(select * from some_user_defined_function(?, NULL, ?, ?,
NULL)”, [‘2013-12-29’, ‘3’, 638])
as
