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['selected_kw']
client='泡麵'
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\/456.csv",encoding='utf-8')
# df = pd.read_csv(f,header=None, names=['kw', 'url'])
df = pd.read_csv(f,header=None, names=['prefix', 'positive','rnd','URL'])
df=df.fillna('')
# print(df)
url='cliqproducts.com.tw'

for index,row in df.iterrows():
    # print(row['kw'],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':url})

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

db.close()

####先從雲端下載csv 再用記事本打開另存一個csv#########