Data access from Raspberry Pi

Easysoft ODBC drivers enable your Pi applications to connect to both local and remote databases. For example, you could use the SQL Server ODBC driver to connect Python on Pi to SQL Server on Windows (or in the Azure Cloud, or on Linux, if you use these platforms). The steps for this are as follows:

  1. Download the SQL Server ODBC driver for Raspberry Pi.
  2. Install and license the SQL Server ODBC driver on the Raspberry Pi machine.

    For installation instructions, refer to the ODBC driver documentation.

    Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

  3. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Python. For example: 
    [SQLSERVER_SAMPLE]
Driver          = Easysoft ODBC-SQL Server
Server          = my_machine\SQLEXPRESS
User            = my_domain\my_user
Password        = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database        = Northwind
  4. Use isql to test the new data source. For example: 
    cd /usr/local/easysoft/unixODBC/bin
./isql.sh -v SQLSERVER_SAMPLE

    At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

    If you are unable to connect, refer to this article and the SQL Server ODBC driver knowledge Base for assistance.

  5. Now install pyodbc, the ODBC interface that enables you to access an ODBC database from Python: 
    sudo apt-get install python-pyodbc
  6. To test the installation, fetch some SQL Server data from a Python shell: 
    pi@raspberrypi:~ $ python
Python 2.7.13 (default, Jan 19 2017, 14:48:08)
[GCC 6.3.0 20170124] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect("DSN=SQLSERVER_SAMPLE")
>>> cursor = cnxn.cursor()
>>> cursor.tables()
<pyodbc.Cursor object at 0x76a168a8>
>>> rows = cursor.fetchall()
>>> for row in rows:
...      print row.table_name
...
sysmatrixageforget
GEMS_DEPENDENTS_STAGING2
GEMS_DEPENDENTS_STAGING2
MSreplication_options
oinsert
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
>>>

Alternatively, if you prefer Perl:

pi@raspberrypi:~ $ sudo apt-get install libdbi-perl
	pi@raspberrypi:~ $ wget http://search.cpan.org/CPAN/authors/id/M/MJ/MJEVANS/DBD-ODBC-1.56.tar.gz
pi@raspberrypi:~ $ tar -xvf DBD-ODBC-1.56.tar.gz
pi@raspberrypi:~ $ cd DBD-ODBC-1.56
pi@raspberrypi:~ $ export DBI_DSN='dbi:ODBC:SQLSERVER_SAMPLE'
pi@raspberrypi:~ $ DBI_USER='mydb_user'
pi@raspberrypi:~ $ DBI_PASS='mydb_password'
pi@raspberrypi:~ $ perl Makefile.PL
pi@raspberrypi:~ $ make
pi@raspberrypi:~ $ make test
pi@raspberrypi:~ $ sudo make install
pi@raspberrypi:~ $ vi perl-test.pl
#!/usr/bin/perl -w

use strict;

use DBI;

my $dbh = DBI->connect( "dbi:ODBC:SQLSERVER_SAMPLE", "mydb_user", "mydb_password" , {
    RaiseError => 1
} );

my $sth = $dbh->table_info();

while ( my ( $qualifier, $owner, $name, $type, $remarks ) = 
        $sth->fetchrow_array() ) {

    foreach ($qualifier, $owner, $name, $type, $remarks) {
        $_ = '' unless defined $_;
    }
    print "$qualifier, $owner, $name, $type, $remarks \n";

}

exit;
pi@raspberrypi:~ $ perl ./perl-test.pl
master, dbo, sysmatrixageforget, SYSTEM TABLE,
master, dbo,  GEMS_DEPENDENTS_STAGING2, TABLE,
master, dbo, DBD_ODBC_LOB_TEST, TABLE,

Easysoft drivers support both the ARMv71 (32-bit) and AArch64 (64-bit) Raspberry Pi platforms.