How to connect python to SQL Server with garbled characters

Source: Internet
Author: User
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 ")

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.