Question 284832 in Azure Question posted in
The official documentation to get you started can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search