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.

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?

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

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.

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.

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.

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.

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.

not currently squashing any errors inside a try/except

Thanks for the assistance and time.

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

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.