python's default database is SQLlite, but it can also support MySql and SQL server. This article introduces how to install and connect the pymssql library under Windows. . .

Environment: Windows_64 bit

Version: python3.6

1. Brief introduction

pymssql is a python database interface, built on the basis of FreeTDS, encapsulates the _mssql module, and follows the python DBAPI specification, and FreeTDS is a public open source library that connects to sqlserver in C language.

Their relationship is as follows:

For details, please refer to the official documentation: pymssql — pymssql 2.1.4 documentation

2. Download and install

There are two installation methods, which are described below:

1,vs+pymssql

According to the official documentation, to install pymssql, you first need to install vs. According to the above figure, select the vs version corresponding to your python version and the operating system version to download the installation package:

FreeTDS download address: https://github.com/ramiro/freetds/releases/

pymssql download address: pymssql · PyPI

Download the corresponding version and install it. pymssql can be installed using the pip command or the installation package, depending on personal preference.

After installation, you can enter the command line through CMD, and then enter pip show mymssql to check whether the installation is successful. The example is as follows:

PS: In this method of installation, there may be some confusing error reports, which is very painful. If this method of installation fails, you can see the second installation method below. . .

2. Install the .whl package

If you have carefully read the children's shoes introduced in the official document, you should have noticed the content here. You can install pymssql by installing the .whl package under Windows. The document is introduced as follows:

First, enter the command import pip; print(pip.pep425tags.get_supported()) in the python shell to check the file name and version supported by pip. The example is as follows:

Then you can get the corresponding .whl file at python's unofficial Windows extension package site, link: https://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql

After entering the site, press Ctrl+F to search for pymssql, and then click. The example is as follows:

Get here, select the .whl package you need to download:

Then enter your python installation package directory Scripts, use the pip command to install:

3. Connection and use

1. Working principle of pymssql

①. Use connect to create a connection object;

②, connect.cursor creates a cursor object, and the execution of the SQL statement is executed on the cursor;

③. The cursor.execute() method executes the SQL statement, and the cursor.fetch() method obtains the query result;

④. Call the close method to close the cursor and the database connection;

2. Sample code

1 # coding=utf-8
 2 import pymssql
 3 
 4 class SQLServer:   
 5     def __init__(self,server,user,password,database):
 6     # Class constructor, initialize DBC connection information
 7         self.server = server
 8         self.user = user
 9         self.password = password
10         self.database = database
11 
12     def __GetConnect(self):
13     # Get the database connection information, return conn.cursor()
14         if not self.database:
15             raise(NameError,"No database information set")
16         self.conn = pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database)
17         cur = self.conn.cursor()
18         if not cur:
19             raise(NameError,"Unable to connect to the database")  # Assign DBC information to cur
20         else:
21             return cur
22              
23     def ExecQuery(self,sql):
24         '''
25         Execute query
26         returns a containing tuple of list，list is the record line of the element, tuple Record field values ​​for each row
27         '''
28         cur = self.__GetConnect()
29         cur.execute(sql) # Execute query
30         result = cur.fetchall() # fetchall() to get query results
31         # Close the database connection after querying
32         self.conn.close()
33         return result
34 
35 def main():
36     msg = SQLServer(server="127.0.0.1",user="test",password="Test321",database="TEST")
37     result = msg.ExecQuery("SELECT TOP 1 Value FROM t_Security_Code WHERE Mobile = '18501007700' ORDER BY InsertTime DESC")
38     for (Value) in result:
39         print(Value)
40  
41 if __name__ == '__main__':
42     main()

PS: Initialize database connection information, database address: server, password: password, database: database! ! !

At the beginning, I also customized it personally. Later, I always reported an error and got it done after querying the configuration information. The specific connect information is as follows:

Two more points:

①. A cursor can only execute one SQL statement. If you need to execute multiple SQL statements, you need to create multiple cursors. Remember! ! !

②. When there is a python default value (such as index) in the SQL statement, just add backticks to it. . .

Regarding the download, installation and basic use of the pymssql module, the content is as above, for reference only. . .

-There must be a way, and then there will be success- Finally, I wish you all get a satisfactory offer as soon as possible~

If you don’t want to grow wildly alone, can’t find system resources, can’t get help for problems, and give up after persisting for a few days, you can click the [small card] below to join us and we can discuss and communicate together. There will be various Software testing materials and technical exchanges.

Today I mainly share my learning route and network disk resources with you. I will share some interview strategies in the future. Friends who have been helped, you can like and support~