Quick Start

Get Started with mssql-python

Connect to a Database

Provide a connection string to the mssql-python connect() method to get a connection object. After establishing the connection, you can request a cursor from it.

Example:

from mssql_python import connect

# Specify connection string
conn_str = "SERVER=<your_server_name>;DATABASE=<your_database_name>;UID=<your_user_name>;PWD=<your_password>;Encrypt=yes;"

# Create connection object
conn = Connection(conn_str)

# Connection object will return cursor
cursor = conn.cursor()

TODO: There are lots of options when connecting the database through connect() method. Refer to Connecting to Databases section for more details.

Reading Data Using SELECT

SQL statements are executed through the Cursor's execute() function. When a SELECT statement returns rows, one of the Cursor's fetch functions can be used to retrieve those records. The following fetch functions are currently supported: fetchone(), fetchall(), and fetchmany(). If there are no rows, fetchone() will return None, while fetchall() and fetchmany() will return empty lists.

cursor.execute("SELECT col1, col2 FROM T1")

# Using fetchone() method
row = cursor.fetchone()
if row:
    print(row)

# Using fetchall() method
rows = cursor.fetchall()
print("Rows:", rows)

In the following sample, row objects can be accessed both by column index and by column name, providing flexibility in handling query results. Essentially, row objects are similar to tuples but provide additional functionality by allowing access to columns by their names.

cursor.execute("select col_1, col_2 from T1")
row = cursor.fetchone()
print('name:', row[1])          # access by column index (zero-based)
print('name:', row.col_1)       # access by name

Fetchone Method - fetchone()

The fetchone() function fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

while True:
    # fetch the next row from the query result set. If there are no more rows to fetch, row will be None.
    row = cursor.fetchone()
    if not row:
        break
    print('id:', row.col_1)

Fetchall Method - fetchall()

The fetchall() function is used to retrieve all remaining rows from the result set of a query. When called, it returns these rows as a list of sequences, typically a list of tuples. Each tuple represents a row from the result set, with each element in the tuple corresponding to a column in that row.

cursor.execute("SELECT col_1, col_2 FROM T1")
rows = cursor.fetchall()
for row in rows:
    print(row.col_1, row.col_2)

Since fetchall() retrieves all remaining rows at once, it stores them in memory. This means that if the result set is very large, it could consume a significant amount of memory, potentially leading to memory exhaustion. Therefore, it's important to be cautious when using fetchall() with large datasets.

If there are no rows left to fetch, fetchall() will return an empty list. This can be useful for checking if the query returned any results without having to handle None values.

Parameters

mssql-python supports the use of parameters in SQL queries by using question marks ? as placeholders as per DBAPI specifications. You can provide the values for these placeholders by passing them after the SQL statement:

cursor.execute("""
    SELECT col1_1, col_2 FROM T1 WHERE col_3 < ? AND col_4 = ? """
    ,2024, 'y')

Using this method is safer than embedding the values directly into the SQL string because the parameters are sent to the database separately, which helps protect against SQL injection attacks. Additionally, it is more efficient when executing the same SQL statement multiple times with different parameters, as the SQL statement will be prepared only once. Additionally, only the most recent prepared statement is kept, therefore, if you execute different SQL statements alternatively, each one will need to be prepared again.

The Python DB API specifies that parameters may be provided as sequence, in the example mentioned below, the parameters are passed as a list:

cursor.execute("""
    SELECT col1_1, col_2 FROM T1 WHERE col_3 < ? AND col_4 = ? """
    ,[2024, 'y'])

DML Operations

Insert Records

To insert data, provide the SQL insert statement to the execute() method, along with any required parameters.

Adhoc Insert:

cursor.execute("INSERT INTO T1(ID, CITY) VALUES (1, 'SEATTLE')")
conn.commit()

Parameterised Insert:

cursor.execute("INSERT INTO PRODUCTS(ID, NAME) VALUES (?, ?)", 1, 'SEATTLE')
conn.commit()

Update and Delete Records

To update and delete the records in the database, provide the SQL insert statement to the execute() method, along with any required parameters.

Update Statement:

cursor.execute("UPDATE T1 SET col_1 = ? WHERE col_2 = ?", 'iPhone', 'Apple')
conn.commit()

DELETE Statement:

cursor.execute("DELETE FROM T1 WHERE col_1 <> ?", 'Apple')
conn.commit()

Rowcount

According to DBAPI specifications, rowcount is a read-only attribute that indicates the number of rows affected by the last execute() call, whether it's a SELECT or DML statement. You can access this information using the Cursor's rowcount attribute. Since the execute() method always returns the cursor, you might see code that directly uses this attribute.

cursor.execute("UPDATE T1 SET col_1 = ? WHERE col_2 = ?", 'iPhone', 'Apple')
print(cursor.rowcount, 'Table Updated')
conn.commit()

deleted = cursor.execute("DELETE FROM T1 WHERE col_1 <> 'Apple'").rowcount
conn.commit()

Autocommit

Autocommit mode determines whether each SQL statement is immediately committed to the database or if you must explicitly commit changes. When autocommit is enabled, every change you make to the database is instantly saved. When it is disabled, any changes remain pending until you call commit. This allows for transactions that can be rolled back on errors. Autocommit is commonly set during connection initialization or updated at runtime if your application workflow requires fine-grained transaction control.

By default, the connection is initialized with autocommit set to True. This means that each SQL statement is committed immediately. If you need transactional control (i.e., the ability to roll back), set autocommit to False and call the commit or rollback methods explicitly.

It is important to know that you must call conn.commit() when autocommit is set to False. If you don't your changes will not be saved.

To retrieve the current mode use the autocommit property. To set or change the current mode, use setautocommit() function.

conn = Connection("Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;", autocommit=True)
print("Default autocommit:", conn.autocommit)    # True by default
conn.setautocommit(False)                        # Switch to manual commit mode
deleted = cursor.execute("DELETE FROM T1 WHERE col_1 <> 'Apple'").rowcount
conn.commit()                                    # Commit the statement, else changes will be lost