一些国家贸易信息的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()