import os
import csv
import pymysql
conn=pymysql.connect(host='xx.xx.xx.xx',port=3306,user='root',password='password',db='weibo',charset='utf8mb4') # 数据库连接信息
cursor=conn.cursor()
for root,dirs,files in os.walk(r"E:\"): #遍历目录
    for f in files:
        if f.split(".")[-1] == "csv":
            user_id=f.split(".")[0]
            csv_file=open(os.path.join(root,f),"r",encoding='utf-8')
            csv_reader=csv.reader(csv_file)
            csv_comment=list(csv_reader)
#             print("test")
            for t in csv_comment:
                if t[0].find("微博id") > -1:
                    continue
                insert="INSERT INTO `weibo` (`id`, `user_id`, `content`, `article_url`, `original_pictures`, `retweet_pictures`, `original`, `video_url`, `publish_place`, `publish_time`, `publish_tool`, `up_num`, `retweet_num`, `comment_num`) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}','{10}', '{11}', '{12}', '{13}');".format(t[0],user_id,t[1],t[2],t[3],"NULL","1",t[4],t[5],t[6],t[7],t[8],t[9],t[10]) #对应的插入语句
                try:
                    cursor.execute(insert)
                    conn.commit()
                    print("成功")
                except:
                    print("失败")

import os
import hashlib
from PIL import Image

def update_info(path,f,root):  #查询图片信息，生成SQL语句
    image_name=os.path.splitext(f)[0]
    image_extension=f.split(".")[-1]
    file=open(path,"rb")
    image_size=os.path.getsize(path)
    im=Image.open(path)
    image_width=im.size[0]
    image_height=im.size[1]
    image_date=path.split("\\")[2]+'-'+path.split("\\")[3]+"-"+path.split("\\")[4]+" 12:00:00"
    image_user_id=1
    image_uploader_ip="60.211.13.123"
    image_md5=hashlib.md5(file.read()).hexdigest()
    image_original_filename=f
    i_th=os.path.join(root,image_name+".th."+f.split(".")[-1])
    i_md=os.path.join(root,image_name+".md."+f.split(".")[-1])
    if(os.path.exists(i_th) and os.path.exists(i_md)):
        image_chain=7
        image_thumb_size=os.path.getsize(i_th)
        image_medium_size=os.path.getsize(i_md)
    else:
        image_chain=5
        image_thumb_size=os.path.getsize(i_th)
        image_medium_size=0
    info="INSERT INTO `chv_images` (`image_id`, `image_name`, `image_extension`, `image_size`, `image_width`, `image_height`, `image_date`, `image_date_gmt`, `image_title`, `image_description`, `image_nsfw`, `image_user_id`, `image_album_id`, `image_uploader_ip`, `image_storage_mode`, `image_storage_id`, `image_md5`, `image_source_md5`, `image_original_filename`, `image_original_exifdata`, `image_views`, `image_category_id`, `image_chain`, `image_thumb_size`, `image_medium_size`, `image_expiration_date_gmt`, `image_likes`, `image_is_animated`) VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{5}', '{6}', NULL, '0', '1', NULL, '60.211.13.123', 'datefolder', NULL, '{7}', NULL, '{8}', NULL, '0', NULL, '{9}', '{10}', '{11}', NULL, '0', '0');".format(image_name,image_extension,image_size,image_width,image_height,image_date,image_name[:99],image_md5,f,image_chain,image_thumb_size,image_medium_size)
    return info

#遍历图片储存文件夹，所有SQL语句写入文本
total =0
new_info=open("update_info.txt","w")
for root,dirs,files in os.walk(r"D:\images"):#这里写路径
    for f in files:
        #print(f)
        if f.split(".")[-1] not in ["jpg","png"]:
            #print(f.split(".")[-1])
            continue
        if f.split(".")[-2] in ["th","md"]:
            #print(f.split(".")[-2])
            continue
        #print(root)
        str_info=update_info(os.path.join(root,f),f,root)
        #print(str_info)
        new_info.write(str_info)
        new_info.write("\n")
        total=total+1
        print(total)
        #print(f)