mysql脚本范例_pymysql操作mysql的脚本示例

#!/usr/bin/env python

#-*- coding:UTF-8 -*-

from multiprocessing import Process , Queue

from queue import Queue,Empty as QueueEmpty

import subprocess

import os

import sys

import time

import datetime

import re

import pymysql

import logging

db_cdb20 = None

cursor_cdb20 = None

db_sdb30 = None

cursor_sdb30 = None

sql_dict = {}

center_ipaddr = None

center_http_port = None

C2_SERIES_TABLE = 'c2_series';

C2_PROGRAM_TABLE = 'c2_program';

C2_MAP_SERIES_PROGRAM_TABLE = 'c2_map_series_program'

C2_PRODUCT_TABLE = 'c2_product'

C2_MOVIE_TABLE = 'c2_movie'

C2_MOVIE_MAP_TABLE = 'c2_map_program_movie'

C2_PICTURE_TABLE = 'c2_picture'

C2_PICTURE_MAP_TABLE = 'c2_map_picture_program'

C2_SERIES_UNIMPORT_STATUS = 0;

C2_LIMIT = 100

CDB20_PRO_TABLE = 'program'

CDB20_PRO_SERIES_TABLE = 'programseries'

CDB20_MEDIA_TABLE = 'mediacontent'

CDB20_PRO_MEDIA_TABLE = 'programmediacontent'

CDB20_PIC_TABLE = 'metapicture'

CDB20_PIC_MAP_TABLE = 'picturemap'

CDB20_MEDIA_SERIEL_TYPE = '26'

logging.basicConfig(

level=logging.DEBUG,

format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',

datefmt='%Y/%m/%d %H:%M:%S',

filename='/var/log/sunlight/c2.log',

filemode='a'

);

def insert_opt(db,cursor,tablename,data_dict,lastinsertid=0):

marks = ','.join(['%s'] * len(data_dict))

fields = ','.join(data_dict.keys())

insert_values = [x if x else '' for x in list(data_dict.values())]

insert_sql = "INSERT INTO %s (%s) VALUES (%s)" % (tablename, fields, marks)

cursor.execute(insert_sql, insert_values)

db.commit()

logging.info("\n --sql-- %s \n --value-- %s \n --result-- %d " % (insert_sql, insert_values, cursor.rowcount))

return cursor.rowcount if not lastinsertid else cursor.lastrowid

def convert_c2_series(db_read,cursor_read,import_field,db_write,cursor_write):

global center_ipaddr

global center_http_port

query_c2_series = "SELECT ss.`code`, ss.`name`, ss.`sort_name` AS `titlesortname`, ss.`search_name` AS `titleserachname`, \

ss.`createtime` AS `createdate`, ss.`status`, ss.`description`, ss.`volumncount`, \

ss.`licensing_window_start` AS `licensingwindowstart`, ss.`licensing_window_end` AS `licensingwindowend`, ss.`labels` AS `tag`, ss.`rating`, \

sp.`language`, sp.`release_year` AS releaseyear, sp.`actor_display` AS `actorsdisplay`, sp.`writer_display` AS `writerdisplay`, \

sp.`original_country` AS `country`, sp.`genre` AS `genres`, st.`price` FROM c2_series AS ss \

LEFT JOIN (select * from c2_map_series_program group by seriescode) AS smp ON ss.`code` = smp.seriescode \

LEFT JOIN c2_program AS sp ON smp.programcode = sp.code \

LEFT JOIN c2_product AS st ON st.productid = ss.productid \

WHERE ss.`%s` = '0'" % import_field

logging.info("[ query_c2_series ] : %s" % query_c2_series)

cursor_read.execute(query_c2_series)

series_lists = cursor_read.fetchall()

if series_lists is not None:

for series_list in series_lists:

check_query = "SELECT `programid` FROM program WHERE name = '%s' " % series_list['name']

print(check_query)

logging.info("[ check_query ] : %s" % check_query)

cursor_write.execute(check_query)

get_query = cursor_write.fetchone()

if get_query :

logging.info("get_query programid: %s, skip..." % get_query['programid'])

continue

series_code = series_list.get('code',0)

del series_list['code']

series_list['series_flag'] = 1

series_list['lastupdatedate'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

series_list['rating'] = series_list['rating'] if series_list['rating'] else 0.0

programid = insert_opt(db_write,cursor_write,'program',series_list,1)

if programid:

logging.info("insert program success! [ series - code ]: %s , [ programid ] %s" % (series_code, programid))

#insert program table finished. 以上

query_c2_series_program = "SELECT sp.`name`, sp.`createtime`, sp.`status`,sp.`code`,sp.`productid`,mv.`duration`, smp.`sequence`, pic.`localurl`, pic.`status` AS `picstatus`, \

pic.`createtime` AS `piccreatetime` \

from c2_series AS ss \

LEFT JOIN c2_map_series_program AS smp ON ss.code = smp.seriescode \

LEFT JOIN c2_program AS sp ON smp.programcode = sp.code \

LEFT JOIN c2_map_program_movie AS mmp ON sp.code = mmp.programcode \

LEFT JOIN c2_movie AS mv ON mmp.moviecode = mv.code \

LEFT JOIN c2_map_picture_program AS picmap ON sp.code = picmap.programcode \

LEFT JOIN c2_picture AS pic ON picmap.picturecode = pic.code \

WHERE pic.status = 1 AND picmap.type = 2 AND ss.code = '%s'" % series_code

cursor_read.execute(query_c2_series_program)

query_seriesprogram_lists = cursor_read.fetchall()

if query_seriesprogram_lists:

for query_seriesprogram_list in query_seriesprogram_lists:

query_seriesprogram = "SELECT `programseriesid` FROM %s WHERE `name` = '%s'" % ('programseries', query_seriesprogram_list['name'] )

cursor_write.execute(query_seriesprogram)

if cursor_write.fetchone():

logging.info("query programseriesid from programseries is ok, skipping... [ name ] %s" % query_seriesprogram_list['name'])

continue

ps_lists = {}

ps_lists['name'] = query_seriesprogram_list['name'] if query_seriesprogram_list['name'] else ''

ps_lists['programid'] = programid

ps_lists['lastupdatetime'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%i:%s")

ps_lists['productid'] = query_seriesprogram_list['productid'] if query_seriesprogram_list['productid'] else ''

ps_lists['createtime'] = query_seriesprogram_list['createtime'] if query_seriesprogram_list['createtime'] else ''

ps_lists['sequence'] = query_seriesprogram_list['sequence'] if query_seriesprogram_list['sequence'] else ''

programseriesid = insert_opt(db_write,cursor_write,'programseries',ps_lists,1)

if programseriesid:

#beigin to insert media and content;

logging.info("insert programseries ok! [ programseriesid ] : %d" % programseriesid)

query_mediacontent_name = "SELECT `mediacontentid` FROM mediacontent WHERE `contentname` = '%s'" % query_seriesprogram_list['name']

cursor_write.execute(query_mediacontent_name)

if cursor_write.fetchone():

logging.info("query mediacontentid from mediacontent is ok, skipping... [ name ] %s" % query_seriesprogram_list['name'])

continue

mediacontent_list = {}

mediacontent_list['contentname'] = query_seriesprogram_list['name']

mediacontent_list['duration'] = query_seriesprogram_list['duration'] if query_seriesprogram_list['duration'] else ''

mediacontent_list['createdate'] = query_seriesprogram_list['createtime'] if query_seriesprogram_list['createtime'] else ''

mediacontent_list['status'] = query_seriesprogram_list['status'] if query_seriesprogram_list['status'] else '1'

mediacontent_list['lastupdatetime'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%i:%s")

mediacontent_list['extcontentid'] = query_seriesprogram_list['code'] if query_seriesprogram_list['code'] else ''

mediacontent_lastid = insert_opt(db_write,cursor_write,'mediacontent',mediacontent_list,1)

if mediacontent_lastid:

logging.info("insert mediacontent success! [ mediacontent_lastid ]: %d" % mediacontent_lastid)

programmediacontent_list = {}

programmediacontent_list['objecttype'] = CDB20_MEDIA_SERIEL_TYPE

programmediacontent_list['objectid'] = programseriesid

programmediacontent_list['mediacontentid'] = mediacontent_lastid

programmedia_rowcount = insert_opt(db_write,cursor_write,'programmediacontent',programmediacontent_list)

if programmedia_rowcount:

logging.info("insert programmediacontent success! [ program - name ] : %s" % query_seriesprogram_list['name'])

picture_list = {}

picture_list['picturename'] = query_seriesprogram_list['localurl'].replace("127.0.0.1", center_ipaddr + ":" + center_http_port)

picture_list['fileurl'] = query_seriesprogram_list['localurl'].replace("127.0.0.1", center_ipaddr + ":" + center_http_port)

picture_list['createdate'] = query_seriesprogram_list['piccreatetime']

picture_list['picturetype'] = 2 #缩略图

picture_lastid = insert_opt(db_write,cursor_write,'metapicture',picture_list,1)

if picture_lastid:

logging.info("inset metapicture success! [ name ]: %s" % query_seriesprogram_list['name'])

picturemap_list = {}

picturemap_list['metapictureid'] = picture_lastid

picturemap_list['sequence'] = 1

picturemap_list['objecttype'] = CDB20_MEDIA_SERIEL_TYPE

picturemap_list['objectid'] = programseriesid

picturemap_rowcount = insert_opt(db_write,cursor_write,'picturemap',picturemap_list,1)

if picturemap_rowcount:

logging.info("insert picturemap success! programname:%s" % query_seriesprogram_list['name'])

update_sql = "UPDATE %s SET %s = 1 WHERE name='%s'" % ('c2_program', import_field,query_seriesprogram_list['name'] )

cursor_read.execute(update_sql)

db_read.commit()

else:

logging.info("insert picturemap failed! programname:%s" % query_seriesprogram_list['name'])

else:

logging.info("inset metapicture failed! [ name ]: %s" % query_seriesprogram_list['name'])

else:

logging.info("insert programseries failed! [ programid ] : %d" % programid)

update_sql = "UPDATE %s SET %s = 1 WHERE code='%s'" % ('c2_series',import_field,series_code)

cursor_read.execute(update_sql)

db_read.commit()

else:

logging.info("insert program failed! [ series - code ]: %s , [ programid ] %s" % (series_code, programid))

def convert_c2_program(db_read,cursor_read,import_field,db_write,cursor_write):

global center_ipaddr

global center_http_port

query_vod_sql = "SELECT p.code,p.name, p.sort_name as titlesortname, search_name as titleserachname, pt.price, p.createtime as createdate, \

p.status,p.description,p.language,p.actor_display as actorsdisplay, p.writer_display as writerdisplay, \

p.licensing_window_start as licensingwindowstart, p.licensing_window_end as licensingwindowend , \

p.rating,p.genre as genres,p.labels as tag, p.release_year as releaseyear, p.original_country as country, \

v.duration, \

pic.localurl, pic.status as picstatus, pic.createtime as piccreatedate \

FROM c2_program AS p \

LEFT JOIN c2_product AS pt ON p.productid = pt.productid \

LEFT JOIN c2_map_program_movie AS vp ON p.code = vp.programcode \

LEFT JOIN c2_movie AS v ON vp.moviecode = v.code \

LEFT JOIN c2_map_picture_program AS pmp ON p.code = pmp.programcode \

LEFT JOIN c2_picture AS pic ON pmp.picturecode = pic.code \

WHERE pic.status = 1 AND pmp.type = 2 AND p.series_flag = '0' AND p.`%s` = '0'" % import_field

cursor_read.execute(query_vod_sql)

program_lists = cursor_read.fetchall()

if program_lists is not None:

for list in program_lists:

#将记录插入program表；

if list['name']:

query_sql = "SELECT programid FROM %s WHERE name = '%s'" % ('program', list['name'])

cursor_write.execute(query_sql)

if cursor_write.fetchone():

continue

program_list = {}

program_list['lastupdatedate'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

program_list['volumncount'] = 1

program_list['name'] = list['name'] if list['name'] else ''

program_list['titleserachname'] = list['titleserachname'] if list['titleserachname'] else ''

program_list['titlesortname'] = list['titlesortname'] if list['titlesortname'] else ''

program_list['price'] = list['price'] if list['price'] else ''

program_list['createdate'] = list['createdate'] if list['createdate'] else ''

program_list['status'] = list['status'] if list['status'] else ''

program_list['description'] = list['description'] if list['description'] else ''

program_list['releaseyear'] = list['releaseyear'] if list['releaseyear'] else ''

program_list['language'] = list['language'] if list['language'] else ''

program_list['actorsdisplay'] = list['actorsdisplay'] if list['actorsdisplay'] else ''

program_list['writerdisplay'] = list['writerdisplay'] if list['writerdisplay'] else ''

program_list['licensingwindowstart'] = list['licensingwindowstart'] if list['licensingwindowstart'] else ''

program_list['licensingwindowend'] = list['licensingwindowend'] if list['licensingwindowend'] else ''

program_list['isfree'] = 1 if list['price'] else ''

program_list['rating'] = list['rating'] if list['rating'] else ''

program_list['genres'] = list['genres'] if list['genres'] else ''

program_list['tag'] = list['tag'] if list['tag'] else ''

insert_program_lastid = insert_opt(db_write,cursor_write,'program',program_list,1)

if insert_program_lastid:

logging.info("insert program success! [ name ] : %s" % list['name'])

media_list = {}

media_list['duration'] = list['duration'] if list['duration'] else ''

media_list['contentname'] = list['name'] if list['name'] else ''

media_list['createdate'] = list['createdate'] if list['createdate'] else ''

media_list['extcontentid'] = list['code'] if list['code'] else ''

media_list['status'] = list['status'] if list['status'] else ''

media_list['lastupdatetime'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

insert_media_lastid = insert_opt(db_write,cursor_write,'mediacontent',media_list,1)

if insert_media_lastid:

programmedia_list = {}

programmedia_list['objecttype'] = 3

programmedia_list['objectid'] = insert_program_lastid

programmedia_list['mediacontentid'] = insert_media_lastid

insert_programmedia_lastid = insert_opt(db_write,cursor_write,'programmediacontent',programmedia_list)

if insert_programmedia_lastid:

logging.info("insert programmediacontent success! [ name ] : %s" % list['name'] )

else:

logging.info("insert programmediacontent failed! [ name ] : %s" % list['name'] )

picture_list = {}

picture_list['picturename'] = list['localurl'].replace("127.0.0.1", center_ipaddr + ":" + center_http_port)

picture_list['fileurl'] = list['localurl'].replace("127.0.0.1", center_ipaddr + ":" + center_http_port)

picture_list['createdate'] = list['piccreatedate'] if list['piccreatedate'] else ''

picture_list['picturetype'] = 2

insert_picture_lastid = insert_opt(db_write,cursor_write,'metapicture',picture_list,1)

if insert_picture_lastid:

picturemap_list = {}

picturemap_list['metapictureid'] = insert_picture_lastid

picturemap_list['sequence'] = 1

picturemap_list['objecttype'] = 3

picturemap_list['objectid'] = insert_program_lastid

insert_picture_lastid = insert_opt(db_write,cursor_write,'picturemap',picturemap_list)

logging.info("insert picturemap sucess! [ name ] : %s" % list['name'])

update_sql = "UPDATE %s SET %s = 1 WHERE code = '%s'" % ('c2_program', import_field, list['code'])

cursor_read.execute(update_sql)

db_read.commit()

if cursor_read.rowcount:

logging.info("update c2_program set %s = 1 success!" % import_field)

def main():

global sql_dict

global db_cdb20

global cursor_cdb20

global db_sdb30

global cursor_sdb30

global center_ipaddr

global center_http_port

if not os.path.isfile("/usr/local/sunlight/conf/server.conf"):

print ("error! db config file not found...");

sys.exit(1);

with open("/usr/local/sunlight/conf/server.conf", "r") as s:

for line in s:

if len(line) and ('=' in line):

(sql_key , sql_value) = line.strip().split('=')

sql_dict[sql_key] = sql_value

db_cdb20 = pymysql.connect( \

host=sql_dict['dbhost'], \

port=int(sql_dict['dbport']), \

user=sql_dict['dbuser'], \

password=sql_dict['dbpass'], \

db='cdb20', \

charset='utf8', \

cursorclass = pymysql.cursors.DictCursor \

)

cursor_cdb20 = db_cdb20.cursor()

db_sdb30 = pymysql.connect( \

host=sql_dict['dbhost'], \

port=int(sql_dict['dbport']), \

user=sql_dict['dbuser'], \

password=sql_dict['dbpass'], \

db='sdb30', \

charset='utf8', \

cursorclass = pymysql.cursors.DictCursor \

)

cursor_sdb30 = db_sdb30.cursor()

query_center_ipaddr = "SELECT `paramvalue` FROM systemparameters WHERE `paramname` = 'center_ipaddr'"

cursor_cdb20.execute(query_center_ipaddr)

query_center_ipaddr_result = cursor_cdb20.fetchone()

center_ipaddr = query_center_ipaddr_result['paramvalue'] if query_center_ipaddr_result['paramvalue'] else '127.0.0.1'

query_center_http_port = "SELECT `paramvalue` FROM systemparameters WHERE `paramname` = 'center_http_port'"

cursor_cdb20.execute(query_center_http_port)

query_center_http_port_result = cursor_cdb20.fetchone()

center_http_port = query_center_http_port_result['paramvalue'] if query_center_http_port_result['paramvalue'] else '80'

convert_c2_series(db_cdb20,cursor_cdb20,'import_cdb20',db_cdb20,cursor_cdb20)

logging.info("---------------------------------c2_series import cdb20 finished----------------------------")

convert_c2_program(db_cdb20,cursor_cdb20,'import_cdb20',db_cdb20,cursor_cdb20)

logging.info("---------------------------------c2_program import cdb20 finished----------------------------")

convert_c2_series(db_cdb20,cursor_cdb20,'import_sdb30',db_sdb30,cursor_sdb30)

logging.info("---------------------------------c2_series import sdb30 finished----------------------------")

convert_c2_program(db_cdb20,cursor_cdb20,'import_sdb30',db_sdb30,cursor_sdb30)

logging.info("---------------------------------c2_program import sdb30 finished----------------------------")

cursor_cdb20.close()

db_cdb20.close()

cursor_sdb30.close()

db_sdb30.close()

if __name__ == '__main__':

main()