备份sqlserver中的视图、函数、存储过程，sqlserver存储过程,#!/usr/bin/p

备份sqlserver中的视图、函数、存储过程，sqlserver存储过程,#!/usr/bin/p

#!/usr/bin/python  # coding=gbk  import osimport reimport timeimport datetimeimport operatorimport 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 dsdef 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 textif __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