前言
由於硬體等各種原因需要把大概170多萬2t左右的微博圖片資料存到Mysql中.之前存微博資料一直用的非關係型資料庫mongodb，由於對Mysql的各種不熟悉，踩了無數坑，來來回回改了3天才完成。
PS:(本人長期出售超大量微博資料、旅遊網站評論資料，並提供各種指定資料爬取服務，Message to YuboonaZhang@Yahoo.com。同時歡迎加入社交媒體資料交流群：99918768)
挖坑填坑之旅
建表
存資料的時候首先需要設計資料庫,我準備設計了3個表
微博表：[id, userid, blog_text, lat, lng, created_time, reserve] pkey: id
圖片表：[md5, pic_url, pic_bin, exif, reserve] pkey: md5
關係表：[id, md5, reserve] pkey: (id, md5) fkey: (id, 微博表id) (md5, 圖片表md5)
建表的時候別的問題都還好，主要是 pic_bin 的型別和 blog_text 的型別有很大的問題，首先是pic_bin的型別，開始設定的為BLOB，但是執行之後發現BLOB最大隻能存1M的資料，並不能滿足微博圖片的儲存，後改成MEDIUMBLOB(16M)基本能夠滿足要求了。再後來就是blog_text，我遇到的第一個大坑
開始的時候很自然的設定blog_text的型別為TEXT，但跑起來發現有些資料存不進去，會報錯，經篩查發現是有些微博文字中包含了emoji表情…隨後找了很多資料發現是因為utf8下文字是三位元組，但是emoji是四位元組，需要將編碼改成utf8mb4。然而我在mac上整mysql的配置檔案報各種奇葩錯誤,一怒之下把TEXT改成了BLOB，就好了。因為本地是MAC，我要連線到遠端的一臺Windows上才能通過那個Windows連線到群暉的Mysql上…本地配置改了也白改。
存圖片
然後這就是一個大坑！！！ 由於我使用的python3，所以讀取圖片得到的二進位制的結果前面會有一個b`, 表示bytes，正是由於這個b`導致sql語句拼接的時候這個b後面的單引號會和sql語句的引號結合，導致後面的二進位制沒有在引號裡面出錯！二進位制編碼又不像string可以對字元轉義，試了好多方法都不行！最後沒有辦法使用base64 對二進位制進行加密轉化成字串，存到資料庫中，然後要用時的時候再解密。
pic_bin = str(base64.b64encode(pic_bin))[2:-1]改配置檔案
由於使用Python多程式,一個小時8G資料量，圖片資料比較大，發包的時候回超過mysql的預設限制，出現Mysql server has gone away, 這個時候要改配置檔案，在配置檔案中引數
max_allowed_packet = 600M
wait_timeout = 60000
Lost connection to Mysql server during query
程式跑著跑著總會出現這個錯誤，一直找原因，試了各種辦法看了好多資料，一直都是錯誤。實在不知道什麼原因了…後來一想，我管他什麼原因，失去連線之後重新連線就行了。使用conn.Ping(True) 判斷是否連線mysql成功。如果失去連線就重新連線就行了！最後解決了這個問題
程式碼實現
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Created by Baoyi on 2017/10/16
from multiprocessing.pool import Pool
import pymysql
import requests
import json
import exifread
from io import BytesIO
import configparser
import hashlib
import logging
import base64
# 配置logging
logging.basicConfig(level=logging.WARNING,
format=`%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s`,
datefmt=`%a, %d %b %Y %H:%M:%S`,
filename=`weibo.log`,
filemode=`w`)
cf = configparser.ConfigParser()
cf.read("ConfigParser.conf")
# 讀取配置mysql
db_host = cf.get("mysql", "db_host")
db_port = cf.getint("mysql", "db_port")
db_user = cf.get("mysql", "db_user")
db_pass = cf.get("mysql", "db_pass")
db = cf.get("mysql", "db")
# 建立連線
conn = pymysql.connect(host=db_host, user=db_user, passwd=db_pass, db=db, port=db_port, charset=`utf8`)
# 獲取遊標
cursor = conn.cursor()
# 建立insert_sql
insert_blog_sql = (
"INSERT IGNORE INTO blog(userid, id, blog_text, lat, lng, created_time) VALUES(`{uid}`, `{id}`,`{blog_text}`,`{lat}`,`{lng}`,`{created_time}`)"
)
insert_pic_sql = (
"INSERT IGNORE INTO pics(pic_url, pic_bin, md5, exif) VALUES (`{pic_url}`,`{pic_bin}`,`{md5}`,`{exif}`)"
)
insert_relationship_sql = (
"INSERT IGNORE INTO relationship(id, md5) VALUES (`{id}`,`{md5}`)"
)
uid = []
with open(`./data/final_id.txt`, `r`) as f:
for i in f.readlines():
uid.append(i.strip(`
`))
# 處理圖片資料
def handle_pic(pic_url):
large_pic_url = pic_url.replace(`thumbnail`, `large`)
large_bin = requests.get(large_pic_url)
return large_bin.content
def get_poiid_info(uid):
try:
url = `https://api.weibo.com/2/statuses/user_timeline.json`
load = {
`access_token`: `xxxxxxxxxx`,
`uid`: uid,
`count`: 100,
`feature`: 2,
`trim_user`: 1
}
get_info = requests.get(url=url, params=load, timeout=(10, 10))
if get_info.status_code != 200:
logging.warning(ConnectionError)
pass
info_json = json.loads(get_info.content)
info_json[`uid`] = uid
statuses = info_json[`statuses`]
# 處理篩選微博資料
for status in statuses:
id = status[`idstr`]
if status[`geo`] is not None:
lat = status[`geo`][`coordinates`][0]
lng = status[`geo`][`coordinates`][1]
pic_urls = status[`pic_urls`]
# 判斷是否在北京
if (115.7 < lng < 117.4) and (39.4 < lat < 41.6):
# 若在北京,插入blog資料進庫
blog_text = status[`text`].replace(```, ````)
created_time = status[`created_at`]
try:
cursor.execute(
insert_blog_sql.format(uid=uid, id=id, blog_text=blog_text, lat=lat, lng=lng,
created_time=created_time))
except pymysql.err.OperationalError as e_blog:
logging.warning(e_blog.args[1])
pass
# conn.commit()
# 處理圖片
for pic_url in pic_urls:
# 獲取原圖片二進位制資料
pic_bin = handle_pic(pic_url[`thumbnail_pic`])
# 讀取exif 資料
pic_file = BytesIO(pic_bin) # 將二進位制資料轉化成檔案物件便於讀取exif資料資訊和生成MD5
tag1 = exifread.process_file(pic_file, details=False, strict=True)
tag = {}
for key, value in tag1.items():
if key not in (
`JPEGThumbnail`, `TIFFThumbnail`, `Filename`,
`EXIF MakerNote`): # 去除四個不必要的exif屬性，簡化資訊量
tag[key] = str(value)
tags = json.dumps(tag) # dumps為json型別 此tag即為exif的json資料
# 生成MD5
MD5 = hashlib.md5(pic_file.read()).hexdigest()
# 首先把二進位制圖片用base64 轉成字串之後再存
try:
cursor.execute(
insert_pic_sql.format(pic_url=pic_url[`thumbnail_pic`].replace(`thumbnail`, `large`),
pic_bin=str(base64.b64encode(pic_bin))[2:-1], md5=MD5,
exif=tags))
except pymysql.err.OperationalError as e_pic:
logging.warning(e_pic.args[1])
pass
try:
cursor.execute(insert_relationship_sql.format(id=id, md5=MD5))
except pymysql.err.OperationalError as e_relation:
logging.warning(e_relation)
pass
conn.commit()
else:
logging.info(id + " is Not in Beijing")
pass
else:
logging.info(id + ` Geo is null`)
pass
except pymysql.err.OperationalError as e:
logging.error(e.args[1])
pass
def judge_conn(i):
global conn
try:
conn.ping(True)
get_poiid_info(i)
except pymysql.err.OperationalError as e:
logging.error(`Reconnect`)
conn = pymysql.connect(host=db_host, user=db_user, passwd=db_pass, db=db, charset=`utf8`)
get_poiid_info(i)
def handle_tuple(a_tuple):
read_uid_set = []
for i in a_tuple:
read_uid_set.append(i[0])
return set(read_uid_set)
if __name__ == `__main__`:
sql_find_uid = (
"SELECT userid FROM blog"
)
cursor.execute(sql_find_uid)
read_uid_tuple = cursor.fetchall()
read_list = handle_tuple(read_uid_tuple)
print(len(read_list))
new_uid = set(uid).difference(read_list)
print(len(new_uid))
pool = Pool()
pool.map(judge_conn, list(new_uid))
撒一波廣告
本人長期出售抓取超大量微博資料的程式碼，並提供微博資料打包出售，Message to YuboonaZhang@Yahoo.com