python连接sql server乱码的解决方法

1197次阅读  |  发布于5年以前

vi /etc/freetds/freetds.conf

复制代码 代码如下:

[global]

TDS protocol version

tds version = 8.0
client charset = UTF-8

A typical Microsoft server

[Server55]
host = 192.168.1.55
port = 1433
tds version = 8.0
vi /etc/odbc.ini
[DSN55]
Description=my dsn
Driver=TDS
Database=qq99
Servername=Server55

tsql -S Server55 -U qq -P 123456 -D qq99

复制代码 代码如下:

coding=utf-8

!/usr/bin/python

import pyodbc
cnxn = pyodbc.connect("DSN=DSN55;UID=qq;PWD=123456")
cursor = cnxn.cursor()
cursor.execute('select * from orders where username=?','qq')
a=cursor.fetchall()
print 'pyodbc',a

关闭连接:

复制代码 代码如下:

csr.close()
del csr
conn.close()

python 使用pymssql连接sql server数据库

复制代码 代码如下:

coding=utf-8

!/usr/bin/env python

-------------------------------------------------------------------------------

Name: pymssqlTest.py

Purpose: 测试 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
使用该库时,需要在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()

Copyright© 2013-2020

All Rights Reserved 京ICP备2023019179号-8