MSSQL 2012 server query from Raspberry Pi 3 using Python, FreeTDS and pyobbc
I am trying to query MSSQL Server 2012 using Python as my scripting language on a Raspberry Pi3.
I need to create an application that will query the MSSQL server and return some values that should be displayed on the HMI. I chose the Raspberry Pi platform to develop this solution using Python as the programming language. I created a script using PyCharm on a Windows 7 PC and everything worked well. When I moved it to the Raspberry platform it didn't work.
I am using pyODBC to connect and query and FreeTDS as driver. For this, I used the following procedure:
sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip3 install pyODBC
Configure the /etc/freetds.conf file as follows
[NAME]
host = ipAddress
port = 1433
tds version = 7.4
instance = dbInstanceName
Then I went to the command line and test the connection with: tsql -S NAME -U username
. Then a message appears on the command line Password:
, so I entered the password and got this:
locale is "enGB.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
Since there are no errors, can I only assume it worked?
Then I create the /etc/odbcinst.ini file like this:
[FreeTDS]
Driver = /usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Then I install the /etc/odbc.ini file like this:
[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
Then I tested this using the isql function on the command line: isql NAME1 user password
and got the following message:
+-------------------------------------------------+
| Connected!
|
| sql-statement
| help [tablename]
| quit
|
+-------------------------------------------------+
SQL>
so I typed in select getDate()
and the date and time came back.
However, in Python I still can't get the connection, I entered 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
source to share
Tested on Raspberry pi2 and Python 3 with Raspbian server and MS Sql 2008
Make sure your APT-Get library and Python version are up to date.
sudo apt-get dist-upgrade
Sudo apt-get install python3
Run the following commands to set 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 execute: (use 'pip3' because pyodbc won't be able to install for pip (python 2) due to some errors)
sudo pip3 install pyodbc
sudo apt-get install python-pyodbc
Modify freeTDS.conf as
sudo nano /etc/freetds/freetds.conf
Add a block like this :
[sqlserver]
host = 182.172.2.2 # Remote Sql Server 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 configure 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 configure 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 check the connection with these commands (with the second you should be able to access the command line to the Sql server
tsql -S sqlserver -U username
isql NAME1 user 'password'
And finally, a piece of code:
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 else works, try this:
sudo dpkg-reconfigure tdsodbc
source to share
The problem is with your connection string. Here's an example of a complete connection string 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 to put {FreeTDS}
in curly braces and add explicitly TDS_Version
. I also want to use the FQDN in my Python connection strings and set the username / password as environment variables to keep the configuration in one place.
source to share