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

yiytaume 于 2023-11-16 发布在 Python

关注(0)|答案(2)|浏览(146)

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!

sql-server

来源：https://stackoverflow.com/questions/77466189/connecting-to-an-azure-database-using-sqlalchemy-in-python-on-m1-mac

2条答案

按热度按时间

nsc4cvqm1#

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.

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 .

赞(0）回复(0）举报 2023-11-16

eufgjt7s2#

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

赞(0）回复(0）举报 2023-11-16

我来回答

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

2条答案

相关问题

热门标签

最新问答