Connect python to MS SQL Database

 

1. Install pyodbc with conda
   
   conda install -c anaconda pyodbc=4.0.17
   
   pyodbc is a Python DB API 2 module for ODBC. This project provides an up-to-date, convenient interface to ODBC using native data types like datetime and decimal.
   
2. Write in jupyter notebook connection to database.
   
   import pyodbc
   cnxn = pyodbc.connect('Trusted_Connection=yes;DRIVER={SQL Server};SERVER=DESKTOP\SQLSERVER;DATABASE=positive;UID=user;PWD=password')
   
   Without Trusted Connection following error appears
   ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
3. Print data row by row
   
   cursor = cnxn.cursor()
   cursor.execute("SELECT [id],[ttext] FROM [dbo].[sortpos]")
   rows = cursor.fetchall()
   for row in rows:
       print(row.id, row.ttext)
   
4. Close connection
   
   pyodbc.Connection.close(cnxn)