在工作的过程中经常用定期执行SQL语句来完成任务,于是就是就写一个脚本,能跨库执行SQL语句，自定义变量，并格式化输出.
脚本使用INI配置文件设定脚本参数.配置文件主要分为datebases与Date两块:
datebases配置块示例如下:
[datebases]
oracle11 = oracle,cc/cc@10.16.0.XXX:1521/sh2
解释说明:
Datebases数据块定义了数据库源
各字段的含义如下:
数据库名称=数据库类型(oracle,mysql),用户名/密码@ip:端口/sid(mysql为库名)
Date配置块示例如下:
[Date1]
name = "xxx"
sql = select count(*) from "000001".ent_table where start_time>to_date('[~yesterday]','yyyymmdd') and start_time <to_date('[~today]','yyyymmdd')
~today = today
yesterday = 20120101
return1 = oracle11;000001wh:[0][0];~today,yesterday
return2 = oracle11;000002wh:[0][1];~today,yesterday
解释说明:
Data数据块定义脚本执行的数据以及产生的值.
Name:定义了标示字段
Sql:定义执行的SQL,[]括号中为定义的变量;
在SQL中定义过的变量要在所在的数据块内定义赋值,如:yesterday = 20120101;
变量前标示有~的为特殊变量(实际为脚本中的一个函数,函数的返回值即为today的值),如:~today = today
Ps:today在程序内的定义为:
def today(l):
return time.strftime('%Y%m%d')
return字段定义了返回值,书写格式如下:
return1 = oracle11;000001wh:[0][0];~today,~yesterday
Return1:必须为return+数字开头,可定义多个返回值.
Oracle11:为之前定义的数据库(以便跨库执行).
000000wh:定义在有在程序中掉用的变量名.如:在程序中可以通过:result.get('50200wh'),即返回对应的数据执行的结果.
[0][0]:SQL执行完之后返回值是以数组的形式,在这里就定义了返回数组哪个值,可自行修改.
~today:为SQL中使用的变量(多个中间加,)
脚本CODE
#! /usr/bin/env python
# -*- coding: utf-8 -*-
import os,time,ConfigParser,re,sys
from optparse import OptionParser
import cx_Oracle
import MySQLdb
import csv
localtime = time.localtime()
todate = str(localtime[0]) +'-' + str(localtime[1]) +'-'+ str(localtime[2]) +' '+ str(localtime[3]) +':'+ str(localtime[2])
reports = os.getcwd()+os.sep+'Reports.py'
reports_ini =os.getcwd()+os.sep+'conf.ini'
def today(l):
return time.strftime('%Y%m%d')
def yesterday(l):
return time.strftime('%Y%m%d',time.localtime(time.time() - 24*60*60) )
def qiantian(l):
return time.strftime('%Y%m%d',time.localtime(time.time() - 24*60*60 - 24*60*60) )
def qy(l):
return '00000'+str(return_str.split(';')[1].split(':')[0][0:5])
def compel_rep(l):
qy = str(return_str.split(';')[1].split(':')[0][0:5])+'qy'
return cf.get(g_date,qy)
def special_char(s,sections):
str_list = s.split(' ')
if sections.find('~') >= 0:
for k,l in enumerate(str_list):
n_sections = sections.replace('~', '')
sections = sections.replace('~', '')
if sections.find('(') >= 0:
n_sections = n_sections.split('(')[0]
sec = eval(n_sections + '(l)')
str_list[k] = str(sec)
return str_list
def connect_ora(connstr,sql):
con_type = connstr.split(',')[0]
connstr = connstr.split(',')[1]
if con_type == 'oracle':
db=cx_Oracle.connect(connstr)
cr=db.cursor()
cr.execute(sql)
rs=cr.fetchall()
db.close()
return rs
if con_type == 'mysql':
host = connstr.split('@')[1].split('/')[0].split(':')[0]
user = connstr.split('@')[0].split('/')[0]
passwd = connstr.split('@')[0].split('/')[1]
db = connstr.split('@')[1].split('/')[1]
conn = MySQLdb.connect(host,user,passwd,db)
cursor = conn.cursor()
cursor.execute(sql)
res = cursor.fetchall()
cursor.close()
conn.close()
return res
def replaces(sql,source,target):
tars = ''
if sql.find('}') > 0:
xh_sql = sql[sql.find('{')+1:sql.find('}')]
for t in target:
tar = xh_sql.replace('['+source+']',t)
# tars = tars +' ' + tar
if tars == '':
tars = tar
else:
tars = tars +' ' + tar
tars = sql.replace(xh_sql,tars)
tars = tars.replace('{','')
tars = tars.replace('}','')
else:
for t in target:
if tars == '':
tars = t
else:
tars = tars +' ' + t
tars = sql.replace('['+source+']',tars)
return tars
def get_sql():
ssql = {}
ssql_list = []
global cf
global g_date
cf = ConfigParser.ConfigParser()
if not os.path.exists(reports_ini):
print reports_ini+u' 不存在'
sys.exit(0)
cf.read(reports_ini)
s = cf.sections()
s_value = []
d_value = {}
con = 0
for i,date in enumerate(s):
g_date = date
new_sql = ''
if str(date).find('Date') == 0:
sql = cf.get(date,'sql')
for rr in cf.options(date):
if str(rr).find('return') == 0:
global return_str
return_str = cf.get(date,rr)
new_sql = sql
rep = return_str.split(';')[2].split(',')
for source in rep:
#添加了替换特殊字符串
target = special_char(cf.get(date, source),source)
new_sql = replaces(new_sql,source,target)
print new_sql
ssql[str(date)+str(rr)+'sql'] = new_sql
ssql[str(date)+str(rr)+'con'] = cf.get('datebases',return_str.split(';')[0])
ssql[str(date)+str(rr)+'ret'] = return_str.split(';')[1]
ssql_list.append(str(date)+str(rr))
return ssql,ssql_list
def get_date(date):
return date.get(date)
def get_value(sql,sql_list):
d_value = {}
for s in sql_list:
rs = connect_ora(sql.get(s+'con'),sql.get(s+'sql'))
re = sql.get(s+'ret').split(',')
for r in re:
rule = r.split(':')[1]
rule_v = eval('rs'+rule)
d_value[r.split(':')[0]] = rule_v
return d_value
def main():
print u'请确定以下连接参数 及SQL语句是否正确 : '
sql,sql_list = get_sql()
result = get_value(sql,sql_list)
print result
##以下三行为输出示例代码(输出到CSV文件)，可根据需要自行书写。
result['000001wh'] = result.get('000001wh wh') + result.get('000001whwh')
writer = csv.writer(open("sj.csv","ab"),quoting=csv.QUOTE_ALL)
writer.writerow([todate,result.get(' 000001wh'),'n'])
main()