- #!/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__': src1= '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
- #该片段来自于http://www.codesnippet.cn/detail/2907201513264.html
来源: http://www.codesnippet.cn/detail/2907201513264.html