python 使用pymssql连接sql server数据库

心已赠人 2022-08-04 11:37 133阅读 0赞

\#coding=utf-8  
\#!/usr/bin/env python  
\#-------------------------------------------------------------------------------  
\# Name: pymssqlTest.py  
\# Purpose: 测试 pymssql库，该库到这里下载：[http://www.lfd.uci.edu/~gohlke/pythonlibs/\#pymssql][http_www.lfd.uci.edu_gohlke_pythonlibs_pymssql]  
\#  
\# Author: scott  
\#  
\# Created: 04/02/2012  
\#-------------------------------------------------------------------------------  
  
import pymssql  
  
  
class MSSQL :  
"" "  
对pymssql的简单封装  
pymssql库，该库到这里下载：[http://www.lfd.uci.edu/~gohlke/pythonlibs/\#pymssql][http_www.lfd.uci.edu_gohlke_pythonlibs_pymssql]  
使用该库时，需要在**Sql Server** Configuration Manager里面将TCP/IP协议开启  
  
用法：  
  
" ""  
  
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 :  
raise( NameError, "没有设置数据库信息")  
self.conn = pymssql.connect(host = self.host,user = self.user,password = self.pwd,database = self.db,charset = "utf8")  
cur = self.conn.cursor()  
if not cur :  
raise( NameError, "连接数据库失败")  
else :  
return cur  
  
def ExecQuery( self,sql) :  
"" "  
执行查询语句  
返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段  
  
调用示例：  
ms = MSSQL(host="localhost ",user="sa ",pwd=" 123456 ",db="PythonWeiboStatistics ")  
resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser ")  
for (id,NickName) in resList:  
print str(id),NickName  
" ""  
cur = self.\_\_GetConnect()  
cur.execute(sql)  
resList = cur.fetchall()  
  
\#查询完毕后必须关闭连接  
self.conn.close()  
return resList  
  
def ExecNonQuery( self,sql) :  
"" "  
执行非查询语句  
  
调用示例：  
cur = self.\_\_GetConnect()  
cur.execute(sql)  
self.conn.commit()  
self.conn.close()  
" ""  
cur = self.\_\_GetConnect()  
cur.execute(sql)  
self.conn.commit()  
self.conn.close()  
  
def main() :  
\#\# ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")  
\#\# \#返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段  
\#\# ms.ExecNonQuery("insert into WeiBoUser values('2','3')")  
  
ms = MSSQL(host = "localhost",user = "sa",pwd = "123456",db = "PythonWeiboStatistics")  
resList = ms.ExecQuery( "SELECT id,weibocontent FROM WeiBo")  
for ( id,weibocontent) in resList :  
print str(weibocontent).decode( "utf8")  
  
if \_\_name\_\_ == '\_\_main\_\_' :  
main()

注意事项：

使用pymssql进行中文操作时候可能会出现中文乱码，我解决的方案是：

*  文件头加上 \#coding=utf8
 *  sql语句中有中文的时候进行encode
    
    insertSql = "insert into WeiBo(\[UserId\],\[WeiBoContent\],\[PublishDate\]) values(1,'测试','2012/2/1')".encode( "utf8")
 *  连接的时候加入charset设置信息
    
    pymssql.connect(host = self.host,user = self.user,password = self.pwd,database = self.db,charset = "utf8")

转载：[python 使用pymssql连接sql server数据库][python _pymssql_sql server]

[http_www.lfd.uci.edu_gohlke_pythonlibs_pymssql]: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
[python _pymssql_sql server]: http://www.cnblogs.com/qianlifeng/archive/2012/02/06/2340367.html