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()

frost-nzcr4 · Accepted Answer · 2021-06-01 11:24:43Z

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?

answered Jun 1, 2021 at 11:24

frost-nzcr4

1,62012 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CGarden Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

Thank you. And what was the second tip?

frost-nzcr4 Over a year ago

You can omit y by simply using x as many times as you need with format, like"{x} {x} {x}".format(x=x['Database'])

Collectives™ on Stack Overflow

Python SQL Server database loop not working

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related