Connecting to SQL server 2019 from python/Jupyter Notebooks

  • pietlinden

    SSC Guru

    Points: 62895

    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: 62895

    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 2 weeks, 6 days ago by  pietlinden.

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

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