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 cursor.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 cursor.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

Be sure to call conn.commit(). If you don't, or if you haven't set autocommit to True on the connection, your changes will not be saved!

Tips and Tricks

Quotes

Since single quotes are valid in SQL, use double quotes to surround your SQL:

deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

It's also worthwhile considering using 'raw' strings for your SQL to avoid any inadvertent escaping (unless you really do want to specify control characters):

cursor.execute("delete from products where name like '%bad\name%'")   # Python will convert \n to 'new line'!
cursor.execute(r"delete from products where name like '%bad\name%'")  # no escaping

Naming Columns

Some databases (e.g. SQL Server) do not generate column names for calculated fields, e.g. COUNT(*). In that case you can either access the column by its index, or use an alias on the column (i.e. use the "as" keyword).

row = cursor.execute("select count(*) as user_count from users").fetchone()
print('%s users' % row.user_count)

Formatting Long SQL Statements

Long SQL statements are best encapsulated using the triple-quote string format. Doing so does create a string with lots of blank space on the left, but whitespace should be ignored by database SQL engines. If you still want to remove the blank space on the left, you can use the dedent() function in the built-in textwrap module. For example:

import textwrap
sql = textwrap.dedent("""
    select p.date_of_birth,
           p.email,
           a.city
    from person as p
    left outer join address as a on a.address_id = p.address_id
    where p.status = 'active'
      and p.name = ?
""")
rows = cursor.execute(sql, 'John Smith').fetchall()

fetchval

If you are selecting a single value you can use the fetchval convenience method. If the statement generates a row, it returns the value of the first column of the first row. If there are no rows, None is returned:

maxid = cursor.execute("select max(id) from users").fetchval()

Most databases support COALESCE or ISNULL which can be used to convert NULL to a hardcoded value, but note that this will not cause a row to be returned if the SQL returns no rows. That is, COALESCE is great with aggregate functions like max or count, but fetchone is better when attempting to retrieve the value from a particular row:

cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
cursor.execute("select coalesce(count(*), 0) from users").fetchone()[0]

However, fetchval is a better choice if the statement can return an empty set:

# Careful!
cursor.execute("""
    select create_timestamp
    from photos
    where user_id = 1
    order by create_timestamp desc
    limit 1
""").fetchone()[0]

# Preferred
cursor.execute("""
    select create_timestamp
    from photos
    where user = 1
    order by create_timestamp desc
    limit 1
""").fetchval()

The first example will raise an exception if there are no rows for user_id 1. The fetchone() call returns None. Python then attempts to apply [0] to the result (None[0]) which is not valid.

The fetchval method was created just for this situation - it will detect the fact that there are no rows and will return None.