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']
table=db['select_kw']
client='loginheart'
domain=''
# for index,row in df.iterrows():
# with open("C:\/Users\/s1301\/Documents\/新飛國際遊學SEO - 關鍵字12.08.csv") as f:
#     data_all = f.readlines()
# print(data_all)
f = open("C:\/Users\/s1301\/Documents\/關鍵字建議.csv",encoding='utf-8')
df = pd.read_csv(f,header=None, names=['kw', 'url'])
df=df.fillna('')
# print(df)
for index,row in df.iterrows():
    print(row['kw'],row['url'])
    # 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':row['url']})

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

db.close()