python mysql 多张表_python将不同数据库多张表内容整合到一张表中

#encoding=utf8

#!/usr/bin/env python

import os

import sys

import urllib2

import MySQLdb

import simplejson

import subprocess

import sys

sys.path.append("/home/hadoop/hivelib")

from common import *

from util import Utils

from datetime import datetime,timedelta

from dbutil import DBUtils

import csv

class RptTracksCps(object):

def __init__(self,today):

self.today = today

self.dayStr = today.strftime("%Y-%m-%d")

self.paramDict = {"dayStr":self.dayStr}

self.testFlag =False

def run(self):

self.deleteData4DB()

self.DataDeal_source()

self.DataDeal_base()

self.selectSum4DB()

def DataDeal_source(self):

tmpFileName = "rpttrackscps_source_%s.log" % self.dayStr

conn = DBUtils.getConnection(dbName="ktepdb")

selectSql = """select detail_external,sum(passenger_number),sum(order_pay),date,external from source_conversion where date ='%(dayStr)s' and external='marketing' group by detail_external,date""" % self.paramDict

rowList = DBUtils.selectSql(selectSql,conn)

self.insertData2DB(rowList)

def selectSum4DB(self):

conn = DBUtils.getConnection(dbName="uniondb")

selectSql = """select detail_external,sum(passenger_number),sum(order_pay),date,external,sum(pay_order) from rpttracks_cps where date ='%(dayStr)s' and external='marketing' group by detail_external,date""" % self.paramDict

rowLists = DBUtils.selectSql(selectSql,conn)

#self.insertData2DB(rowLists)

insertSql ='insert into rpttracks_cps(detail_external,passenger_number,order_pay,date,external,pay_order) VALUES (%s,%s,%s,%s,%s,%s)'

DBUtils.insertList(insertSql,rowLists,conn)

sum=0

for item in rowLists:

sum+=1

continue

#print sum

selectSql = """select detail_external,count(*) from rpttracks_cps where date ='%(dayStr)s'""" % self.paramDict

rowList = DBUtils.selectSql(selectSql,conn)

for line in rowList:

deleteSql = "delete from `rpttracks_cps` where date='%s' and external='marketing' ORDER BY id LIMIT %s" % (self.paramDict["dayStr"],line[1]-sum)

#print line[1]

DBUtils.deleteSql(deleteSql,conn)

break

conn.close()

def DataDeal_base(self):

url = "http://192.168.0.76:60903/configadmin/index.php?action=api4inner&method=api&apiname=getSiteRelationForCustomer"

site_no={}

sites=""

urls = urllib2.urlopen(url)

data = urls.read()

datas = simplejson.loads(data)

conn = DBUtils.getConnection(dbName="ktepdb")

conns = DBUtils.getConnection(dbName="uniondb")

#print datas

for line in datas.keys():

try:

if line == "xxx":

continue

tmpFileName = "rpttrackscps_base_%s.log" % self.dayStr

sql = '''

select detail_external,sum(pay_order),order_sn,date,external from base_%s where date ='%s' and external='marketing' group by detail_external

''' % (line,self.paramDict["dayStr"])

rowList = DBUtils.selectSql(sql,conn)

insertSql ='insert into rpttracks_cps(detail_external,pay_order,order_sn,date,external) VALUES (%s,%s,%s,%s,%s)'

DBUtils.insertList(insertSql,rowList,conns)

except:

pass

conn.close()

conns.close()

def deleteData4DB(self):

conn = DBUtils.getConnection(dbName="uniondb")

deleteSql = "delete from `rpttracks_cps` where date='%(dayStr)s' and external='marketing'" % self.paramDict

DBUtils.deleteSql(deleteSql,conn)

conn.close()

def insertData2DB(self,rowList):

insertSql ='insert into rpttracks_cps(detail_external,passenger_number,order_pay,date,external) VALUES (%s,%s,%s,%s,%s)'

conn = DBUtils.getConnection(dbName="uniondb")

DBUtils.insertList(insertSql,rowList,conn)

conn.close()

if __name__=='__main__':

today = datetime.now() - timedelta(days=1)

ompdata2db = RptTracksCps(today)

ompdata2db.run()

#homedir = os.getcwd()

#print homedir