Skip to content
Advertisement

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.

Advertisement

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!

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement