pyodbc and insert statements

Question

I'm using pyodbc quite short period of time and now faced with the problem of applying sql script from file which performs some view creation. For applying sql file I use sample from another thread - Follow up: Execute .sql files from python Have no problems with majority of my sql scripts but this one causes a problem:

part of script:

insert into TMP_VIEWS select * from TMP_QUERY_SQL
go
begin
declare @SQL_CMD varchar(4000);
declare @CNT int;
set @CNT = (select count(1) from TMP_VIEWS);
while @CNT > 0
begin 
  set @SQL_CMD = (select top 1 SQL_CMD from TMP_VIEWS order by SQL_CMD);
  print 'Executing: ' + @SQL_CMD;
  EXEC(@SQL_CMD)
  delete from TMP_VIEWS where @SQL_CMD = SQL_CMD;
  set @CNT = (select count(1) from TMP_VIEWS);
end
end

As you see it performs execution of SQL statements that were inserted to table from another table. So in the way I apply it it doesn't work. Sample from another thread devides sql file into blocks between GO and applyes them. So 'insert into TMP_VIEWS select * from TMP_QUERY_SQL' applied separately and then other part. It looks like pyodbc or driver doesn't actually wait for full execution of insert on server and second block is executed before actuall insert is comlete. As a result I have quite random amount of views created and non-creted queries remained in TMP_VIEWS. I have autocommit and tried to add additional commits after query execution - this doesn't help. The only thing that helps - add time.sleep(0.2) beetween execution of this batches between GO. Looks like some asynchronuous call. Who faces with the same problem or what may be wrong in my attempt? May be some workaround?

Thanks!

score 0 · Answer 1 · answered Oct 10 '15 at 14:48

If you just need a solution for this particular script, then do the following: read all the SQL statements from the TMP_QUERY_SQL table into a Python list. Then call excecute on each statement as you iterate through the list, and avoid any dependence on the temporary SQL table TMP_VIEWS.

pyodbc and insert statements

1 Answers1