Python SQL Server database loop not working

Question

Using Python looping through a number of SQL Server databases creating tables using select into, but when I run the script nothing happens i.e. no error messages and the tables have not been created. Below is an extract example of what I am doing. Can anyone advise?

df = [] # dataframe of database names as example
for i, x in df.iterrows():
 
 SQL = """
 Drop table if exists {x}..table
 Select
 Name
 Into
 {y}..table
 From
 MainDatabase..Details
 """.format(x=x['Database'],y=x['Database'])
 
 cursor.execute(SQL)
 conn.commit()

Answer 1

Looks like your DB driver doesn't support multiple statements behavior, try to split your query to 2 single statements one with drop and other with select:

for i, x in df.iterrows():
 drop_sql = """
 Drop table if exists {x}..table
 """.format(x=x['Database'])
 select_sql = """
 Select
 Name
 Into
 {y}..table
 From
 MainDatabase..Details
 """.format(x=x['Database'], y=x['Database'])
 cursor.execute(drop_sql)
 cursor.execute(select_sql)
 cursor.commit()

And second tip, your x=x['Database'] and y=x['Database'] are the same, is this correct?

• CGarden: Thank you @frost-nzcr4. Regarding your second tip, yes you are correct they are the same. And I am using Microsoft Sql Server, if that wasn't clear.
• CGarden: I've tested it and its working great. Thank you. Out of interest what's the logic behind your comment that the DB driver doesn't support multiple behavior? Why do they not? Thank you.
• frost-nzcr4: Python has a DB API specification python.org/dev/peps/pep-0249 where execute was documented as "Prepare and execute a database operation (query or command)" and technically your query contains 2 operation of different nature DDL for DROP and DML for SELECT. Each DB server has (or does not have) its own driver for this Python DB API and may have different behavior for these types of operations and may (or may not) handle multiple operations for a single query.
• CGarden: Thank you. And what was the second tip?
• frost-nzcr4: You can omit y by simply using x as many times as you need with format, like"{x} {x} {x}".format(x=x['Database'])