jgp
Posts: 7
Joined: Tue Dec 27, 2011 1:56 pm

pyodbc module

Fri Aug 31, 2012 11:48 am

I noticed that this module is not available by default on Raspbian. Before I go through the effort of figuring out how to get it installed and working I figured it would be a smart idea to ask a question. What is the recommended path to take to do database programming in Python on the Raspberry Pi? I'm connecting primarily to MS SQL Server at the moment but I could easily see PostgreSQL in my future.

Any help or hints would be greatly appreciated.

BlackJack
Posts: 288
Joined: Sat Aug 04, 2012 8:28 am
Contact: Website

Re: pyodbc module

Fri Aug 31, 2012 12:24 pm

@jgp: Python has a PEP for a DB API and for most relational DBMS there is at least one module adhering to that standard. Raspbian has a `python-pymssql` package. But I also see a `python-pyodbc` package in aptitude‽

I like SQLAlchemy as a layer over the concrete DB API modules which helps smoothing out differences between DBMS and building queries programatically and has an ORM build on top of that.

Code: Select all

while not self.asleep():
    sheep += 1

jgp
Posts: 7
Joined: Tue Dec 27, 2011 1:56 pm

Re: pyodbc module

Fri Aug 31, 2012 12:30 pm

Thank you. I didn't think to check for a package named 'python-pyodbc'. I have a database wrapper module that depends on pyodbc so I wanted to start with that. I've played with SQLAlchemy before and will again at some point.

Thanks again.

SoupMan
Posts: 4
Joined: Thu Jun 21, 2012 2:28 pm

Re: pyodbc module

Fri Sep 07, 2012 6:38 pm

I am also having a problem with the python odbc module. I have MS SQL Server database on a Windows machine attached to my network that I would like to run queries against. I have loaded the python odbc module but cannot get it to connect. Have you or anyone else you know been able to actually use this module on the PI.

Please see my prior post for details on my attempt.

Thnks.

nexusdb
Posts: 4
Joined: Thu Jan 24, 2013 10:47 pm

Re: pyodbc module

Tue Mar 19, 2013 8:01 pm

Did you ever resolve your issues with the pyodbc and connecting to a MS SQL database as I have what appears to be the same problem.
pyodbc works lovely on a windows machine but the same setup doesn’t work on the PI it fails to connect.

hgestel
Posts: 5
Joined: Mon Nov 19, 2012 2:42 pm

Re: pyodbc module

Wed Mar 27, 2013 9:50 pm

Hallo,

has anybody python3 pyodbc 3.02 installed?
I can not find it.

python py odbc is available but not for python3 , did I overlook something?
thks

hgestel
Posts: 5
Joined: Mon Nov 19, 2012 2:42 pm

Re: pyodbc module

Thu Mar 28, 2013 5:11 pm

or
has anybody a test routine for raspberry pi to test a pyodbc connection to mysql?

thanks

Nigel Day
Posts: 22
Joined: Sat Jun 16, 2012 2:15 pm

Re: pyodbc module

Mon Apr 22, 2013 6:31 pm

hgestel wrote:Hallo,

has anybody python3 pyodbc 3.02 installed?
I can not find it.

python py odbc is available but not for python3 , did I overlook something?
thks
Both python2 and python3 are installed by default on the Raspian wheezy distro, and as you point out you 'sudo apt-get install python-pyodbc' you get the version for python2. To install for Python3, you have to download and build it for yourself - but that is pretty easy: first you ensure the python 3 development kit, the unixODBC development kit and git are installed, and then you grab a copy of the source code and build it, and finally you install it:

Code: Select all

sudo apt-get install python3-dev unixodbc-dev git
git clone https://code.google.com/p/pyodbc/
cd pyodbc
python3 setup.py build
sudo python3 setup.py build
To prove that it works and that you can have pyodbc installed for both python2 and python3, here is a little test program, and the results of running it against a Polyhedra Lite database using both versions of Polyhedra:

Code: Select all

[email protected] ~ $ cat query.py
import pyodbc
cnxn = pyodbc.connect('DSN=8001')
cursor = cnxn.cursor()
for row in cursor.execute("select code, usdollar from currency"):
    print ('Currency code', row.code, '- 1 US Dollar buys', row.usdollar)
[email protected] ~ $ 

Code: Select all

[email protected] ~ $ python query.py
('Currency code', 'GBP', '- 1 US Dollar buys', 0.6767000000000001)
('Currency code', 'EUR', '- 1 US Dollar buys', 0.8181)
('Currency code', 'CHF', '- 1 US Dollar buys', 1.6866999999999999)
('Currency code', 'CAD', '- 1 US Dollar buys', 1.5049)
('Currency code', 'AUD', '- 1 US Dollar buys', 1.7372)
('Currency code', 'JPY', '- 1 US Dollar buys', 110.595)
[email protected] ~ $ 

Code: Select all

[email protected] ~ $ python3 query.py
Currency code GBP - 1 US Dollar buys 0.6767000000000001
Currency code EUR - 1 US Dollar buys 0.8181
Currency code CHF - 1 US Dollar buys 1.6866999999999999
Currency code CAD - 1 US Dollar buys 1.5049
Currency code AUD - 1 US Dollar buys 1.7372
Currency code JPY - 1 US Dollar buys 110.595
[email protected] ~ $
A quick inspection shows that the database results are the same (no other client was updating this table in the background), but the output looks different due to the differences between the the two versions of the language.

Nigel Day
Posts: 22
Joined: Sat Jun 16, 2012 2:15 pm

Re: pyodbc module

Tue Apr 23, 2013 8:51 am

oops... cut & paste error; the last line of my build instructions for pyodbc for python3 should have said 'install', not 'build', viz:

Code: Select all

sudo python3 setup.py install
Of course, you could combine the build and install into a single step - but as a matter of habit it is always best to minimise the work that is done using root privileges.

jailolous
Posts: 1
Joined: Thu Sep 29, 2016 8:59 am

Re: pyodbc module

Thu Sep 29, 2016 11:17 am

Thanks, worked for me.
But Google code is shutting down so you have to clone from github https://github.com/mkleehammer/pyodbc
The code will be:

Code: Select all

sudo apt-get install python3-dev unixodbc-dev git
git clone https://github.com/mkleehammer/pyodbc
cd pyodbc
python3 setup.py build
sudo python3 setup.py install

Return to “Python”