kw_tools/SEO/csv_to_sql.py

import pandas as pd
import dataset
import pymysql
pymysql.install_as_MySQLdb()

# df = pd.read_csv(r"C:\/Users\/s1301\/Documents\/關鍵字建議.csv",engine='python')


db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/seo?charset=utf8mb4')
table=db['seo_jobs']
client='護佳'
domain=''
# for index,row in df.iterrows():
# with open("C:\/Users\/s1301\/Documents\/關鍵字建議.csv") as f:
#     data_all = f.readlines()
f = open("C:\/Users\/s1301\/Documents\/關鍵字建議.csv")
df = pd.read_csv(f)
df=df.fillna('')
# print(df)
for index,row in df.iterrows():
    prefix='"'+row['prefix']+'"'
    positive='"'+row['positive']+'"'
    domain='"'+row['URL']+'"'
    rnd='"'+str(row['rnd'])+'"'
    postfix='""'
    data = f'"prefix":{prefix},"postfix":{postfix},"positive":[{positive}],"domain":[{domain}],"rnd":[{rnd}]'
    json='{'+data+'}'
    print(json)
    table.insert({'cust':client,'plan':'形象SEO','json':json})
    # table.insert({'term':row['kw'],'client':client,'domain':domain})

{"prefix": "護佳", "postfix": "", "domain": ["www.hujiai-international.com"], "positive": ["細胞食物","紅蔘"], "rnd": [""]}

db.close()