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']
# table=db['sns_kw']
# table=db['select_kw']
client='歌林'
# domain='naturalbenefits-hpp'
# 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("/Users/mac/Downloads/_2024歌林關鍵字 - 工作表1.csv")
# df = pd.read_csv(f,header=None, names=['kw', 'url'])
df = pd.read_csv(f,header=None, names=['kw'])
# df = pd.read_csv(f,header=None, names=['prefix','id', 'positive','domain','rnd'])
df=df.fillna('')
# print(df)
domain='kolin.com.tw'
lst=[]
for index,row in df.iterrows():
    # print(row)
    # prefix='"'+row['prefix']+'"'
    # # positive='"'+row['positive']+'"'
    # positive=row['positive']
    # domain='"'+row['domain']+'"'
    # rnd='"'+str(row['rnd'])+'"'
    # postfix='""'
    # id=row['id']
    # data = f'"id":{id},"prefix":{prefix},"domain":[{domain}],"postfix":{postfix},"positive":[{positive}],"rnd":[{rnd}]'
    # json='{'+data+'}'
    # print(json)
    # table.insert({'cust':client,'plan':'形象SEO','json':json})
    table.insert({'term':row['kw'],'client':client,'domain':domain})
    # table.insert({'term': row['kw'], 'client': client, 'url': row['url']})

db.close()

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