Querying MSSQL server 2012 from a Raspberry Pi 3 using Python, FreeTDS and pyodbc

Question

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 with Password: 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

Did you set the NAME, dbName, user and password values for your connection to be successful? Hint: gist.github.com/rduplain/1293636#file-hello_pyodbc-py — BoboDarph
– BoboDarph, Commented Jul 7, 2017 at 12:04
Also please note that the connect function gets a single string as parameter. — BoboDarph
– BoboDarph, Commented Jul 7, 2017 at 12:10
Hi BoboDarph,Thank you for your reply, I did set the user and password to the real values. — Alfalaval
– Alfalaval, Commented Jul 7, 2017 at 12:51

Catalin · Accepted Answer · 2020-07-25 16:12:35Z

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

aarset89 · Accepted Answer · 2018-08-22 16:28:47Z

1

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

answered Aug 22, 2018 at 16:28

aarset89

791 silver badge4 bronze badges

Comments

FlipperPA · Accepted Answer · 2017-07-07 13:22:22Z

0

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.

answered Jul 7, 2017 at 13:22

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

6 Comments

Alfalaval Over a year ago

Hi FlipperPA, Thank you for your reply. I have now changed my connection string to 'DRIVER={FreeTDS};SERVER=ipAddress;PORT=1433;DATABASE=dbName;UID=user;PWD=password;TDS_Version=7.4;' but unfortunately it still won't connect!

FlipperPA Over a year ago

I'm betting you don't have FreeTDS 1.0, which was the first to support TDS_Version=7.4. Can you try TDS_Version=7.1 instead, and if it works, try 7.2 and 7.3? If you let me know what version of FreeTDS you're running (with tsql -C) I can tell you for sure what the maximum supported TDS version is.

Alfalaval Over a year ago

Hi FlipperPA, Thank you for your reply. You are right I have version 0.91 installed. There is another line which reads TDS Version 4.2

Alfalaval Over a year ago

One other thing I have the ipAdress explicitly declared as SERVER=192.168.4.212 does this need to be in braces?

FlipperPA Over a year ago

Only the DRIVER={Driver} needs to be in braces. Version 0.91 has a maximum supported TDS_Version of 7.2. I have a PR in for this, but the documentation hasn't been updated yet: github.com/FreeTDS/freetds/pull/71/files The TDS Version 4.2 is from the [global] settings in freetds.conf. Passing TDS_Version=7.2 in the pyodbc.connect() string should override this.

|

Collectives™ on Stack Overflow

Querying MSSQL server 2012 from a Raspberry Pi 3 using Python, FreeTDS and pyodbc

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related