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!