September 4, 2020 at 3:11 pm
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!
September 5, 2020 at 12:39 am
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)
October 6, 2020 at 4:52 am
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?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy