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():
    while True:
        user_sql='''
         select * from image_info ii 
        where preview_url is not null 
        and size 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__':
    db_qc = DB(config=quchen_text)
    db_dm = DB(config=dm)
    # update_media_info()
    update_image_info()