Python实现数据库并行读取和写入实例

max_process = 16 #最大进程数def read_SQL_write(r_host,r_port,r_user,r_passwd,r_db,r_charset,w_host,w_port,w_user,w_passwd,w_db,w_charset,cmd,index=None):  #得到tem字典保存着信息  try:    conn = pymysql.Connect(host=r_host, port=r_port, user=r_user, passwd =r_passwd, db =r_db, charset =r_charset)    cursor = conn.cursor()    cursor.execute(cmd)  except Exception as e:    error = "[-][-]%d fail to connect SQL for reading" % index    log_error('error.log',error)    return   else:    tem = cursor.fetchone()    print('[+][+]%d succeed to connect SQL for reading' % index)  finally:    cursor.close()    conn.close()    try:    conn = pymysql.Connect(host=w_host, port=w_port, user=w_user, passwd =w_passwd, db =w_db, charset =w_charset)    cursor = conn.cursor()    cursor.execute(cmd)  except Exception as e:    error = "[-][-]%d fail to connect SQL for writing" % index    log_error('error.log',error)    return   else:    print('[+][+]%d succeed to connect SQL for writing' % index)      r_dict = dict()  r_dict['id'] = tem[0]  r_dict['content_id'] = tem[1]  r_dict['pub_date'] = tem[2]  r_dict['title'] = cht_to_chs(tem[3])  r_dict['title_score'] =tem[4]![](http://images2015.cnblogs.com/blog/1172464/201706/1172464-20170609000900309-1810357590.png)  r_dict['news_content'] = cht_to_chs(tem[5])  r_dict['content_score'] = tem[6]    for key in stock_dict.keys():    #能找到对应的股票    if stock_dict[key][1] and ( r_dict['title'].find(stock_dict[key][1])!=-1 or r_dict['news_content'].find(stock_dict[key][1])!=-1 ):      w_dict=dict()      w_dict['code'] = key      w_dict['english_name'] = stock_dict[key][0]      w_dict['cn_name'] = stock_dict[key][1]      #得到分数      if r_dict['title_score']:        w_dict['score']=r_dict['title_score']      else:        w_dict['score']=r_dict['content_score']            #开始写入      try:        global max_process        cmd = "INSERT INTO dyx_stock_score%d VALUES ('%s', '%s' , %d , '%s' , '%s' , %.2f );" % /          (index%max_process ,r_dict['content_id'] ,r_dict['pub_date'] ,w_dict['code'] ,w_dict['english_name'] ,w_dict['cn_name'] ,w_dict['score'])        cursor.execute(cmd)        conn.commit()      except Exception as e:        error = "  [-]%d fail to write to SQL" % index        cursor.rollback()        log_error('error.log',error)      else:        print("  [+]%d succeed to write to SQL" % index)  cursor.close()  conn.close()def main():  num = 238143#数据库查询拿到的总数  p = None  for index in range(1,num+1):    if index%max_process==1:      if p:        p.close()        p.join()      p = multiprocessing.Pool(max_process)    r_cmd = ('select id,content_id,pub_date,title,title_score,news_content,content_score from dyx_emotion_analysis where id = %d;' % (index))    p.apply_async(func = read_SQL_write,args=(r_host,r_port,r_user,r_passwd,r_db,r_charset,w_host,w_port,w_user,w_passwd,w_db,w_charset,r_cmd,index,))  if p:    p.close()    p.join()