Connecting to an Azure database using SQLAlchemy in Python on M1 mac

Question

I am trying to connect to an Azure SQL Server database, but I keep getting this error:

(pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I'm using this code snippet to try connecting:

params = urllib.parse.quote_plus
('Driver={ODBC Driver 18 for SQL Server};Server=tcp:test-server.database.windows.net,1433;Database=test-database;Uid=*****;Pwd=*****;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine_azure = create_engine(conn_str,echo=True)
connection = engine_azure.connect()

I've tried changing the driver to 'SQL Server' and the driver file path '/opt/homebrew/lib/libmsodbcsql.18.dylib'. None of these have worked.

I am able to connect using the pyodbc.connect() function, so I know my connection string is correct.

In the /opt/homebrew/etc/ folder I have the odbc.ini file which is empty and the odbcinst.ini file which is as follows:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib
UsageCount=1

odbcinst -j gives:

unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/wholt2/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I created shortcuts to from the /etc/ folder to the /opt/homebrew/etc/ folder for the odbc.ini and odbcinst.ini files. Strangely, the ODBCDataSources and .odbc.ini files don't exist. I've tried uninstalling and reinstalling the drivers using brew install msodbcsql18 mssql-tools18, but nothing seems to work. Any help would be greatly appreciated. Thank you!

Have you confirmed that the installed file paths (using brew list msodbcsql18) match what you're trying to edit into the .ini files? — AlwaysLearning
– AlwaysLearning, Commented Nov 11, 2023 at 22:53
Have you read Creating URLs Programmatically and tried to use URL.create('mssql+pyodbc', ...) instead? See also Building a connection URL for mssql+pyodbc with sqlalchemy.engine.url.URL — AlwaysLearning
– AlwaysLearning, Commented Nov 11, 2023 at 23:04

Bhavani · Accepted Answer · 2023-11-13 11:11:00Z

1

You can try the code below to connect to an Azure SQL database with SQL Alchemy:

import urllib
import sqlalchemy
import pandas as pd

sql_query = 'SELECT * FROM [dbo].[<tableName>]'
server = '<serverName>.database.windows.net'
database = '<databaseName>'
username = '<userName>'
password = '<password>'
driver = '{ODBC Driver 17 for SQL Server}'
odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;UID='+username+';DATABASE='+ database + ';PWD='+ password
connect_str = 'mssql+pyodbc:///?odbc_connect=' + urllib.parse.quote_plus(odbc_str)
engine = sqlalchemy.create_engine(connect_str)
df = pd.read_sql(sql_query, engine)
print(df)

This will connect to the database successfully without any errors.

enter image description here

Regarding the error below:

(pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Check the location of the odbc.ini and odbcinst.ini files. If the files are in the /usr/local/etc/ location, then copy them to the /etc/ location. Use the commands below to copy the files:

$ cp /usr/local/etc/odbc.ini /etc/odbc.ini  
$ cp /usr/local/etc/odbcinst.ini /etc/odbcinst.ini

This may resolve the issue. For more information, you can refer to this.

answered Nov 13, 2023 at 11:11

Bhavani

5,4762 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wholt Over a year ago

I already did all of that. It still doesn't work. Thank you for your help, though.

wholt · Accepted Answer · 2023-11-13 20:24:23Z

0

I managed to find a workaround for my issue. Thank you for your help everyone!

I wasn't able to get the ODBC drivers to work. I think something is just broken there. I got around the issue by using the PYMSSQL driver instead.

Here's the code

import pymssql

engine_azure = create_engine("mssql+pymssql://username:password@server:port/database")

connection = engine_azure.connect()

To get it working I had to run these commands in the terminal

brew install freetds openssl
export LDFLAGS="-L/opt/homebrew/opt/freetds/lib -L/opt/homebrew/opt/openssl@3/lib"
export CFLAGS="-I/opt/homebrew/opt/freetds/include"
export CPPFLAGS="-I/opt/homebrew/opt/openssl@3/include"
python -m pip install pymssql

edited Nov 13, 2023 at 20:24

answered Nov 13, 2023 at 20:24

wholt

115 bronze badges

Collectives™ on Stack Overflow

Connecting to an Azure database using SQLAlchemy in Python on M1 mac

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related