CODEWITHSUNDEEP
raspberry-piazure-sql-databasepyodbc
Steven Noppe
Steven Noppe

Reputation: 29

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

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

Upvotes: 2

Views: 1340

Answers (2)

Tim Mylott
Tim Mylott

Reputation: 2758

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')

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16431

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: .

Upvotes: 0

Related Questions