Querying Mssql Server 2012 From A Raspberry Pi 3 Using Python, Freetds And Pyodbc

I am trying to query MSSQL server 2012 using Python as my scripting language on a raspberry Pi3. I have the need to create an application that will query MSSQL server and return so

Solution 1:

Tested on Raspberry pi2 & Python 3 with Raspbian & MS Sql server 2008

Make sure your APT-Get library & Python Version is up to date ”

sudo apt-get dist-upgrade
Sudo apt-get install python3

Run following commands to install requirements

sudo apt-get install unixodbc
sudo apt-get install unixodbc-dev
sudo apt-get install freetds-dev
sudo apt-get install tdsodbc
sudo apt-get install freetds-bin 

In terminal, now run :(use 'pip3' because pyodbc wouldn’t install for pip (python 2) due to some errors)

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

Change freeTDS.conf like this

sudonano/etc/freetds/freetds.confAdd a block like this :

[sqlserver]
      host=182.172.2.2# Remote Sql Server's IP addrport=1433# this is defaulttdsversion=7.0# this is by the time i post thisinstance=Test1# your Database name 

Then set up the /etc/odbcinst.ini file as follows:

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

Then setup the /etc/odbc.ini file as follows:

[NAME1]Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Description = MSSQL Server
Trace = NoServer = Server2      # IP or host name of the Sql ServerDatabase = Test1      # DataBase NamePort = 1433# This is defaultTDS_Version = 7.4

Now test the connection with this commands (with second one you should get command line access to Sql server

tsql -S sqlserver -U username
isql NAME1 user'password'

And finally the code part :

import pyodbc
conn = pyodbc.connect('DRIVER={FreeTDS};Server=Server2;PORT=1433;DATABASE=Test1;UID=user;PWD=pass;TDS_Version=7.2;')
cursor= conn.cursor()
cursor.execute("Select * from Table1")
forrowin cursor.fetchall():
    print (row)

Finally if nothing worked try this :

sudo dpkg-reconfigure tdsodbc

Solution 2:

Ill try to connect a RPi 3 to my DB in SQL Server, to capture GPIO status/value.

I already using a web server called Webiopi, and python is used to execute macros, and define GPIO function/Value.

It can be possible?

If(possible == yes)
  {
     happiness = happiness + 10;
     return view(success)
  }
else
  {
     happines = 0;
     return view(keep_searching);
  }

TY

Solution 3:

The problem is with your connection string. Here's a full connection string example for FreeTDS:

conn = pyodbc.connect(
    'DRIVER={FreeTDS};SERVER=yourfqdn.com;PORT=1433;DATABASE=your_db;UID=your_username;PWD=your_pw;TDS_Version=7.4;'
)

Try putting {FreeTDS} in braces and adding the TDS_Version explicitly. I also choose to use the FQDN in my Python connection strings and set username / password as environment variables to keep configuration in one less place.