Connecting to SQL server 2019 from python/Jupyter Notebooks

Question

Post reply

Connecting to SQL server 2019 from python/Jupyter Notebooks

pietlinden

SSC Guru

Points: 63506

September 4, 2020 at 3:11 pm

#3786429

Does anybody have an example of connecting to a local instance of SQL Server from Jupyter notebooks?

Do I really have to create a DSN to do this?

## define some variables so this stuff is easier to modify
server = 'DESKTOP-D19S9C8/NONALAND'
database_name = 'AdventureWorks2017'
query = 'SELECT TOP 10 BusinessEntityID,FirstName,LastName,ModifiedDate FROM Person.Person WHERE PersonType = "EM";'

conn_str = (r'DRIVER={SQLServer};Server='+server+';database='+ database_name +';Trusted_Connection=yes')
print(conn_str)
            
with pyodbc.connect(conn_str) as conn:
    cursor = conn.cursor()
    cursor.execute(query)
    data = cursor.fetchall()

I think my connection string is wrong... but I'm not sure how to fix it.

If anybody knows/has a good article on Python/pyodbc for dummies, I'd be happy to read it. =)

thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Created a User DSN, and it worked a champ.

Then for fun I created a trivial stored procedure just to see if I could call it.

CREATE SCHEMA junk;

CREATE PROC junk.Top10Peeps
AS
SELECT BusinessEntityID,
FirstName,
LastName
FROM Person.person
ORDER BY BusinessEntityID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Then my super-complex notebook is just:

import pyodbc 

print('running a stored procedure to analyze later\n')

qry = 'junk.Top10Peeps' # this is actually a stored procedure

conn_str = 'FileDSN=C:/Users/User/AW2017.dsn'
conn = pyodbc.connect(conn_str)

cursor = conn.cursor()
cursor.execute(qry)

for row in cursor:
    print(row)

Then I decided I had to try to call a parameterized stored procedure... (not claiming that this is the best way, but it did work <g>)

import pyodbc 

# params are @LastStartsWith VARCHAR(10) and @FirstStartsWith VARCHAR(4)
last_name_starts_with = 'Martin'
first_name_starts_with = 'Al'

qry = 'junk.NamesThatStartWith' # this is actually a stored procedure

conn_str = 'FileDSN=C:/Users/User/AW2017.dsn'
conn = pyodbc.connect(conn_str)

#--- use the two variables to pass parameter values
params = (last_name_starts_with, first_name_starts_with)

crsr = conn.cursor()
crsr.execute("{CALL junk.NamesThatStartWith (?,?)}", params)

for row in crsr:
    print(row)

(This is probably more a note to myself as I'm learning... at least I can come back here and copy and paste it back in case I'm losing my mind later)

This reply was modified 4 years, 3 months ago by pietlinden.

pietlinden SSC Guru Points: 63506 More actions · Answer 2

This is odd. I can connect to SQL Server from Python if I use a File DSN... this mess works:

import pyodbc 

print('running a stored procedure to analyze later\n')

# params are @LastStartsWith VARCHAR(10) and @FirstStartsWith VARCHAR(4)

last_name_starts_with = 'Martin'
first_name_starts_with = 'Al'

qry = 'junk.NamesThatStartWith' # this is actually a stored procedure

#--- this stuff worked ---
#sql = 'exec ' + qry + ' (?, ?)'
# values = (last_name_starts_with, first_name_starts_with)
# sql = 'exec ' + qry + ' ' + last_name_starts_with + ', ' + first_name_starts_with

conn_str = 'FileDSN=C:/Users/User/AW2017.dsn'
conn = pyodbc.connect(conn_str)

#--- use the two variables to pass parameter values
params = (last_name_starts_with, first_name_starts_with)

crsr = conn.cursor()
crsr.execute("{CALL junk.NamesThatStartWith (?,?)}", params)

for row in crsr:
    print(row)

then if I try to connect without the DSN, it fails:

import pandas as pd
import numpy as np
import pyodbc
import sqlalchemy

# boilerplate sql server connection
server = 'DESKTOP-D19S9C8\NONALAND'
database = 'PythonSQL' # enter database name
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';Trusted_Connection=yes;')

When I run that, I get this error:

----> 4 cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';Trusted_Connection=yes;')
      5 
      6 #cnxn_str = 'FileDSN=C:/Users/User/AW2017.dsn'

OperationalError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (67)')

So I found this article, "Resolving could not open a connection to SQL Server errors" and followed the instructions (well, at least I think I did!). Python and Jupyter Notebooks are running on the same machine as SQL Server, and I can connect to my databases if I use a DSN.... like so:

import pyodbc 

print('running a stored procedure to analyze later\n')

# params are @LastStartsWith VARCHAR(10) and @FirstStartsWith VARCHAR(4)

last_name_starts_with = 'Martin'
first_name_starts_with = 'Al'

qry = 'junk.NamesThatStartWith' # this is actually a stored procedure

#--- this stuff worked ---
#sql = 'exec ' + qry + ' (?, ?)'
# values = (last_name_starts_with, first_name_starts_with)
# sql = 'exec ' + qry + ' ' + last_name_starts_with + ', ' + first_name_starts_with

conn_str = 'FileDSN=C:/Users/User/AW2017.dsn'
conn = pyodbc.connect(conn_str)

#--- use the two variables to pass parameter values
params = (last_name_starts_with, first_name_starts_with)

crsr = conn.cursor()
crsr.execute("{CALL junk.NamesThatStartWith (?,?)}", params)

for row in crsr:
    print(row)

(Yeah, I know the code is terrible... but the only really critical part is the DSN stuff works... so I know Python and SQL Server work together just fine on my computer.... because I can connect to the same database from another Notebook on it.)

So what painfully obvious thing am I missing?