guopengfa
发布于 2020-05-28 / 805 阅读 / 0 评论 / 0 点赞

pandas保存为sql表

import pandas as pd
import os
import pymysql
import datetime
from sqlalchemy import create_engine

if name == 'main':
filepath = "W:\GUOPENGFA"
files = os.listdir(filepath)
file_list = []
for file in files:
if(".csv" in file):
file_list.append(r"W:\GUOPENGFA%s"%(file))
df = pd.DataFrame(columns=["标识","FBA可用库存","国际在途","short_days","update_time1"])
times = 0
conn = pymysql.connect(
host = "192.168.24.104",
port = 3306,
user = "user",
passwd = "passwd",
db = "fba"
)
conn1 = create_engine("mysql+pymysql://192.168.24.104:3306/fba?user=root&passwd=1111aaaa")
conn2 = pymysql.connect(
host = "10.0.0.0",
port = 10036,
user = "user",
passwd = "passwd",
db = "mrp_py"
)
data2 = pd.read_sql(con=conn2)
skuprice = pd.read_csv(r"D:\Python\数据分析\datasource\525最新采购价格.csv")
skuprice.drop_duplicates(inplace=True)
for file in file_list:
data1 = pd.read_csv(file)
# print(data2.info())
sale_data = data1.update_time1[0]
sale_data = datetime.datetime.strptime(sale_data,"%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d")
data2_head1 = []
for i in data2.keys():
data2_head1.append(i)
if i == sale_data:
break
data2_head = ["index","标识"]
for head in data2_head1[-60:]:
data2_head.append(head)
data2 = data2[data2_head]
data2["sale_60"] = data2.iloc[:,2:].sum(axis=1)
data2["日销加权"] = data2["sale_60"]/60
data1.drop_duplicates(inplace=True)
data1 = data1[["标识","FBA可用库存","国际在途","short_days","update_time1","erp_sku"]]
data1 = pd.merge(data1,skuprice,how="left",left_on="erp_sku",right_on="sku")
data1.drop(columns="sku",axis=1,inplace=True)
data3 = pd.merge(data2,data1,how="left",on="标识")

    data4 = {}
    data4["有销无库存listing(断货listing计数)"] = data3.loc[(data3["sale_60"] > 0) & (data3["FBA可用库存"] == 0)]["标识"].shape[0]
    data4["有销总库存listing(正常售卖listing)"] = data3.loc[data3["sale_60"] > 0].shape[0]
    data4["时间"] = sale_data
    data4 = pd.DataFrame([data4])
    data4.to_sql(con=conn1,name="fba_listing_breaking_num",if_exists="append")
    times+=1
    print(times)
conn.close()

评论