python 传入sql

一些国家贸易信息的csv传入SQL

conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 10.0};SERVER=192.168.2.188;DATABASE=india1;UID=sa;PWD=123456')
cursor = conn.cursor()

#创建数据库
for type1 in ["export"]:
    for year in ['2009']:  
        y = str(int(year) - 1)
        name = type1 + '_' + year
        table = 'create table {}(S.No nvarchar(MAX),Year nvarchar(MAX),Month nvarchar(MAX),Flow nvarchar(MAX),COUNTRY nvarchar(MAX),Product_Code nvarchar(MAX),Product_Num nvarchar(MAX),y_Usd nvarchar(MAX),year_Usd nvarchar(MAX),y_Quantity nvarchar(MAX),year_Quantity nvarchar(MAX),Unit nvarchar(MAX))'.format(name)



        cursor.execute(
        '''
        create table {} (
            S_No nvarchar(MAX),
            y nvarchar(MAX),   
            Year nvarchar(MAX),
            Month nvarchar(MAX),
            Flow nvarchar(MAX),
            COUNTRY nvarchar(MAX),
            Product_Code nvarchar(MAX),
            Product_Num nvarchar(MAX),
            y_Usd nvarchar(MAX),
            year_Usd nvarchar(MAX),
            y_Quantity nvarchar(MAX),
            year_Quantity nvarchar(MAX),
            Unit nvarchar(MAX)
        )'''.format(name)
        )

        conn.commit()
        print(y,'ok')


for roots,dirs,files in os.walk('ceshi/exports/2009/'):
    print("555")
    for filename in files:   
        print(filename)

        if filename.split('--')[-1] == 'final.csv':
            df = pd.read_csv('ceshi/exports/2009/' + filename,encoding='gb2312')
            for indexs in df.index:
                 l = df.loc[indexs].values                                               
                 l[5] = str(l[5]).split(".")[0]

                 l[6] = str(l[6]).split(".")[0]

                 if len(str(l[5])) == 7:
                     l[5] = str(0) + str(l[5])
                 if len(str(l[6])) == 7:
                     l[6] = str(0) + str(l[6]) 
                 print(l[5])
                 print(l[6])
                 table = type1 + '_' + str(year)

                 sql_insert = "insert into " + table + " (S_No, Year,y,Month, Flow,COUNTRY,Product_Code, Product_Num,y_Usd,year_Usd,y_Quantity, year_Quantity, Unit) values('" + str(
                    l[0]) + "','" + str(l[1]) + "','" + str(y) + "','" + str(l[2]) + "','" + str(l[3]) + "','" + str(l[4]) + "','" + str(
                    l[5]) + "','" + str(l[6]) + "','" + str(l[7]) + "','" + str(l[8]) + "','" + str(l[9]) + "','" + str(l[10]) + "','" + str(l[11]) + "')"

            cursor.execute(sql_insert)
            print('插入成功')
            conn.commit()