#!/usr/bin/python  
# coding=gbk  

import os
import re
import time
import datetime
import operator
import pyodbc  
import sys


"""  
backup procedure,view,function
"""  

def ado_cmd(src, sql):
    db = pyodbc.connect(src)  
    cursor = db.cursor() 
    cursor.execute(sql)
    db.commit()
    db.close()

def ado_sel(src, sql):
    db = pyodbc.connect(src)  
    cursor = db.cursor()  
    cursor.execute(sql)
    ds = cursor.fetchall()
    db.close() 
    return ds


def getprocedure(src, pname):
    sql = "EXEC Sp_HelpText '" + pname + "';"
    ds = ado_sel(src, sql)
    text = ''
    index = 0
    try:    
        for dr in ds:    
            #print(str(dr[0]))
            text = text + str(dr[0])
            text = text.replace("\r\n", "") + "\n"
            index = index + 1
    except Exception as e:
        print("查询存储过程出错：" + pname + "  [line:"+str(index)+"] ")
        print(e)
    
    return text
    

if __name__ == '__main__':  

    src = 'DRIVER={SQL Server};SERVER=服务器;DATABASE=数据库;UID=用户名;PWD=密码'  
    # p procedure; v view; fn function
    sql = "SELECT [name],[type] FROM sysobjects WHERE type IN('p','v','fn') order by name"
    #src = 'DSN=sampledb;UID=dba;pwd=sql'  

    now = datetime.datetime.now()
    path = now.strftime('%Y-%m-%d')
    if os.path.exists(path):
        for i in range(98,122):
            new_path = path + "_" + chr(i) 

            if not os.path.exists(new_path):  
                path = new_path
                break
            else:
                print(new_path + '已存在')            

    os.makedirs(path)   #创建新文件夹
    sv  = "View"
    sp  = "Prodecure"
    sfn = "Function"
    os.makedirs(path + '/' + sv)
    os.makedirs(path + '/' + sp)
    os.makedirs(path + '/' + sfn)

    ds = ado_sel(src, sql)
    i_count = len(ds)
    print("count=" + str(i_count))
    for dr in ds:    
        p2 = ""
        pname = str(dr[0])
        typ   = str(dr[1])
        typ   = typ.strip()
        if   typ == "V"  : p2 = sv
        elif typ == "P"  : p2 = sp
        elif typ == "FN" : p2 = sfn
        print(typ + ", " + p2 + ", " + pname)
        
        text = getprocedure(src, pname)
        
        filename = pname + ".sql"
        
        file1 = open(path + "/" + p2 + "/" + filename, "w")
        file1.write(text + "\n")
        file1.close