Connection to SQL Database

Connecting to SQL Server Database

The connect() function simplifies creating a connection to SQL Server by returning a Connection object. Internally, it initializes and configures connection handle, processes the provided connection string, and attempts to establish a session with the target database.

The Connection class itself supports fundamental DB-API 2.0 features, you can create cursors to run SQL statements, control transactional behavior via methods like commit and rollback, and close the connection once you’re done. By default, autocommit mode is set to True, meaning all statements are committed immediately—if you need explicit transactional control, simply set setautocommit(False) and invoke commit or rollback as needed.

The connection string traditionally indicates the database server, the specific database to connect to, driver settings, and security details (e.g., Trusted Connection).

Following are the methods and attributes exposed through Connection Class:

connect() Method

Creates a new Connection object.

from mssql_python import connect

conn_str = "Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;"
conn = connect(conn_str)

cursor() Method

Creates and returns a cursor object for executing SQL commands.

cursor = conn.cursor()
cursor.execute("SELECT * FROM T1")
rows = cursor.fetchall()

commit() Method

Commits the current transaction. Only necessary if autocommit is off.

conn.commit()

rollback() Method

Rolls back the current transaction. Only necessary if autocommit is off.

conn.rollback()

close() Method

Closes the connection, freeing any resources. No further operations can be performed afterward.

conn.close()

Autocommit Attribute

The autocommit is a read_only attribute which determines whether SQL statements are committed to the database automatically or only when explicitly requested. By default, autocommit is set to True, meaning any changes made (such as INSERT, UPDATE, or DELETE commands) are immediately committed and cannot be rolled back.

The setautocommit() function enables or disables autocommit mode for the current connection.

Behavior:

• When autocommit is True, each DML statement (INSERT, UPDATE, DELETE) completes as soon as it is executed.
• When autocommit is False, all changes remain in a temporary state until commit is called. If an error occurs, you can roll back pending changes.
• You can switch autocommit mode at any time by calling setautocommit(True) or setautocommit(False).
• You can check the current mode via the autocommit property; it returns True if autocommit is enabled or False if disabled.

from mssql_python import connect

# By default, autocommit is True
conn = connect("Server=<your_server_name>;Database=<your_db_name>;Trusted_Connection=yes;")
# Prints True since autocommit is enabled
print("Autocommit:", conn.autocommit)

# Disable autocommit
conn.setautocommit(False)
cursor = conn.cursor()
cursor.execute("INSERT INTO T1 (col_1) VALUES (?)", "1234")
# Changes remain uncommitted until explicitly committed
conn.commit()