unicode query strings? (sqlalchemy 0.5.0beta1, pyodbc, mssql)

[Jeremiah]

Hello. I am having a little problem getting sqlalchemy working. I am
running mac os x 10.5, sqlalchemy 0.5beta1, pyodbc, mssql 2005. i
have a simple insert example like below. This runs with no errors but
inserts no data. If I try to autload a table's data it gives me the
following error: "No results. Previous SQL was not a query."

import sqlalchemy as sa

engine = sa.create_engine(connect-string, echo=True)

#a meta data object defined elsewhere
c.metadata.bind = engine

sa_q = c.dimAsset.insert()
print sa_q
sa_q.execute(ColumName = 'Some Value')


I watched these connections via SQL Server Profiler. I compared the
the above script against the same query when run through pyodbc.

via sql alchemy
---------------------------
declare @p1 int
set @p1=13808
exec sp_prepare @p1 output,NULL,N'',1
select @p1

via pyodbc
-----------------------
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 VARCHAR(11)',N'insert into
schemax.tableName (ColumnName) values(@P1)',1
select @p1


The actual string defining the query is lost with sql alchemy. I can
get similar results with pyodbc (without using parameters) by passing
a unicode version of the query string. All queries passed by sql
alchemy show up as empty strings according to the profiler. I assume
that there is some character formatting mismatch somewhere that causes
this but I am at a loss to say where: sql alchemy, pyodbc, odbc driver
(Actual Technologies), the db server itself.

Any ideas/solutions would be much appreciated.

Thanks.

[Rick Morrison]

Prima facie, I have no idea why this should be acting this way. You listed the SQL as seen through profiler, what did you get for statement echos from the "echo=True" on your engine -- does the query appear there?

[Michael Bayer]

On Jul 11, 2008, at 5:59 PM, Jeremiah wrote:

>

> The actual string defining the query is lost with sql alchemy. I can
> get similar results with pyodbc (without using parameters) by passing
> a unicode version of the query string. All queries passed by sql
> alchemy show up as empty strings according to the profiler. I assume
> that there is some character formatting mismatch somewhere that causes
> this but I am at a loss to say where: sql alchemy, pyodbc, odbc driver
> (Actual Technologies), the db server itself.

any kind of encoding issue would raise an error immediately. You need
to do some more experimentation, and or produce a more complete test
case illustrating the behavior you're seeing for us to have any
further insight. Experiments include: what happens if you execute a
plain string with the engine , i.e. engine.execute("insert into table
values (:foo)", foo='bar') ? try it with unicode bind params ? what
happens if you execute against a sqlite database instead of MSSQL?
what happens if you purposefully use a nonexistint column name with
the insert().execute() parameters ? are you squashing errors within a
try/except ? etc.

[Jeremiah]

> Prima facie, I have no idea why this should be acting this way. You listed
> the SQL as seen through profiler, what did you get for statement echos from
> the "echo=True" on your engine -- does the query appear there?

Yes, the query does appear there when echoed. I can take this query
and paste it into a sql management studio and it runs fine. I can
also take the generated query and use pyodbc to execute the query. In
addition I just tested running the generated query using the sql
alchemy engine.execute() method and it runs fine.

> any kind of encoding issue would raise an error immediately.  You need  
> to do some more experimentation, and or produce a more complete test  
> case illustrating the behavior you're seeing for us to have any  
> further insight.  Experiments include:  what happens if you execute a  
> plain string with the engine , i.e. engine.execute("insert into table  
> values (:foo)", foo='bar') ?  try it with unicode bind params ?  what  

at your suggestion I tried executing this with the builtin
engine.execute() method and it seems to work fine. That surprises me.

the table I have defined is just a test table with one String()
column. I am not trying to use any unicode bind params. It just
seems to me that since the query string is what gets lost or mangled
it is some type of character encoding mismatch but I submit I know
very little about unicode. Nevertheless I will test unicode params
tomorrow and post results.

> happens if you execute against a sqlite database instead of MSSQL?  

i will do further testing against sqlite tomorrow but an initial test
a few days ago let me run the create_all() method. It did throw an
error when I tried to specify a schema, something like database not
found. I will post more when I have it.

> what happens if you purposefully use a nonexistent column name with  

> the insert().execute() parameters ?  are you squashing errors within a  

Using a nonexistent column name curiously produces no errors and
runs. The output (when echo=True for the engine) and the trace are a
bit different though. It produces a query that specifies no columns
or parameters. The profiler just shows a series of commits / empty
strings / roll backs being processed.

> try/except ?  etc.

not currently squashing any errors inside a try/except

Thanks for the assistance and time.

[Michael Bayer]

On Jul 13, 2008, at 7:05 PM, Jeremiah wrote:

>
> Using a nonexistent column name curiously produces no errors and
> runs. The output (when echo=True for the engine) and the trace are a
> bit different though. It produces a query that specifies no columns
> or parameters. The profiler just shows a series of commits / empty
> strings / roll backs being processed.

ok, that one aspect here might be SQLA's fault, in that it's ignoring
the nonexistent column name instead of raising an error.

I think at this point you should try some pdb stepping just to isolate
exactly where things are getting lost. I'm pretty sure you either
have something weird going on with your DBAPI driver or you're somehow
using the insert() construct incorrectly (though your sample code
looked fine). this is not an issue we've seen before.