How do I establish the connection between my SQL server and python script?

Question

I am trying to connect my Azure database to my Jupyter Notebook Python script, but I keep getting an error saying that the connection can't be established.

This is my code:

# Create the SQLAlchemy engine
connection_string = "Driver=/usr/local/lib/libmsodbcsql.18.dylib;Server=tcp:XXX.database.windows.net,1433;Database=XXX;Uid=XXX;Pwd={XXX};Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;ssl_version=TLSv1.2"

engine = sqlalchemy.create_engine(f"mssql+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}")

This is the error I am getting:

Error: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish a connection (0) (SQLDriverConnect)')
(Background on this error at: http://sqlalche.me/e/14/e3q8)

I am using a Mac. I don't know if it is an error with my database settings, but I have tried to mess with the settings and keep getting the same error.

Answer 1

Error: (pyodbc.OperationalError) (‘08001’, ‘[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (0) (SQLDriverConnect)’)

The above error occurs when the SERVER in the Connection String is incorrect. I tried on my machine with the correct credentials, it connected successfully, as you can see in the code below:

import urllib
import sqlalchemy

connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=tcp:*****.database.windows.net,1433;"
    "DATABASE=sampleDB;"
    "UID=admin6391;"
    "PWD=******;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;"
)

engine = sqlalchemy.create_engine(f"mssql+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}")

connection = engine.connect()

try:
    print("Connection successful!")

    table_name = 'sampleTable'
    select_query = sqlalchemy.sql.text(f"SELECT * FROM {table_name}")
    result = connection.execute(select_query)

    rows = result.fetchall()
    for row in rows:
        print(row)

except Exception as e:
    print(f"Error: {e}")

finally:
    if connection:
        connection.close()

Output:

Connection successful!
(1, 'Pavan', 'Balaji', Decimal('31000.0000'), 'Hyderabad')
(2, 'Sai', 'Venkatesh', Decimal('35000.0000'), 'Visakhapatnam')