Workarounds for Python connection to SQL Server garbled

Source: Internet
Author: User
Tags dsn
Vi/etc/freetds/freetds.conf
Copy CodeThe 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
Copy CodeThe 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.execute (' select * from Orders where username=? ', ' QQ ')
A=cursor.fetchall ()
print ' Pyodbc ', a


To close the connection:
Copy CodeThe code is as follows:


Csr.close ()
Del CSR
Conn.close ()

Python uses pymssql to connect to a SQL Server database
Copy the Code code as follows:


#coding =utf-8
#!/usr/bin/env python
#-------------------------------------------------------------------------------
# Name:pymssqlTest.py
# Purpose: Test pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
#
# Author:scott
#
# created:04/02/2012
#-------------------------------------------------------------------------------

Import pymssql


Class MSSQL:
"""
A simple package for pymssql
Pymssql Library, the library to download here: http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
When using this library, you need to open 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):
"""
Get connection Information
return: Conn.cursor ()
"""
If not self.db:
Raise (Nameerror, "Database information not 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, "Connection database Failed")
Else
Return cur

def execquery (Self,sql):
"""
Execute Query statement
The element that returns a list,list containing a tuple is a record row, and the elements of a tuple are the fields of each row of records

Invocation 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.execute (SQL)
Reslist = Cur.fetchall ()

#查询完毕后必须关闭连接
Self.conn.close ()
Return reslist

def execnonquery (Self,sql):
"""
Executing non-query statements

Invocation Example:
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, the element of list is the record row, the element of tuple is the field of each row record
# # 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 ()

Precautions:
When using pymssql for Chinese operation, Chinese characters may appear garbled, the solution I solve is:
File header plus #coding =utf8
Encode when there are Chinese in SQL statements
Insertsql = "INSERT into WeiBo ([userid],[weibocontent],[publishdate]) VALUES (1, ' Test ', ' 2012/2/1 ')". Encode ("UTF8")
Join charset setup Information when connecting
Pymssql.connect (host=self.host,user=self.user,password=self.pwd,database=self.db,charset= "UTF8")

  • Related Article

    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.