123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114 |
- import requests
- import cv2
- import sys
- import pandas
- from model.sql_models import DB
- from config.using_config import quchen_text, dm
- def update_video_info():
-
- sql = '''
- select * from video_info vi
- where length (preview_url )>0;
- '''
- df = pandas.read_sql(sql=sql, con=db_qc.engine)
- print(df)
- for index, row in df.iterrows():
-
-
- video_url = row['preview_url']
- rsp = requests.get(video_url)
- with open('aa.mp4', 'wb') as f:
- f.write(rsp.content)
- video_size = len(rsp.content)
- cap = cv2.VideoCapture('aa.mp4')
- if cap.isOpened():
- rate = cap.get(5)
- frame_num = cap.get(7)
- print(frame_num, rate)
- duration = frame_num / rate
- print(video_size, duration)
-
- byte_rate = (video_size/(duration/8))
- print(byte_rate)
-
- sql = '''
- update video_info
- set size={},video_length={},type='mp4',byte_rate={}
- where video_id='{}'
- '''.format(video_size, duration, byte_rate, row['video_id'])
- db_qc.session.execute(sql)
- db_qc.session.commit()
- def update_byte_rate():
- sql = '''
- select * from video_info vi
- where length (preview_url )>0 and not type
- '''
- df = pandas.read_sql(sql=sql, con=db_qc.engine)
- print(df)
- for index, row in df.iterrows():
-
-
- video_url = row['preview_url']
- video_size = row['size']
- duration = row['video_length']
-
- byte_rate = (video_size / (duration / 8))
- print(byte_rate)
-
- sql = '''
- update video_info
- set size={},video_length={},type='mp4',byte_rate={}
- where video_id='{}'
- '''.format(video_size, duration, byte_rate, row['video_id'])
- db_qc.session.execute(sql)
- db_qc.session.commit()
- def update_video_info_pro():
- from model.sql_models import DB
- from config import using_config_test, using_config
- db_qc = DB(config=using_config.quchen_text)
- db_qc_test = DB(config=using_config_test.quchen_text)
- sql_get = '''
- select
- video_id,size, video_length, byte_rate, video_meta_data,download_path
- from video_info
- where LENGTH (preview_url )>1 and download_path is not null;
- '''
- cursor=db_qc_test.session.execute(sql_get)
- id_dict={}
- for line in cursor.fetchall():
- video_id,size, video_length, byte_rate, video_meta_data, download_path=line
- id_dict[video_id]=(size, video_length, byte_rate, video_meta_data,download_path)
- for k,v in id_dict.items():
- video_id=k
- size, video_length, byte_rate, video_meta_data,download_path=v
- sql_update = '''
- update video_info
- set size={}, video_length={},
- type='mp4', byte_rate={}, video_meta_data='{}',
- download_path='{}'
- where
- video_id='{}'
- '''.format(size, video_length, byte_rate, video_meta_data, download_path, video_id)
- db_qc.session.execute(sql_update)
- db_qc.session.commit()
- if __name__ == '__main__':
- db_qc = DB(config=quchen_text)
- db_dm = DB(config=dm)
-
-
- update_video_info_pro()
|