Connecting to an Azure database using SQLAlchemy in Python

Question

I am trying to connect to an Azure database using SQLAlchemy in Python.

My code is the following:

engine_azure = \
create_engine('mssql+pyodbc://{Server admin login}:{password}@{Server name}.database.windows.net:1433/{AdventureWorksLT}', echo=True)

I get the following message:

C:\ProgramData\Anaconda3\lib\site-packages\sqlalchemy\connectors\pyodbc.py:92: SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections
  "No driver name specified; "

Then I run the following code:

print(engine_azure.table_names())

I get the following message:

DBAPIError: (pyodbc.Error) ('01S00', '[01S00] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0) (SQLDriverConnect)')

Answer 1

There are 2 issues with your connection string:

1. As per the SQLAlchemy documentation: The delimeters must be URL escaped when using a pass-through exact pyodbc string.

2. And you do not specify the sql driver name either.

You can use the code below, which works fine at my side:

import pyodbc
from sqlalchemy import create_engine
import urllib

params = urllib.parse.quote_plus \ # urllib.parse.quote_plus for python 3
(r'Driver={ODBC Driver 13 for SQL Server};Server=tcp:yourDBServerName.database.windows.net,1433;Database=dbname;Uid=username@dbserverName;Pwd=xxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine_azure = create_engine(conn_str,echo=True)

print('connection is ok')
print(engine_azure.table_names())

Test result:

And for the connection string, you can get it by going to azure portal -> your database -> connection strings(select the ODBC in this case):

Answer 2

This is what i use in Python3:

params = urllib.parse.quote_plus(
    'Driver=%s;' % driver +
    'Server=tcp:%s,1433;' % server +
    'Database=%s;' % database +
    'Uid=%s;' % username +
    'Pwd={%s};' % password +
    'Encrypt=yes;' +
    'TrustServerCertificate=no;' +
    'Connection Timeout=30;')

conn_str = 'mssql+pyodbc:///?odbc_connect=' + params
engine = create_engine(conn_str)

Answer 3

Python3 snippet that I am using with ODBC Driver 17 for SQL Server. Cost me some time to figure it all out, especially the driver version and params.

import urllib
from sqlalchemy import create_engine

driver = "{ODBC Driver 17 for SQL Server}"
server = "<server-name>.database.windows.net"
database = "<db-name>"
user = "<db-user>"
password = "<db-password>"

conn = f"""Driver={driver};Server=tcp:{server},1433;Database={database};
Uid={user};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"""

params = urllib.parse.quote_plus(conn)
conn_str = 'mssql+pyodbc:///?autocommit=true&odbc_connect={}'.format(params)
engine = create_engine(conn_str, echo=True)

engine.execute("SELECT 1")

Furthermore, I needed to install the following drivers/tools on macOS:

brew install msodbcsql17 mssql-tools

Answer 4

None of the solutions posted so far worked for me.

Instead, I had to specify Driver ({SQL Server}) which worked perfectly.

params = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=sqlhost.database.windows.net;DATABASE=pythonSQL;UID=username@sqldb;PWD=password56789")
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine_azure = create_engine(conn_str,echo=True)

Source:

https://gist.github.com/timmyreilly/f4a351eda5dd45aa9d56411d27573d7c

Answer 5

Step 1: Install Azure SQL DB Drivers.

Install the new version of SQL DB Drivers using official documentation: Linux, MacOS, Windows

Major update to previous answers: use the last supported version of DB driver ODBC Driver 17 for SQL Server instead of outdated versions ODBC Driver 13 for SQL Server or versions without explicitly defined a version, e.g. SQL Server.

Step 2: Install sqlalchemy package

Just print in terminal: pip install SQLAlchemy

Step 3: Wrapping specific DB logic in AzureDbConnection class

from dataclasses import dataclass
from typing import Dict, Any, Iterable
from pandas import DataFrame
from sqlalchemy import create_engine, inspect
import urllib

@dataclass(frozen=True)
class ConnectionSettings:
    """Connection Settings."""
    server: str
    database: str
    username: str
    password: str
    driver: str = '{ODBC Driver 17 for SQL Server}'
    timeout: int = 30


class AzureDbConnection:
    """
    Azure SQL database connection.
    """
    def __init__(self, conn_settings: ConnectionSettings, echo: bool = False) -> None:
        conn_params = urllib.parse.quote_plus(
            'Driver=%s;' % conn_settings.driver +
            'Server=tcp:%s,1433;' % conn_settings.server +
            'Database=%s;' % conn_settings.database +
            'Uid=%s;' % conn_settings.username +
            'Pwd={%s};' % conn_settings.password +
            'Encrypt=yes;' +
            'TrustServerCertificate=no;' +
            'Connection Timeout=%s;' % conn_settings.timeout
        )
        conn_string = f'mssql+pyodbc:///?odbc_connect={conn_params}'

        self.db = create_engine(conn_string, echo=echo)

    def connect(self) -> None:
        """Estimate connection."""
        self.conn = self.db.connect()

    def get_tables(self) -> Iterable[str]:
        """Get list of tables."""
        inspector = inspect(self.db)
        return [t for t in inspector.get_table_names()]

    def dispose(self) -> None:
        """Dispose opened connections."""
        self.conn.close()
        self.db.dispose()

Major update to previous answers: do not forget to close the connection and dispose of the DB engine explicitly as soon as it stops being needed.

Enjoy!

Set connection settings and credentials using Azure DB blade on Azure Portal:

conn_settings = ConnectionSettings(
    server='<db_server_name>.database.windows.net', 
    database='<db_name>', 
    username='<user_name>', 
    password='<passworf>')

Open DB connection:

db_conn = AzureDbConnection(conn_settings)
db_conn.connect()

Test connection (for example, get available tables list), do other stuff, and close it finally:

try:
    for t in db_conn.get_tables():
        print(t)
    # Do another DB-related stuff:
    # ...
finally:
    db_conn.dispose()