Microsoft SQL (MSSql) via pyodbc¶
Description:
- Work with data synchronously in MSSQL
Resources:
- [GitHub] https://github.com/mkleehammer/pyodbc
- [Docs] https://github.com/mkleehammer/pyodbc/wiki
- [Docs (ODBC Driver for SQL Server)] https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MSSQL_HOST: hostname of your Microsoft SQL ServerMSSQL_PORT: optional, defaults to1433MSSQL_USER: SQL Server loginMSSQL_PASSWORD: password for that loginMSSQL_TRUST_SERVER_CERTIFICATE: optional, defaults tono. Set it toyesonly for a test server with a self-signed certificate.
The example uses Microsoft's ODBC Driver 18 for SQL Server, which must be installed on the machine running Jupyter.
Example 1¶
Read data synchronously from an MSSQL database
In [ ]:
Copied!
import os
import pyodbc
# The ODBC drivers installed on this machine
print(pyodbc.drivers())
def mssql_basic():
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
cnxn = pyodbc.connect(dsn)
print(f"Connected to database: {cnxn}")
crsr = cnxn.cursor()
rows = crsr.execute("select @@VERSION").fetchall()
print(rows)
crsr.close()
cnxn.close()
mssql_basic()
import os
import pyodbc
# The ODBC drivers installed on this machine
print(pyodbc.drivers())
def mssql_basic():
dsn = (
"DRIVER={ODBC Driver 18 for SQL Server};"
f"SERVER={os.environ['MSSQL_HOST']},{os.environ.get('MSSQL_PORT', '1433')};"
f"UID={os.environ['MSSQL_USER']};PWD={os.environ['MSSQL_PASSWORD']};"
f"TrustServerCertificate={os.environ.get('MSSQL_TRUST_SERVER_CERTIFICATE', 'no')}"
)
cnxn = pyodbc.connect(dsn)
print(f"Connected to database: {cnxn}")
crsr = cnxn.cursor()
rows = crsr.execute("select @@VERSION").fetchall()
print(rows)
crsr.close()
cnxn.close()
mssql_basic()