备份sqlserver中的视图、函数、存储过程

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

#!/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("rn","") + "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        

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!