Run SQL queries from remote database using Python.

run_sql.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

"""
This module is used to run SQL code from remote DB host.
"""

__version__ = '0.2'
__date__ = '2016-04-13'
__author__ = 'Guangning Yu'

import sys
import cx_Oracle
import csv
import codecs
import os
from optparse import OptionParser

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

PARA_DICT={}

class Connection():
    def __init__(self, db):
        self.db = db
        self.cursor = db.cursor()
        
    def query(self, query):
        return self.cursor.execute(query)
        
    def fetchall(self):
        return self.cursor.fetchall()
        
    def close(self):
        self.cursor.close()
        self.db.close()
        
def run_sql(user_name, password, host, sql, output_file):
    db = cx_Oracle.connect(user_name, password, host)
    conn = Connection(db)
    cursor = conn.cursor
    result = conn.query(sql)
    with open(output_file, 'w') as f:
        f.write(codecs.BOM_UTF8)
        output = csv.writer(f, delimiter=',', dialect='excel')
        header = []
        for col in cursor.description:
            header.append(col[0])
        output.writerow(header)
        for line in result:
            output.writerow(line)
    conn.close()
    
def pass_parameter(option, opt, value, parser):
    try:
        key = value.strip().split('=')[0]
        vlu = value.strip().split('=')[1]
        PARA_DICT[str(key)] = str(vlu)
    except Exception as e:
        raise Exception('The parameter "%s" cannot be parsed.' % value)
    return
  
def set_parameter(sql, para_dict):
    for (key, vlu) in para_dict.items():
        sql = sql.replace('&'+key, vlu)
    return sql
    
    
    
if __name__ == '__main__':
    parser = OptionParser()
    parser.add_option("-u", dest="user_name", default=None, help="e.g.'yugnw'")
    parser.add_option("-p", dest="password",  default=None, help="e.g.'88888888'")
    parser.add_option("-t", dest="host",      default=None, help="e.g.'192.168.xx.xx:1521/xxx'")
    parser.add_option("-s", dest="script",    default=None, help="e.g.'/home/yugnw/query.sql'")
    parser.add_option("-o", dest="output",    default=None, help="e.g.'/home/yuwnw/result.csv'")
    parser.add_option("--var", action="callback", type="string", callback=pass_parameter, help="e.g.start_dt=\"2016-04-01\"; then write the parameter like \"create_dt >= '&start_dt' in the SQL code\"")
    (options, args) = parser.parse_args()
    
    if options.user_name:
        user_name = options.user_name
    else:
        raise Exception("请输入用户名")
        
    if options.password:
        password = options.password
    else:
        raise Exception("请输入密码")
        
    if options.host:
        host = options.host
    else:
        raise Exception("请输入HOST名称")
        
    if options.script and os.path.isfile(options.script):
        script = options.script
    else:
        raise Exception("请输入正确的脚本路径")
        
    if options.output:
        output = options.output
    else:
        raise Exception("请输入输出文件的路径")
        
    with open(script, 'rb') as f:
        sql = f.read()
        
    sql = set_parameter(sql, PARA_DICT)
    
    run_sql(user_name, password, host, sql, output)