# -*- coding=utf-8 -*- 

import pyodbc

from datetime import datetime

import pymssql

import os

import decimal

class SQLServer:

    def __init__(self,server,user,password,database,autocommit):

        self.server = server

        self.user = user

        self.password = password

        self.database = database

        self.autocommit = autocommit

    def __GetConnect(self):

        if not self.database:

            raise(NameError,"没有数据库信息")

        self.conn = pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database,autocommit=self.autocommit)

        cur = self.conn.cursor()

        if not cur:

            raise(NameError,"连接数据库失败")

        else:

            return cur

    def backDb(self,database,backpath):

        cur = self.__GetConnect()

        backPath = backpath + database + datetime.now().strftime("%Y%m%d") + '.bak'

        sql = "BACKUP DATABASE [{0}] TO DISK = N'{1}'".format(database,backPath)

        cur.execute(sql)

        self.conn.close()

    def ExecQuery(self,sql):

        cur = self.__GetConnect()

        cur.execute(sql)

        self.conn.commit()

        self.conn.close()

def main():

    msg = SQLServer(server="192.168.2.190",user="sa",password="P123",database="master",autocommit=True)

    msg.backDb("wanghz","D:\\backup\\")

    msg.ExecQuery("exec wanghz")

if __name__ == "__main__":

    main(

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[wanghz]    Script Date: 11/07/2019 15:20:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[wanghz]

AS

declare @dbname sysname

set @dbname='wanghz'

declare @s varchar(50)

declare tb cursor local for

select s='kill '+cast(spid as varchar)

from master..sysprocesses

where dbid=db_id(@dbname) 

open tb

fetch next from tb into @s 

begin

exec(@s)

fetch next from tb into @s

end

close tb

deallocate tb

exec('drop database ['+@dbname+']')