How to connect to an SQLServer2000 instance using Python

Source: Internet
Author: User
This article mainly introduces how to connect Python to SQLServer2000, and analyzes the common problems and precautions encountered during the implementation of database connection in Python in the form of instances, for more information about how to connect Python to SQLServer2000, see this article. This article analyzes the common problems and precautions encountered during the implementation of database connection using Python in the form of instances, for more information, see

This article describes how to connect Python to SQLServer2000. We will share this with you for your reference. The details are as follows:

Pymssql.sourceforge.net/introduces a good address for PYTHON to connect to MSSQL!

A good way to use Python is to find many ready-made modules on the Internet and then download and use them directly. This is one reason for rapid development. Now we are mainly studying the operation functions of a pymssql module!

After installation, you can directly query the help documentation to view some functional help documents of this module.

1. oneSolve the garbled problemMethod:

s.decode('gbk', 'ignore')

For example, to convert a String object s from a gbk internal code to a UTF-8, you can do the following:

s.decode('gbk').encode('utf-8')

However, in actual development, I found that exceptions often occur in this method:

UnicodeDecodeError: 'gbk' codec can't decode bytes in position 30664-30665: illegal multibyte sequence

This is because of illegal characters-especially in some programs written in C/C ++, full-angle spaces often have different implementation methods, such as/xa3/xa0, OR/xa4/x57, these characters are all full-width spaces, but they are not "valid" full-width spaces (the real full-width spaces are/xa1/xa1 ), therefore, an exception occurs during transcoding.

Fortunately, tiny found a perfect solution (I was criticized for not reading the document carefully, sweated ......)

s.decode('gbk', 'ignore').encode('utf-8')

Because the prototype of the decode function is decode ([encoding], [errors = 'strict ']), you can use the second parameter to control the error handling policy. The default parameter is strict, an exception is thrown when an invalid character is encountered;

If it is set to ignore, invalid characters are ignored;
If it is set to replace,? Replace invalid characters;
If it is set to xmlcharrefreplace, the XML character reference is used.

This method is indeed quite helpful. We can solve the problem of storing full-width and half-width illegal characters in the database!

Character encoding is always a headache!

2.www.python.org/dev/peps/pep-0249/

The above provides common Python-DBAPI operations.

Summarize the related API operation methods:

3. the general procedure of connecting Python to the database is as follows:

Step 1: Import related modules

MYSQL:

import MySQLdb

MSSQL:

import pymssql

Step 2: Open the connection

MYSQL:

conn = MySQLdb.connect(self.dbhost,self.dbuser,self.dbpasswd,self.database)

MSSQL:

conn = pymssql.connect(host=self.dbhost,user=self.dbuser,password=self.dbpasswd,database=self.database)

[Define this method through its function prototype]

Step 3: Create a cursor after the connection is complete. This object is used to send a request to the database.

Code:

curs = conn.cursor()

It is equivalent to the same Statement object in JAVA. SQL command

Step 4: start sending SQL commands to the database server. you can do this.

curs.execute(SQL)

For example:

curs.execute("select * from test")

The SQL command can be any SQL statement, which can be an INSERT or DELETE operation or a SELECT operation.

Note that commit () must be submitted once after the execution is complete.

For example:

cursor.execute("insert into test(id) values(1)")

If the SELECT operation is executed, the fifth step is required:

Step 5:

curs.execute("select * from test")rows = curs.fetchall()

Fetchall () is just a method of our cursor object.

Now we can extract the relevant information.

for i in range(len(rows)): print "Row",i,"name",rows[i][0],"value",rows[i][1]

Check the code I wrote:

Def test (self): conn = self. connect () SQL = "select * from bbs where id <20" curs = conn. cursor () # obtain a cursor object curs.exe cute (SQL) # execute an SQL statement rows = curs. fetchall () # obtain all query result sets for I in range (len (rows): # print "Row", I, "name ", rows [I] [3], "value", rows [I] [4] conn. close ()

I understand that the row record obtained through this method is actually a data structure of a tuples.

You can use a method similar to a two-dimensional array to query one of them.

Rows [1] [0]: indicates the value at the first position in the first record.

Then let's take a look at the encoding and decoding after extracting it from the database.

for i in range(len(rows)):      print rows[i][4].decode('gb2312', 'ignore').encode('gb2312')

The above is a detailed description of how to connect to the SQLServer2000 instance using Python. For more information, see other related articles in the first PHP community!

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.