Querying MSSQL server 2012 from a Raspberry Pi 3 using Python, FreeTDS and pyodbc
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
sudo nano /etc/freetds/freetds.conf
Add a block like this :
[sqlserver]
host = 182.172.2.2 # Remote Sql Server's IP addr
port = 1433 # this is default
tds version = 7.0 # this is by the time i post this
instance = 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 = No
Server = Server2 # IP or host name of the Sql Server
Database = Test1 # DataBase Name
Port = 1433 # This is default
TDS_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")
for row in 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
Alfalaval
Updated on August 21, 2022Comments
-
Alfalaval over 1 year
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 some values that need to be displayed on a H.M.I. I chose the Raspberry Pi platform to develop this solution using Python as the programming language. I created the script using PyCharm on a Windows 7 PC and all worked well. When I moved it to the Raspberry Platform it didn't work.
I am using pyODBC to do the connecting and querying and FreeTDS as the driver. I used the following procedure to set this up:
sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc pip3 install pyODBC
Configured the /etc/freetds.conf file as follows
[NAME] host = ipAddress port = 1433 tds version = 7.4 instance = dbInstanceName
I then went to the command line and tested the connection with:
tsql -S NAME -U username
. The command line then prompts withPassword:
so I typed the password in and I got the following:locale is "enGB.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
As there are no errors present I can only assume that this has worked?
I then set up the /etc/odbcinst.ini file as follows:
[FreeTDS] Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
I then setup the /etc/odbc.ini file as follows:
[NAME1] Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so Description = MSSQL Server Trace = No Server = ipAddress Database = dbName Port = 1433 TDS_Version = 7.4
I then tested this with the isql function in the command line:
isql NAME1 user password
and I got the following prompt:+-------------------------------------------------+ | Connected! | | sql-statement | help [tablename] | quit | +-------------------------------------------------+ SQL>
so I typed in
select getDate()
and the date and time returned.However within Python I still can't get a connection, I typed the following into the interpreter:
import pyodbc conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=NAME;DATABASE=dbName;UID=user;PWD=password')
Then when I type this
cu = conn.cursor()
I get an error:AttributeError: 'NoneType' object has no attribute cursor