#coding =utf-8
#!/usr/bin/env python
#-------------------------------------------------------------------------------
# Name:pymssqlTest.py
# Purpose: Test pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
#
# Author:scott
#
# created:04/02/2012
#-------------------------------------------------------------------------------
ImportPymssql
classMSSQL:
"""
A simple package for pymssql
Pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
When using this library, you need to open the TCP/IP protocol in SQL Server Configuration Manager
Usage:
"""
def__init__( Self, host,user,pwd,db):
Self. Host = Host
Self. user = User
Self. pwd = pwd
Self. db = db
def__getconnect ( Self):
"""
Get connection Information
return: Conn.cursor ()
"""
if not Self. DB:
Raise(Nameerror, "No Database Information set")
Self. conn = Pymssql.connect (host= Self. host,user= Self. user,password= Self. pwd,database= Self. db,charset= "UTF8")
Cur = Self. Conn.cursor ()
if notCur
Raise(Nameerror, "Connection database Failed")
Else:
returnCur
defExecQuery ( Self, SQL):
"""
Execute Query statement
The element that returns a list,list containing a tuple is a record row, and the elements of a tuple are the fields of each row of records
Invocation Example:
ms = MSSQL (host= "localhost", user= "sa", pwd= "123456", db= "Pythonweibostatistics")
Reslist = Ms. ExecQuery ("SelectID, nickname from Weibouser ")
for (Id,nickname) in reslist:
Print str (ID), nickname
"""
Cur = Self. __getconnect ()
Cur.execute (SQL)
Reslist = Cur.fetchall ()
#查询完毕后必须关闭连接
Self. Conn.close ()
returnReslist
defExecnonquery ( Self, SQL):
"""
Executing non-query statements
Invocation Example:
cur = self.__getconnect ()
Cur.execute (SQL)
Self.conn.commit ()
Self.conn.close ()
"""
Cur = Self. __getconnect ()
Cur.execute (SQL)
Self. Conn.commit ()
Self. Conn.close ()
defMain ():
# # ms = MSSQL (host= "localhost", user= "sa", pwd= "123456", db= "Pythonweibostatistics")
# # #返回的是一个包含tuple的list, the element of list is the record row, the element of tuple is the field of each row record
# # 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)inchReslist:
PrintStr(weibocontent). Decode ("UTF8")
if__name__= = ' __main__ ':
Main ()
Python access to SQL Server