insert python sql into sql db

Original query should work with proper SQL that does not have brackets enclosing the INSERT INTO column list. Additionally, executemany should not require any for loop across data frame rows and can be faster than df.to_sql. Also, table and column identifiers should not use single quotes.

By the way, for easier multi-line strings, consider Python’s triple quote string. Do note, since Pandas v0.24, .to_numpy is recommended method over .values (see docs). Below assumes data frame contains exactly 32 columns that each map to corresponding table column.

sql = """INSERT INTO DWWorking.dbo.api_Nic_Data 
            ([a],[b],[c],[d],[e],[f],[g],[h],[i],[j],
             [k],[l],[m],[n],[o],[p],[q],[r],[s],[t],
             [u],[v] [w],[x],[y],[z],[ab],[cd],[ef],[gh],[ij],[kl])
         VALUES (?,?,?,?,?,?,?,?,?,?,
                 ?,?,?,?,?,?,?,?,?,?,
                 ?,?,?,?,?,?,?,?,?,?,?,?)
      """ 

cursor.executemany(sql, t6.to_numpy().tolist())