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
> thread<https://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 that is more accurately what would be sent from SQLAlchemy.
>
--
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.
