How to connect python to SQL Server with garbled characters

To solve python connection to sqlserver is garbled, you need to specify the character set utf8 (clientcharsetUTF-8) when connecting to sqlserver, the python environment has a character set variable (# codingutf-8) vi/etc/freetds. conf

The code is as follows:

[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

The code is as follows:

# Coding = UTF-8
#! /Usr/bin/python
Import pyodbc
Cnxn = pyodbc. connect ("DSN = DSN55; UID = qq; PWD = 123456 ")
Cursor = cnxn. cursor ()
Cursor.exe cute ('select * from orders where username =? ', 'QQ ')
A = cursor. fetchall ()
Print 'pyodb',

Close connection:

The code is as follows:

Csr. close ()
Del csr
Conn. close ()

Python uses pymssql to connect to the SQL server database

The code is as follows:

# Coding = UTF-8
#! /Usr/bin/env python
#-------------------------------------------------------------------------------
# Name: pymssqlTest. py
# Purpose: Test the pymssql Library, which is downloaded here: http://www.lfd.uci.edu /~ Gohlke/pythonlibs/# pymssql
#
# Author: scott
#
# Created: 04/02/2012
#-------------------------------------------------------------------------------

Import pymssql

Class MSSQL:
"""
Simple encapsulation of pymssql
Pymssql Library, which is downloaded here: http://www.lfd.uci.edu /~ Gohlke/pythonlibs/# pymssql
When using this library, you must enable 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 ):
"""
Obtain the connection information.
Return value: 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 not cur:
Raise (NameError, "database connection failed ")
Else:
Return cur

Def ExecQuery (self, SQL ):
"""
Execute the query statement
The returned result is a list containing tuple. the list elements are record rows, and the tuple elements are record fields in each row.

Call example:
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.exe cute (SQL)
ResList = cur. fetchall ()

# The connection must be closed after the query is complete.
Self. conn. close ()
Return resList

Def ExecNonQuery (self, SQL ):
"""
Execute non-query statements

Call example:
Cur = self. _ GetConnect ()
Cur.exe cute (SQL)
Self. conn. commit ()
Self. conn. close ()
"""
Cur = self. _ GetConnect ()
Cur.exe cute (SQL)
Self. conn. commit ()
Self. conn. close ()

Def main ():
# MS = MSSQL (host = "localhost", user = "sa", pwd = "123456", db = "PythonWeiboStatistics ")
### The returned result is a list containing tuple. the list element is the record row, and the tuple element is the record field of each row.
# 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 ()

Note:
Chinese garbled characters may occur during Chinese operations using pymssql. the solution is as follows:
Add # coding = utf8 to the file header
Encode when the SQL statement contains Chinese characters
InsertSql = "insert into WeiBo ([UserId], [WeiBoContent], [PublishDate]) values (1, 'test', '2017/1 ')". encode ("utf8 ")
Add charset settings during connection
Pymssql. connect (host = self. host, user = self. user, password = self. pwd, database = self. db, charset = "utf8 ")