[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/14

import pymssql
import numpy as np

class 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 None




if __name__ == '__main__':
    item=MSSQL_Interface()