Thanks for the reply -
A cast works fine here, but it is a workaround that would be nice to
avoid. I'll see what kind of behavior I get by dropping back to just
pyodbc. My other concern is that all numeric parameters are being
treated as varchar and undergoing implicit conversions in SQL Server -
which for whatever reason won't fly inside date functions hence the
error.
This also exposes a bit of a misconception on my part: I had always
assumed that parameterized queries were simply parse/validation
operations handled by the connection API which then sent massaged SQL
to the db. Not so! I feel like I just learned that you have to put oil
in your car. What else don't I know?
Are there more robust ways to get SA running with SQL Server than
pyodbc->unixODBC->FreeTDS?
Oh, and 'select x = foo' is just a T-SQL construct for column
aliasing, works the same as 'select foo as x'...
On Jun 8, 10:25 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> Malherbe wrote:
>
> > I'm new to SQLAlchemy (and Python in general) and have been struggling
> > to get a basic execution wrapper working - the goal is to use of raw
> > batches of parameterized SQL - no ORM.
>
> > The engine initializes and I can run queries through it without a
> > problem, however the generated SQL from the execute statement seems to
> > interpret everything as string data.
>
> > The line:
>
> > engine.execute(text("select result = dateadd(day, :days, getdate())"),
> > dict(days = 7))
>
> > ...yields the following error:
>
> > ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
> > Server]Argument data type varchar is invalid for argument 2 of dateadd
> > function. (8116) (SQLPrepare)') 'select result = dateadd(day, ?,
> > getdate())' [7]
>
> > Am I overlooking something simple here? Is there a way to have the
> > engine substitute parameters as per the Python datatype?
>
> parameters work by passing them to the execute() or executemany() method
> of the underlying DBAPI cursor. SQLAlchemy usually does not affect these
> values except in specific cases, such as unicode strings that need to be
> encoded to utf-8 or dates that needs to be turned into strings. For plain
> python types like ints, floats, etc. the DBAPI is usually able to handle
> these straight through and SQLAlchemy doesn't affect them. Unfortunately,
> you're using FreeTDS, which is the most buggy and difficult DBAPI out
> there (and SQL server the most finnicky database), so the issue probably
> lies in some combination of SQL Server and FreeTDS not liking what is
> being sent. Also the format of the SQL you're using "select x = foo()"
> seems unusual but I'm not deeply familiar with the quirks of SQL server.
>
> you might want to try a raw DBAPI script both with FreeTDS and perhaps
> without, using Pyodbc, to narrow the issue down to FreeTDS. There also
> might be a CAST() syntax you can add to your statement - something along
> the lines of "dateadd(day, CAST ? AS INT, getdate())".
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
