CODEWITHSUNDEEP
pythonsqlsql-server
CGarden
CGarden

Reputation: 347

Python SQL Server database loop not working

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

Upvotes: 0

Views: 196

Answers (1)

frost-nzcr4
frost-nzcr4

Reputation: 1620

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?

Upvotes: 1

Related Questions