首页 > 代码库 > python利用pymssql链接sqlserver数据库

python利用pymssql链接sqlserver数据库

1.引入pymssql包

import pymssql

2.建立连接:

conn = pymssql.connect(server=conf[‘db_address‘],
port="1433",user=conf[‘db_username‘],
password=conf[‘db_password‘],
database=conf[‘db_name‘],

charset="UTF-8")
cursor = conn.cursor()

3.执行SQL语句:

cursor.execute(sql)

4.获得数据:
search_table = cursor.fetchall()

5.修改数据:

cursor.execute(sql,(str(datetime.today()),))

conn.commit()

定义一个python的类

#tool class to write data into fileclass OutFile:    dir_ = ‘‘    sew_ = ‘==‘    def __init__(self, dir, sew = ‘==‘):        self.dir_ = dir        self.sew_ = sew    def write_line(self, line, date):        fp = open(self.dir_ + "/" + date, ‘ab‘)        print line        fp.write(line)                fp.write(\n)        fp.close()    def write_vector(self, vec, date):        fp = open(self.dir_ + "/" + date, ‘ab‘)        if len(vec) > 0:            for i in range(0, len(vec) - 1):                fp.write(vec[i])                fp.write(self.sew_)            fp.write(vec[len(vec) - 1])            fp.write(\n)        fp.close()    def write_dict(self, dict, date):        fp = open(self.dir_ + "/" + date, ‘ab‘)        for key in dict:            fp.write(key)            fp.write(‘ : ‘)            leng = len(dict[key])            for i in range(0, leng - 1):                fp.write(dict[key][i])                fp.write(self.sew_)            fp.write(dict[key][leng - 1])            fp.write(\n)        fp.close()    def write_dict_dict(self, dict_dict, date):        fp = open(self.dir_ + "/" + date, ‘ab‘)        for key in dict_dict:            fp.write(key.encode(‘latin-1‘))            print key.encode(‘latin-1‘)            #fp.write(key.decode(‘utf-8‘).encode(‘gbk‘))            dict = dict_dict[key]            fp.write(self.sew_)            for key_word in dict:                #fp.write(key_word.decode(‘utf-8‘).encode(‘gb2312‘))                fp.write(key_word.encode(‘latin-1‘))                #fp.write(key_word.decode(‘utf-8‘).encode(‘gbk‘))                fp.write(‘:‘)                fp.write(str(dict[key_word]))                fp.write(self.sew_)            fp.write(\n)        fp.close()        outfile = OutFile(‘report‘, ‘	‘)