mysql大表数据抽取_抽取mysql分表数据

yestoday=`date -d "1 day ago" +"%Y-%m-%d"`

year=`date -d "now" +"%Y"`

echo '' > /apps/data/cash_bill/cash_bill_tmp.csv;

##找出配置表中表名后缀名字

relate_list=`mysql --skip-column-names -h192.168.1.136 -uws_shop_pos -pws_shop_pos_2015 ws_shop_pos -e "use ws_shop_pos;SET NAMES 'UTF8';select tbl_suffix from shop_relate_config;"|xargs echo`

#find_list=""

## 找到所有和配置表关联的表

for tbl_suffix in ${relate_list[*]}

do

find_list="$find_list bill_flow_${year}${tbl_suffix}"

done

##0-9,a-y,配置表

bill_flow_list=(bill_flow_${year}{0..9} bill_flow_${year}{a..z})

bill_flow_list=($find_list ${bill_flow_list[*]})

echo ${bill_flow_list[*]}

#把所有cash_goods数据拼接到cash_goods_tmp

for bill_flow in ${bill_flow_list[*]}

do

mysql --skip-column-names -h192.168.1.136 -uws_shop_pos -pws_shop_pos_2015 ws_shop_pos -e "use ws_shop_pos;SET NAMES 'UTF8';set @1:='"$yestoday"';select \

flow_no flow_no, \

total_amount total_amount, \

status status, \

source source, \

cash_time cash_time, \

remark remark, \

device_no device_no, \

sync_time sync_time, \

coupon_count coupon_count, \

cash_tradeno cash_tradeno, \

coupon_amount coupon_amount, \

integral_amount integral_amount, \

ticket ticket, \

shop_id shop_id, \

store_id store_id, \

create_time create_time, \

pay_amount pay_amount, \

pay_type pay_type, \

pay_time pay_time, \

onduty onduty, \

out_tradeno out_tradeno, \

pay_user pay_user, \

open_id open_id, \

wx_coupon_count wx_coupon_count

FROM \

${bill_flow} where DATE_FORMAT(create_time,'%Y-%m-%d') >= @1 AND DATE_FORMAT(create_time,'%Y-%m-%d') < date_add(@1, INTERVAL 1 DAY) ;" > /apps/data/cash_bill/cash_bill_tmp.csv;

python /apps/export/trans.py /apps/data/cash_bill/cash_bill_tmp.csv >> /apps/data/cash_bill/cash_bill_$yestoday.csv

done

trans.py 脚本

# -*- coding: utf-8 -*-

import sys

f = open(sys.argv[1], "r")

for line in f.readlines():

line_list = line.split('\t')

print ",".join(line_list),