Method of connecting Python to SQLServer2000

  • 2020-05-30 20:28:45
  • OfStack

The example in this article shows how to connect Python to SQLServer2000. I will share it with you for your reference as follows:

http: / / pymssql. sourceforge. net/introduce PYTHON connection MSSQL good address!

One of the best ways to use Python is to be able to find a lot of modules on the web and just download them and use them. One of the reasons for rapid development is this. Now it is time to study the operation function of one pymssql module.

You can directly query the help documentation after installation to view the module's 1 function help documentation.

1.1 ways to solve the garbled code problem:

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

For example, to convert an String object s from an gbk internal code to UTF-8, do the following


In practice, however, I've found that this approach often goes awry:

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

Met illegal characters - especially in this is because some written in C/C + + program, the Angle of space often have a variety of different ways of implementation, such as/xa3 / xa0, or/xa4 / x57, these characters, seem to be the Angle of space, but they are not "legal" all the Angle of space (the real Angle of whole space is/xa1 / xa1), therefore in the process of transcoding appeared abnormal.

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

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

Since the function prototype of decode is decode([encoding], [errors='strict']), the second parameter can be used to control the error handling strategy. The default parameter is strict, which means to throw an exception when encountering illegal characters.

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

This method really helps a lot. For the kind of storage in the database full Angle and half of the illegal characters can be so to solve the problem oh!

Character encoding is always a headache!


Common operations for Python-DBAPI are provided above.

The relevant operation methods of API are summarized as follows:

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

Step 1: import the relevant modules


import MySQLdb


import pymssql

Step 2: open the connection


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


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

[the method is defined by its own function prototype]

Step 3: once the connection is complete, start creating an cursor. This object is used to send a request operation to the database.


curs = conn.cursor()

Equivalent to Statement object 1 in JAVA. Commit the SQL command by statement

Step 4: start sending the SQL command to the database server. You can do this


Such as:

curs.execute("select * from test")

The SQL command can be any SQL statement that performs an INSERT operation or an DELETE operation or an SELECT operation

Note that the commit() commit is done once.

Such as:



If the SELECT operation is performed, step 5 is required:

Step 5:



fetchall() is just one method of our cursor object.

Now you can extract the relevant information



Look at the code I wrote:

def test(self):
    conn = self.connect()
    sql="select * from bbs where id<20"
    curs = conn.cursor()  # get 1 Vernier objects 
    curs.execute(sql)    # perform 1 a SQL statements 
    rows=curs.fetchall()  # Get the entire query result set 
    for i in range(len(rows)): #
      print "Row",i,"name",rows[i][3],"value",rows[i][4]

I've learned that the 1 line of records obtained by this method is actually a data structure of 1 tuple.

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

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

And then we'll look at extracting it from the database and encoding it and decoding it



After the operation of the database can be directly so to write the code!

http: / / pymssql. sourceforge. net/userguide html have detailed help documentation

For more information about Python, please visit our site: Python common database operations skills summary, Python coding skills summary, Python pictures skills summary, "Python data structure and algorithm tutorial", "Python Socket programming skills summary", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article is helpful to you Python programming.

Related articles: