Connecting to SQL server 2019 from python/Jupyter Notebooks

  • pietlinden

    SSC Guru

    Points: 62902

    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!

  • pietlinden

    SSC Guru

    Points: 62902

    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 1 month, 2 weeks ago by  pietlinden.
  • pietlinden

    SSC Guru

    Points: 62902

    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