[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
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点!
本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。