python查询sqlserver视图_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

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com

特别注意：本站所有转载文章言论不代表本站观点！

本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。