Python – Using pyodbc to connect to remote server using info from Excel data connection

I have an excel (albeit, one that’s on our company server) that has a data connection to our SQL database so we can make nice pivot tables.

I would like to get that data into python (on my local computer) so I can do some faster analysis.

I have installed pyodbc.

Here is the “connection string” from the excel:

Provider=SQLOLEDB.1;Password=**mypassword**;Persist Security Info=True;User
ID=**myusername**;Initial Catalog=**catalogename**;Data 
Source=**datasourcename**;Use Procedure for Prepare=1;Auto 
Translate=True;Packet Size=4096;Workstation ID=**workstationid**;Use 
Encryption for Data=False;Tag with column collation when possible=False

and now I have the python script:

import pyodbc
cnxn = pyodbc.connect(DRIVER='{SQL Server}',SERVER='***server ip address**',DATABASE='**cataloguename**',UID='**myusername**',PWD='**mypassword**')
cursor = cnxn.cursor()

and I am getting errors saying that SQL Server does not exist or access denied.

Any help is greatly appreciated.

Answer

You don’t need the single quotes around each field, and may need a port… Try something like this:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.123.456;PORT=1433;DATABASE=yourdb;UID=your_user;PWD=your_pw;')

Good luck!