Detailed example of teaching you how to connect to SQLServer2000 using Python

This article mainly introduces the method of Python connecting to SQLServer2000, and analyzes the common problems and related precautions encountered in Python's implementation of database connection in the form of examples. Friends in need can refer to this article

The example describes how Python connects to SQLServer2000. Share it with everyone for your reference, the details are as follows:

pymssql.sourceforge.net/ Introducing a good address for PYTHON to connect to MSSQL!

A good way for Python is to find many ready-made modules on the Internet and then download them directly and use them. One of the reasons for rapid development is this. Now we are mainly going to study the operating functions of the pymssql module!

You can directly query the help document after installation to view some function help documents of this module.

1. A method to solve the problem of garbled characters:

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

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

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

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

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

This is because an illegal character is encountered - especially in some programs written in C/C++, full-width spaces are often implemented in many different ways, such as / xa3/xa0, or /xa4/x57, these characters appear to be full-width spaces, but they are not "legal" full-width spaces (the real full-width spaces are /xa1/xa1), so they appear during the transcoding process Exception.

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

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

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

If set to ignore, illegal characters will be ignored;
If set to replace, illegal characters will be replaced with ?;
If set to xmlcharrefreplace, XML character references will be used.

This method is really helpful. For the problem of storing full-width and half-width illegal characters in the database, you can solve it like this!

The character encoding problem is always a headache, haha!

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

The above provides common operating functions of Python-DBAPI.

Summarize the relevant API operation methods:

3. Summarize the general program process of connecting Python to the database as follows:

Step 1: Import related Module

where MYSQL:

import MySQLdb

where MSSQL:

import pymssql

Step 2: Open the connection

where MYSQL:

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

where MSSQL:

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

[This method is defined through its own function prototype]

Step 3: After the connection is completed, start creating a cursor. Use this object to send a request operation to the database.

Code:

curs = conn.cursor()

Equivalent to the Statement object in JAVA. Submit SQL commands through statements

The fourth step: Start sending SQL commands to the database server. You can do it like this

curs.execute(SQL)

For example:

curs.execute("select * from test")

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

Please note that commit() is required after execution.

For example:

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

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

The fifth step:

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

Among them fetchall() is just a method of our cursor object.

Now you can extract the relevant information

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

Look at the code I wrote:

def test(self):
    conn = self.connect()
    sql="select * from bbs where id<20"
    curs = conn.cursor()  #得到一个游标对象
    curs.execute(sql)    #执行一个SQL语句
    rows=curs.fetchall()  #得到全部的查询结果集
    for i in range(len(rows)): #
      print "Row",i,"name",rows[i][3],"value",rows[i][4]
    conn.close()

I understand: through this method, the one-line record obtained is actually a Tuple data structure.

To get one of them, you can query it using a method similar to a two-dimensional array.

rows[1][0]: Indicates the value of the first position in the first record.

Then let’s take a look at extracting it from the database and then encoding and decoding it

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

The above is the detailed content of Detailed example of teaching you how to connect to SQLServer2000 using Python. For more information, please follow other related articles on the PHP Chinese website!