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!