Python link MSSQL Python library pymssql

Connecting to a database

Pymssql connect to a database in the same way that you use SQLite:

• Use to connect create a Connection object
• connect.cursorCreates a cursor object, and the execution of the SQL statement is basically performed on the cursor
• cursor.executeXXXMethods Execute SQL statements, cursor.fetchXXX get query results, etc.
• Calling close methods to close cursors cursor and database connections

import pymssql# server    数据库服务器名称或IP# user      用户名# password  密码# database 数据库名称conn = pymssql.connect(server, user, password, database)cursor = conn.cursor()# 新建、插入操作

Cursor.execute ("""IF object_id (' persons ', ' U ') is not a null DROP table personscreate table persons (ID INT not NULL, name Varch AR (+), SalesRep VARCHAR (+), PRIMARY KEY (ID))""") Cursor.executemany ("INSERT into Persons VALUES (%d,%s,%s)",    [(1,'John Smith','John Doe'),     (2,'Jane Doe','Joe Dog'),     (3,'Mike T.','Sarah H..')])#If you do not specify the Autocommit property as true, you need to call the commit () methodConn.commit ()

# 查询操作

Cursor.execute ('SELECT * from persons WHERE salesrep=%s','John Doe') Row=Cursor.fetchone () whilerow:Print("id=%d, name=%s"% (Row[0], row[1]) Row=Cursor.fetchone ()#You can also use a for loop to iterate over query results#For row in cursor:#print ("id=%d, name=%s"% (Row[0], row[1]))#Close ConnectionConn.close ()

Note : The parameters of the query operation in the example are used %s instead of ‘%s‘ , if the parameter value is a string , the single quotation marks are added automatically when the statement is executed

Cursor Usage Considerations

A connection can have only one cursor query active at a time, as follows:

c1 = conn.cursor()c1.execute(‘SELECT * FROM persons‘)c2 = conn.cursor()c2.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘)print( "all persons" )print( c1.fetchall() ) # 显示出的是c2游标查询出来的结果print( "John Doe" )print( c2.fetchall() ) # 不会有任何结果

To avoid the above problems you can use the following two ways:

• Create multiple connections to ensure that multiple queries can be executed in parallel on different connected cursors
• Use fetchall the method to get to the cursor query results before executing the next query, as follows:

C1.execute ('SELECT ... '  = c1.fetchall () c2.execute ('SELECT ... '  = C2.fetchall ()

Cursor return behavior dictionary variable

In the above example, the target gets the query results for each of the behavior tuple types,
You can as_dict make a cursor return a dictionary variable by specifying a parameter when the cursor is created.
The key in the dictionary is the column name of the data table

conn =pymssql.connect (server, user, password, database) cursor= Conn.cursor (as_dict=True) Cursor.execute ('SELECT * from persons WHERE salesrep=%s','John Doe') forRowinchcursor:Print("id=%d, name=%s"% (row['ID'], row['name'])) Conn.close ()

Use withStatement (context Manager)

You can with close close joins and cursors by using statements to omit the calling method that is displayed

with pymssql.connect(server, user, password, database) as conn:    with conn.cursor(as_dict=True) as cursor: cursor.execute(‘SELECT * FROM persons WHERE salesrep=%s‘, ‘John Doe‘) for row in cursor: print("ID=%d, Name=%s" % (row[‘id‘], row[‘name‘]))

Call a stored procedure

pymssql 2.0.0 versions above can cursor.callproc invoke stored procedures by means of methods

With pymssql.connect (server, user, password, database) as Conn:with conn.cursor (as_dict=True) as cursor:#create a stored procedureCursor.execute ("""CREATE PROCEDURE Findperson @name VARCHAR as BEGIN SELECT * from Persons WHE RE name = @name END""")        #call a stored procedureCursor.callproc ('Findperson', ('Jane Doe',))  forRowinchcursor:Print("id=%d, name=%s"% (row['ID'], row['name']))

Reference: http://pymssql.org/en/stable/pymssql_examples.html

Python link MSSQL Python library pymssql