Python备份sqlserver中的视图、函数、存储过程详解编程语言

[Python]代码    

#!/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         

QQ截图20150910150454.png    

QQ截图20150910150517.png