How to Connect Microsoft SQL Server Database to Python
Purpose #
I am attending a coding academy and wanted to show my class a presentation on how to set-up software for connecting to a SQL database using Visual Studio Code, MS SQL server, and Python 3 (hence no formalities).
I didn’t want to use my local Microsoft account for authentication for security reasons, this also requires disabling SSL encryption (as shown in the code) - if you are just doing local testing then this is fine.
Many tutorials exist but are very unclear or don’t show how this is done, so I recorded the video below to show how specifically.
Requirements #
- Microsoft SQL Server set up
- ODBC Driver for SQL Server
- pyodbc
Outcome #
Code to do a Basic Connection and DB Query in VSCODE #
Fill in the blanks, including entering your table name in query
import pyodbc
# Edit string for your server name, username, password
connection = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER=yourservername;DATABASE=yourdatabasename;UID=yourusername;PWD=yourpassword;Encrypt=No;')
# Create a cursor from the connection
cursor = connection.cursor()
# Example query to execute
query = 'SELECT * FROM table'
# Execute the query
cursor.execute(query)
# Fetch and print all results
for row in cursor.fetchall():
print(row)
# Close the cursor and connection
cursor.close()
connection.close()