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 -~----------~----~----~----~------~----~------~--~---
