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
