python sqlserver 2

#_*_encoding:utf-8_*_
import pymssql
import os

class MSSQLHelper:
    def __init__(self,host,user,pwd,db):
        self.host=host
        self.user=user
        self.pwd=pwd
        self.db=db

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            print "没有设置数据库信息"
        self.conn=pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db)
        cur=self.conn.cursor()
        if not cur:
            print "数据库连接失败"
        else:
            return cur

    def ExecQuery(self,sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
        """
        try:
            cur = self.__GetConnect()
            cur.execute(sql)
            resList =cur.fetchall()
            return resList
        except Exception,e:
            print e
        finally:
            #查询完毕后必须关闭连接
            self.conn.close()


    def ExecNonQuery(self,sql):
        """
        执行非查询语句
        """
        try:
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
        except Exception,e:
            print e
        finally:
            self.conn.close()


    def export(self,sql):
        """导出特定数据"""
        #得到当前脚本的执行目录
        top=os.getcwd()
        #查看是否已经存在备份目录，如果有则删除，没有则新建目录
        backUpDirectory="%s\\%s" % (top,self.db+"Backup")
        if not os.path.exists(backUpDirectory):
            os.mkdir(backUpDirectory)

        currentTablePath ="%s\\%s.txt" % (backUpDirectory,sql.split('from')[1].strip())
        f=open(currentTablePath,"w")  # open file for writing
        for m in self.ExecQuery(sql):
            for n in m:
                f.write(str(n)+"\t")   # write 输出到文本
            f.write("\r\n")   # write 输出到文本
        f.close()

    def exportDB(self):
        """导出数据库"""
        #得到当前脚本的执行目录
        top=os.getcwd()
        #查看是否已经存在备份目录，如果有则删除，没有则新建目录
        backUpDirectory="%s\\%s" % (top,self.db+"Backup")
        if not os.path.exists(backUpDirectory):
            os.mkdir(backUpDirectory)

        for (m,) in self.ExecQuery("select name from sysobjects where xtype='U'"):
            currentTablePath ="%s\\%s.txt" % (backUpDirectory,m)
            r = os.popen('BCP %s..%s out %s -c -U"%s" -P"%s"' % (self.db,m,currentTablePath,self.user,self.pwd))
            print r.read()
            r.close()

#_*_encoding:utf-8_*_
import bag.sqlserver_cs as sql
reload(bag.sqlserver_cs)

a=sql.MSSQLHelper("10.19.1.50","sa","123","GuBei_Data")
a.ExecNonQuery("INSERT INTO tblComment([ID],[Number],[visitor],[comments]) VALUES(5,'view3','youke4','very done')")
a.ExecNonQuery("delete from tblComment where id=5 or id=6 or id=7")
print a.ExecQuery("select * from tblComment")

a.export("select * from tblComment")
a.exportDB()