-
Notifications
You must be signed in to change notification settings - Fork 20
Quick Start
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.
import mssql_python
# 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 = mssql_python.connect(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.
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 nameThe 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)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.
The fetchmany() function is used to retrieve a specified number of 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. If no rows are left, it returns an empty list.
fetchmany() accepts size as its parameter. size defines the maximum number of rows to return. If the size parameter is not provided, it defaults to the value of the arraysize attribute, which can be modified to control the number of rows fetched by default.
The arraysize attribute is defined in the Cursor class. It defaults to 1 and can be changed to specify how many rows should be returned by fetchmany() if no explicit size is provided in fetchmany().
cursor.execute("SELECT col_1, col_2 FROM T1")
# Set the number of rows fetched using size parameter
rows = cursor.fetchmany(size=10)
for row in rows:
print(row.col_1, row.col_2)In this example, fetchmany(size=10) retrieves up to 10 rows from the result set. The size parameter specifies the maximum number of rows to fetch. If fewer than size rows are available, fetchmany() will return only the available rows.
cursor.execute("SELECT col_1, col_2 FROM T1")
# Change the default fetch size using arrarysize
cursor.arraysize = 5
rows = cursor.fetchmany()
for row in rows:
print(row.col_1, row.col_2)In this example, fetchmany() retrieves up to 5 rows from the result set. The size parameter is not defined, however, arrarysize specifies the maximum number of rows to fetch which is 5.
You can use fetchmany() in a loop to process large result sets in manageable chunks. This approach allows you to handle large datasets without loading the entire result set into memory at once.
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'])To insert data, provide the SQL insert statement to the execute() method, along with any required parameters.
Adhoc Insert Statement:
cursor.execute("INSERT INTO T1(ID, CITY) VALUES (1, 'SEATTLE')")
conn.commit()Parameterised Insert Statement:
cursor.execute("INSERT INTO PRODUCTS(ID, NAME) VALUES (?, ?)", 1, 'SEATTLE')
conn.commit()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()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 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 = connect("Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;", autocommit=True)
# True by default
print("Default autocommit:", conn.autocommit)
# Switch to manual commit mode
conn.setautocommit(False)
deleted = cursor.execute("DELETE FROM T1 WHERE col_1 <> 'Apple'").rowcount
# Commit the statement, else changes will be lost
conn.commit()