DataCenterGame/example/update_media_info.py

from model.sql_models import DB
from config.using_config import quchen_text, dm
import pandas
import requests
import time


def update_media_info():
    # 1.按用户维度获取图片
    sql = '''
                select foo.account_id ,access_token ,image_ids from 
        (select account_id ,group_concat(image_id) as image_ids from adcreative_info ai 
        where is_video =0 and length (account_id )>1
        group by account_id ) as foo
        left join
        (select account_id,access_token,'MP' as type from advertiser_vx av 
        union
        select account_id ,access_token,'GDT' as type from advertiser_qq aq ) as foo2
        on foo.account_id=foo2.account_id
        order by foo.account_id
        limit 10
    '''
    df = pandas.read_sql(sql=sql, con=db_qc.engine)
    print(df)
    for row in df.rows():
        # 2.图片重复去除

        print(row)
        user_sql = '''
            select * from
            where 
        '''

        # 3.获取图片信息(腾讯),图片信息(大小,格式)


from PIL import Image
from io import BytesIO


def update_image_info():
    db_qc = DB(config=quchen_text)
    db_dm = DB(config=dm)
    while True:
        user_sql = '''
         select * from image_info ii 
        where preview_url is not null 
        and (size is null or type is null)
        limit 100
        '''
        df = pandas.read_sql(user_sql, con=db_qc.engine)
        if len(df) == 0:
            break
        for index, row in df.iterrows():
            try:
                print(row['preview_url'])
                rsp = requests.get(row['preview_url'])
                # 1.图片写入内存
                im = Image.open(BytesIO(rsp.content))
                # 2.获取图片属性
                image_format = im.format
                image_size = len(rsp.content)
                # 3.数据进行存储
                sql = ''' update image_info set size={},type='{}'
                            where image_id = '{}'  '''.format(image_size, image_format, row['image_id'])
                db_qc.session.execute(sql)
                db_qc.session.commit()
            except Exception as e:
                time.sleep(1)
                print(e)


if __name__ == '__main__':

    # update_media_info()
    import threading

    for i in range(5):
        thread_update = threading.Thread(target=update_image_info, )
        thread_update.start()