trying to connect to an azure SQL database with python / pyodbc and a raspberry pi

Question

with following code I try to connect to an azure database :

server = 'tcp:myserver.database.windows.net'
database = 'DBFREE'
username = 'user'
password = 'password'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

but getting following error :

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

I used following commands to install pyodbc :

sudo apt-get install python-pyodbc
sudo apt-get install unixodbc-dev
pip3 install pyodbc

thanx in advance

Please ref the code here:learn.microsoft.com/en-us/azure/azure-sql/database/… — Leon Yue
– Leon Yue, Commented Oct 15, 2020 at 6:56
I spent a good week trying all the same links and articles mentioned here trying to get my Rasberry PIs to connect to an Azure database. They don't work. I think it has something to do with the ARM architecture. So even though Rasberry PI OS is based on Debian and Microsoft supplies the drivers, they don't work. I ended up using freetds.org. — Tim Mylott
– Tim Mylott, Commented Oct 15, 2020 at 13:30

Tim Mylott · Accepted Answer · 2020-10-21 20:19:37Z

1

As mentioned in my comment I recently walked this path and could never get the drivers from Microsoft to work on my Rasberry PIs. I speculate it has something to do with the ARM architecture. So even though Rasberry PI OS is based on Debian and Microsoft supplies the drivers, I could never get them to work.

I ended up using FreeTDS

First, install using the following commands:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
sudo pip install pyodbc sqlalchemy

When that is done, open the following file in a text editor:

/etc/odbcinst.ini

Add the following:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup=/usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so

Then in the python connection string use FreeTDS as the driver and add ;TDS_Version=8.0 to the end of the connection string:

server = 'servername.database.windows.net'
database = 'database'
username = 'user'
password = 'password'
driver = 'FreeTDS'

pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password + ';TDS_Version=8.0')

edited Oct 21, 2020 at 20:19

answered Oct 15, 2020 at 13:50

Tim Mylott

2,8511 gold badge8 silver badges15 bronze badges

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

3 Comments

Steven Noppe Over a year ago

hello, I seemed to get further, thanx. But now I get following error : pyodbc.OperationalError: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)') What would cause this error?

Tim Mylott Over a year ago

@StevenNoppe I remember now that you have to add ";TDS_Version=8.0" to the end of the connection string. Try that, I'll go double check mine and update the answer here.

Steven Noppe Over a year ago

I think I found the problem : I forget to add to which port I needed to connect!

Leon Yue · Accepted Answer · 2020-10-15 07:02:49Z

0

Please ref the pyodbc here: Quickstart: Use Python to query a database in Azure SQL Database or Azure SQL Managed Instance

import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

You didn't set the ODBC driver version and the server don't have prefix tcp: .

edited Oct 15, 2020 at 7:02

answered Oct 15, 2020 at 6:59

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

6 Comments

Steven Noppe Over a year ago

still doesn't work :( : Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

Leon Yue Over a year ago

@StevenNoppe Did you install it? please see: learn.microsoft.com/en-us/sql/connect/python/pyodbc/…

Steven Noppe Over a year ago

yes, but I get an error : "Unable to locate package msodbcsql17" on this line : sudo ACCEPT_EULA=Y apt-get install msodbcsql17

Leon Yue Over a year ago

@StevenNoppe, can you please test also test ODBC 17 and 13? Download here: learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/…

Leon Yue Over a year ago

If you installed the v17 msodbcsql package that was briefly available, you should remove it before installing the msodbcsql17 package. This will avoid conflicts. The msodbcsql17 package can be installed side by side with the msodbcsql v13 package.

|

Collectives™ on Stack Overflow

trying to connect to an azure SQL database with python / pyodbc and a raspberry pi

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related