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

Question

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 1

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!

Answer 2

For connecting to the remote server SQL, I found this solution to work. Here, the port number is important which is 1433.

import pyodbc
server = 'tcp:***your_server_name*,1433' (i.e your_server_name = abcd.mydomain.com)
database = 'your_DB_NAME' 
username = 'Your_USER_NAME' 
password = 'YOUR_PASSWORD' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()`enter code here`
cursor.execute('SELECT top 1 * FROM [DBNAME].[SchemaNAME].[YourTable]')
for row in cursor:
    print(row)

And If you don't have username and password then just remove username and password and add Trusted_Connection=yes

See here example:

import pyodbc
server = 'tcp:***your_server_name*,1433' (i.e your_server_name = abcd.mydomain.com)
database = 'your_DB_NAME' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';Trusted_Connection=yes')
cursor = cnxn.cursor()
cursor.execute('SELECT top 1 * FROM [DBNAME].[SchemaNAME].[YourTable]')
for row in cursor:
    print(row)

Ckick here for more details