Python PyMsSQL库

安装(环境CentOS6.5x64 & Mac):

1
2
3
4
5
6
7
8
# CentOS
yum -y install freetds-devel

# Mac
brew install freetds

#PiP
pip install pymssql

使用MSSQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
import pymssql

class DB:
def __init__(self, config):
self.DB_HOST = config['hostname']
self.DB_PORT = config['port']
self.DB_USER = config['username']
self.DB_PWD = config['password']
self.DB_NAME = config['database']
self.conn = self.get_connection()

def get_connection(self):
try:
return pymssql.connect(
server=self.DB_HOST, port=self.DB_PORT,
user=self.DB_USER, password=self.DB_PWD,
database=self.DB_NAME, charset='utf8'
)
except Exception, e:
print("Fatal: connect db fail:%s" % e)
return None

def query(self, cmd, dict_cursor=True):
if dict_cursor:
cursor = self.conn.cursor(as_dict=True)
else:
cursor = self.conn.cursor()
cursor.execute(cmd)
result = cursor.fetchall()
cursor.close()
return [item for item in result]

def update(self, cmd, row_count=False):
cursor = self.conn.cursor()
cursor.execute(cmd)
self.conn.commit()
cursor.close()
return cursor.rowcount if row_count else None

def close(self):
self.conn.close()