install

pip install pymssql

Connect to the database

The way pymssql connects databases and uses sqlite is basically the same:

• Creating Connection Objects with connect
• connect.cursor creates cursor objects, and the execution of SQL statements is basically done on the cursor.
• The cursor.executeXXX method executes the SQL statement, cursor.fetchXXX obtains the query results, etc.
• Close cursor and database connection by calling close method

import pymssql

# Server database server name or IP
# user username
# Password password
# Database database name
conn = pymssql.connect(server, user, password, database)

cursor = conn.cursor()

# New, insert operation
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    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 the autocommit attribute is not specified as True, you need to call the commit() method
conn.commit()

# Query operation
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

# You can also use for loops to iterate over query results
# for row in cursor:
#     print("ID=%d, Name=%s" % (row[0], row[1]))

# Close connection
conn.close()

Cautions in the Use of Cursors

Queries with only one cursor per connection are active, 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() )  # It shows the result of query with c2 cursor.

print( "John Doe" )
print( c2.fetchall() )  # There will be no result.

To avoid these problems, the following two ways can be used:

• Create multiple connections to ensure that multiple queries can be executed concurrently on cursors with different connections
• The next query is executed after the cursor query result is obtained by fetchall method, as follows:

c1.execute('SELECT ...')
c1_list = c1.fetchall()

c2.execute('SELECT ...')
c2_list = c2.fetchall()

Cursor returns behavior dictionary variables

In the example above, each behavior tuple type of the query result obtained by the cursor.
The cursor can be returned to the dictionary variable by specifying as_dict parameter when creating the cursor.
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')
for row in cursor:
    print("ID=%d, Name=%s" % (row['id'], row['name']))

conn.close()

Use the with statement (context manager)

You can close connections and cursors by using the with statement without the call close method shown.

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']))

Calling stored procedures

The version of pymssql 2.0 or above can call stored procedures through cursor.callproc method.

with pymssql.connect(server, user, password, database) as conn:
    with conn.cursor(as_dict=True) as cursor:
        # Create stored procedures
        cursor.execute("""
        CREATE PROCEDURE FindPerson
            @name VARCHAR(100)
        AS BEGIN
            SELECT * FROM persons WHERE name = @name
        END
        """)

        # Calling stored procedures
        cursor.callproc('FindPerson', ('Jane Doe',))
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))