python-操作mssql数据库

准备工作：

cmd 命令行下安装pymssql：

pip install pymssql

 

查询的数据库如下：

 

代码如下：

#coding=utf-8


import pymssql


class MSSQLOBJ(object):
    
    def __init__(self, user, password, dbname, host='local', port=1433):
        
        self._connect = pymssql.connect(server=host, port=port, user=user, password=password, database=dbname, charset='UTF-8', as_dict=True)
        self._cursor = self._connect.cursor()
     
    def execute_query(self, sql):
        self._cursor.execute(sql)
        
        return self._cursor.fetchall()  
    
    def execute_non_query(self, sql): 
        self._cursor.execute(sql)        
        self._connect.commit()
        
    def close(self):
        self._cursor.close()
        self._connect.close()
        

if __name__ == '__main__':
    sqlobj = MSSQLOBJ('chenfp', '111111', 'test', '127.0.0.1', 1433)
    res = sqlobj.execute_query('select top 4 username,department,age from test.dbo.info') 
    for v in res:
        print(v['username']+v['department']+v['age'])
　　sqlobj.close()

输入如下:
----------------------------------

陈姗姗       技术部       25        
哈成鹏       爱炒股事业部    22        
袁卉        技术部       25        
陈典强       技术部       30        

 

将数据库对象转换成列表对象：

li=[]
for v in res:
    s=Stock(v['stock_id'],v['stock_name'],v['costPrice'],v['targetPrice'],v['currentPrice'],v['shares'])
    li.append(s)