[Python]分享一些用来简化与SQL Server交互的函数

由于经常需要从网上或者从下载下来的文件导入数据库，在有了pymssql包以后，感觉还是有点麻烦。或者是老忘记指令，或者是执行完以后还要取出数据，或者要用更多的行数来实现一个功能。总而言之，感觉还是不太方便。于是自己写了一个类，能够实现一些基本操作。由于我是即用即写的，所以功能肯定不全。水平所限，估计执行效率也一般。不过好歹大部分操作都能用一行解决了嘛^_^
在下的包叫MSSQL_Interface, 需要pymssql包，还需要numpy包支持。经常使用的函数主要有：

1.init(self,host=’127.0.0.1’,user=’sa’,pwd=’admin’,dbname=’multiangle’)
功能：顾名思义，初始化。需要输入host,user,pwd,dbname4个参数。你也可以在用的时候修改好默认参数，这样构建对象的时候直接用dbi=MSSQL_Interface()就行了 ，适合我这种懒人

2.create_table(self,table_name,col_name_list,col_type_list=[]):
建立新表。需要输入新建表名字，表的列名，各列属性。各列属性可以缺省，默认为varchar(100).也可以用数字表示一些选项，0表示int型，1表示float型，2表示varchar(100)

3.drop_table(self,table_name)
删除表。只要输入表名即可

4.get_col_name(self,table_name)
获取已建立的表的列名。输入待查询的表名，返回一个str的list

5.format_list_asTable(self,list,list_col_info,table_name)
根据现有表对数据进行整形。很多时候，我们获得的数据表的格式需要修改列的排列啊之类的，以适应插入的需要。这个函数可以根据表的格式来对待插入但格式不对的数据进行整形。需要输入待整形二维数组，该二维数组各列所代表的含义，以及作为参照的表名

6.format_list_asColInfoList(self,list,list_col_info,target_col_info)
与上面类似，只不过参照对象可以由自己提供，而不是按照数据表的列来排列的。更自由一些，不过需要自己提供参照列排布

7.import_list_weak(self,data_list,table_name)
弱插入类型。这里的弱插入是我自己的叫法，指当待插入数据中含有数据表中没有的数据时，抛弃这部分数据。对于数据表中有而待插入数据中没有的部分，以空字符串代替。取名叫weak是因为本来还打算写个strong的，不过后来并没有这方面的需求，也就没写了。还有要注意的是这是个批量插入指令，待插入的数据需要为二维数组形式，其中一行代表数据表中一行数据。

8.select_all(self,table_name)
返回指定数据表的所有数据，以及该表的列名

9.add_col(self,table_name,new_col_name,new_col_property)
对指定的表格增加列

10.update_content(self,table_name,target_col_name,target_col_value,pos_col_name,pos_col_value):
用于更新内容。需要输入待修改的数据表名，待修改的列以及想修改的值，还需要输入定位查找条件

11.alter_col_property(self,table_name,target_col,target_property)
修改列的属性。比如说，想把multiangle表中的abc列修改为varchar(100)，可以用
alter_col_property(‘multiangle’,’abc’,’varchar(100)’)来实现

12.code_transform(self,strText,codec=’gb2312’)
用于转码。这个主要是因为将中文数据存入数据库以后，再取出来就变成乱码了，所以需要对取出来的数据进行转码

还有其他的一些函数，不过由于不具有通用性，所以就没贴上来。可能其中有一些是这里函数调用的，如果有人发现，请指出来，我马上补~

__author__ = 'multiangle'# @author:      multiangle# @date:        2015/8/14import pymssqlimport numpy as npclass MSSQL_Interface :    def __init__(self,host='127.0.0.1',user='sa',pwd='admin',dbname='multiangle_investment'):        self.host=host        self.user=user        self.pwd=pwd        self.dbname=dbname        self.connect_db()    def connect_db(self):        try:            self.conn=pymssql.connect(user=self.user,password=self.pwd,host=self.host,database=self.dbname,charset="utf8")            self.cur=self.conn.cursor()        except:            print("ERROR: fail to connect mssql")    def __del__(self):        self.cur.close()        self.conn.close()    #↓新建表    def create_table(self,table_name,col_name_list,col_type_list=[]):        """        #可以有预设值 0 表示 int； 1表示 float; 2表示 varchar(100)        :param table_name:        :param col_name_list:        :param col_type_list:        :return:        """        if col_type_list==[]:   #col_type_list默认为空。如果为空，则默认值为varchar(100)            col_type_list=['varchar(100)']*col_name_list.__len__()        if col_name_list.__len__()!=col_type_list.__len__():            print('ERROR:列名与列属性长度不一致！')            return -1        q0="use "+self.dbname+';'        q1="create table %s ("%(table_name)        q2=""        for i in range(0,col_name_list.__len__()):            q2=q2+col_name_list[i]+' '            if col_type_list[i]==0: #可以有预设值 0 表示 int； 1表示 float; 2表示 varchar(100)                q2=q2+'int,'            elif col_type_list[i]==1:                q2=q2+'float,'            elif col_type_list[i]==2:                q2=q2+'varchar(100),'            else:                q2=q2+col_type_list[i]+','        q2=q2[0:q2.__len__()-1]        query=q0+q1+q2+');'        # print(query)        try:            self.cur.execute(query)            self.conn.commit()            return 1        except:            print("ERROR:create_table: 创建表失败")            return -1    #↓删除表    def drop_table(self,table_name):        query="use multiangle_investment;drop table %s"%(table_name)        try:            self.cur.execute(query)            self.conn.commit()        except:            print('ERROR: drop table')    #↓获得表的列属性    def get_col_name(self,table_name):        q1="use "+self.dbname+';'        q2="select name from syscolumns where id=object_id(N'"+table_name+"')"        query=q1+q2        self.cur.execute(query)        col_pre_name=self.cur.fetchall()        col_num=col_pre_name.__len__()        col_name=[]        for item in col_pre_name:            col_name.append(item[0])        return col_name    #↓按照表的列排列来规整数组 没有对应的值就设为空    def format_list_asTable(self,list,list_col_info,table_name):        table_col_info=self.get_col_name(table_name)        return self.format_list_asColInfoList(list,list_col_info,table_col_info)    #↓按照提供的列排列来规整数组    def format_list_asColInfoList(self,list,list_col_info,target_col_info):        """        :function 按照提供的target_col_info来重新组织输入list        :param list:        :param list_col_info:        :param target_col_info:        :return:        """        file_col_info=list_col_info        file=list        table_col_info=target_col_info        mapping=[-1]*table_col_info.__len__()  #映射表，映射的是table中每列在读入文件中的位置,如果表中列在文件中没有，则mapping中该列的值为-1        for i in range(0,table_col_info.__len__()): #映射表的构建            if table_col_info[i] in file_col_info:                mapping[i]=file_col_info.index(table_col_info[i])        file=np.array(file)              data_list=[]        for i in range(0,table_col_info.__len__()):            if mapping[i]!=-1:                data_list.append(file[:,mapping[i]])            else:                data_list.append(['']*file.__len__())        data_list=np.array(data_list)        data_list=data_list.transpose()        data_list=data_list.tolist()        return [data_list,table_col_info]    #↓将数组录入数据表的普通方法    def import_list_weak(self,data_list,table_name):    #输入已经整理好的list格式        #ATTENTION:这里datalist的值必须为tuple数列，且要跟table_name表中的列严格一一对应        if data_list.__len__()>0:            if isinstance(data_list[0],list):                # data_list=self.__listlist_to_tuplelist__(data_list)                data_list=[tuple(x) for x in data_list]            q0="use "+self.dbname+';'            q1="insert into "+table_name+" values("            # temp_q=""            # for i in range(0,data_list[0].__len__()):            #     temp_q=temp_q+"%s"            #     if i<data_list[0].__len__()-1 :            #         temp_q=temp_q+','            #            temp_q="%s,"*data_list[0].__len__()            temp_q=temp_q[0:temp_q.__len__()-1]            query=q0+q1+temp_q+")"            # print(query)            self.cur.executemany(query,data_list)            self.conn.commit()    def select_all(self,table_name):        #注意：为了通用性，这里取得的数据是没有经过排序的！！！        pre_query="use %s;"%(self.dbname)        main_query="select * from %s"%(table_name)        query=pre_query+main_query        self.cur.execute(query)        res=self.cur.fetchall()        data=[row for row in res]        col_info=self.get_col_name(table_name)        return [data,col_info]    def add_col(self,table_name,new_col_name,new_col_property):        col_info=self.get_col_name(table_name)        if new_col_name in col_info:            print('WARNING:import_data.MSSQL_Interface.add_col:  待插入列已经存在')        else:            query="use %s;alter table %s add %s %s null"%(self.dbname,table_name,new_col_name,new_col_property)            try:                self.cur.execute(query)                self.conn.commit()            except:                print('ERROR:import_data.MSSQL_Interface.add_col')    def update_content(self,table_name,target_col_name,target_col_value,pos_col_name,pos_col_value):        query="use %s;update %s set %s=%s where %s=%s"%(self.dbname,table_name,target_col_name,target_col_value,pos_col_name,pos_col_value)        self.cur.execute(query)        self.conn.commit()    def __listlist_to_tuplelist__(self,data):        # for i in range(0,data.__len__()):        #     data[i]=tuple(data[i])        data=[tuple(x) for x in data]        return data        # newlist=[]        # for line in data:        #     newlist.append(tuple(line))        # return newlist    def __tuplelist_to_listlist__(self,data):        """        :param data:        :return:        function: transform tuplelist[(),(),...,()] to listlist[[],[],[],...,[]]        """        # for i in range(0,data.__len__()):        #     data[i]=list(data(i))        data=[list(x) for x in data]        return data    def alter_col_property(self,table_name,target_col,target_property):        table_col=self.get_col_name(table_name)        if target_col in table_col:            query="use %s; alter table %s alter column %s %s"%(self.dbname,table_name,target_col,target_property)            try:                self.cur.execute(query)                self.conn.commit()            except:                print('ERROR:alter_col_property ',target_col)        return Noneif __name__ == '__main__':    item=MSSQL_Interface()