树莓派上python访问数据库

墨蓝 2022-08-09 13:39 215阅读 0赞

本文档主要描述了Linux下python数据库驱动的安装和配置，用来实现在Linux平台下通过python访问MySQL、Oracle、SQL Server数据库。其中包括以下几个软件的安装及配置：  
        unixODBC  
        FreeTDS  
        pyodbc

cx\_Oracle

## 一、前提概述 ##

pyodbc是一个Python模块，能够让python使用 ODBC 连接来自Windows, Linux, OS/X等系统中的大部分数据库。pyodbc依赖于unixODBC和FreeTDS，因此需要先安装unixODBC和FreeTDS。（FreeTDS是一个开源的C程序库，它可以实现在Linux系统下访问操作SQL Server数据库）

cx\_Oracle 是一个用来连接并操作 Oracle 数据库的 Python 扩展模块， 支持包括 Oracle 9.2 10.2 以及 11.1 等版本。

## 二、驱动安装 ##

### 1. unixODBC安装 ###

a.下载unixODBC安装包：unixODBC-2.3.2.tar.gz

下载地址：http://www.linuxfromscratch.org/blfs/view/cvs/general/unixodbc.html

b.解压：tar -zxf unixODBC-2.3.2.tar.gz

c.安装：cd unixODBC-2.3.2

./configure --enable-gui=no

make

make install

NOTES：

编译安装三个步骤之间，可通过echo $?查看退出状态，为0则正常，可进行下一步操作。（MYSQL驱动可安装MySQL-python.x86\_64)

### 2. FreeTds安装 ###

下载FreeTds安装包：freetds-stable.tgz

下载地址：http://www.freetds.org/

tar -zxvf freetds-stable.tgz

cd freetds-0.91

./configure --prefix=/etc/freetds --with-tdsver=7.0 --enable-msdblib --with-gnu-ld  --enable-shared --enable-static

make

make install

同样可通过echo $?查看退出状态，安装完后可查看freeTds相关版本及安装信息：

cd src/apps/

./tsql -C

<table style="color:rgb(0,0,0); word-wrap:break-word; width:426.1pt; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-width:initial; border-color:initial"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:black; border-right-color:black; border-bottom-color:black; border-left-color:black; border-top-width:1pt; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt"> [root@localhost apps]# ./tsql -C&nbsp; Compile-time settings (established with the "configure" script) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Version: freetds v0.91 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; freetds.conf directory: /usr/local/etc &nbsp;&nbsp;&nbsp;&nbsp; MS db-lib source compatibility: no &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sybase binary compatibility: no &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thread safety: yes &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; iconv library: yes &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TDS version: 7.1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; iODBC: no &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; unixodbc: yes &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SSPI "trusted" logins: no &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Kerberos: no </td> 
 </tr> 
 </tbody> 
</table>

### 3. pyodbc安装 ###

下载pyodbc-3.0.6.zip

下载地址：http://www.oschina.net/p/pyodbc

unzip pyodbc-3.0.6.zip

cd pyodbc-3.0.6

python setup.py build

python setup.py install

NOTES：安装时build 遇到以下错误，而且gcc相关包已经安装完全

查看python版本，发现其为python 2.4，通过官网下载页面信息对比，可知pyodbc-3.0.6不支持python 2.4，重新下载pyodbc-2.1.7安装即可。

### 4. cx\_Oracle安装 ###

4.1. 下载：cx\_oracle：cx\_Oracle-5.1.2-10g-py27-1.x86\_64.rpm

http://sourceforge.net/projects/cx-oracle/files/

NOTES：

需要下载对应版本的驱动

4.2  oracle instant client 安装：

gunzip 10201\_client\_linux\_x86\_64.cpio.gz

cpio -idmv < 10201\_client\_linux\_x86\_64.cpio

在~/.bash\_profile中添加oracle相关配置：

export ORACLE\_BASE=/opt/oracle

export ORACLE\_HOME=$ORACLE\_BASE/client\_10g

export LD\_LIBRARY\_PATH=$ORACLE\_HOME:$LD\_LIBRARY\_PATH

建立oracle用户和文件目录

groupadd dba

useradd -g dba oracle

su - oracle

mkdir -p /opt/oracle/client\_10g

配置.bash\_profile

回到root用户：

mv client /home/oracle/

\[root@localhost software\]\# chown -R oracle:dba /home/oracle/client

su - oracle 进行安装：

cd /home/oracle/client/response

vi instantClient.rsp

修改以下参数：

UNIX\_GROUP\_NAME=dba

ORACLE\_HOME=/opt/oracle/client\_10g

ORACLE\_HOME\_NAME=OraClient10ghome1

cd ../

./runInstaller -silent -noconfig -responseFile /home/oracle/client/response/instantClient.rsp

python setup.py build

python setup.py install

4.3 安装cx\_Oracle：

rpm -ivh cx\_oracle：cx\_Oracle-5.1.2-10g-py27-1.x86\_64.rpm

## 三、驱动配置 ##

1. 配置freetds：

vi /etc/freetds/etc/freetds.conf

text size = 10240000

client charset = UTF-8

2. 配置freetds驱动：

vi freetds.ini

\[FreeTDS\]

Description     = 0.91 with protocol v7.0

Driver          = /etc/freetds/lib/libtdsodbc.so

odbcinst -i -d -f freetds.ini

3. 配置数据源：

vi db.ini

\[SQLSERVER\]

Driver = FreeTDS

Description = SQLSERVER

Trace   =No

Server  =192.168.16.13

Port    =1433

Database=master

\#此处需要配置SQL Server数据的连接信息

odbcinst -i -s -f db.ini

cat /root/.odbc.ini

\[SQLSERVER\]

Driver=FreeTDS

Description=SQLSERVER

Trace=No

Server=192.168.16.13

Port=1433

Database=master

NOTES：

若见到以下错误：

<table style="color:rgb(0,0,0); word-wrap:break-word; width:404.9pt; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-width:initial; border-color:initial"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:black; border-right-color:black; border-bottom-color:black; border-left-color:black; border-top-width:1pt; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt"> import cx_Oracle Traceback (most recent call last): &nbsp; File "", line 1, in ? ImportError:&nbsp;libclntsh.so.10.1: cannot open shared object file: No such file or directory &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

则需要找到对应的文件建立软链接，如：

ln -s /opt/oracle/db\_10g/lib/libclntsh.so.10.1 /usr/lib64/libclntsh.so.10.1

## 四、连接测试 ##

1. 前提说明：

本处提供的连接测试，仅仅是一个简单的连通并发起查询，以证明驱动的正确安装和配置。

可先进行import 查看驱动安装是否有误：

若驱动正确安装，执行import操作就不会报错。

2. MySQL连接测试：

3.Oracle连接测试：

<table style="color:rgb(0,0,0); word-wrap:break-word; border-top-style:none; border-right-style:none; border-bottom-style:none; border-left-style:none; border-width:initial; border-color:initial"> 
 <tbody style="word-wrap:break-word"> 
 <tr style="word-wrap:break-word"> 
 <td style="word-wrap:break-word; margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:black; border-right-color:black; border-bottom-color:black; border-left-color:black; border-top-width:1pt; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt"> [root@localhost software]#&nbsp;cat test.py import time import cx_Oracle sql2="select * from tab"; connstr="system/oracle@192.168.43.130:1521/orcl" conn=cx_Oracle.connect(connstr) cur=conn.cursor() aa=cur.execute(sql2) print cur.fetchone() print cur.rowcount print "connection string is&nbsp;&nbsp; : ",conn.dsn print "the database version is: ",conn.version &nbsp; [root@localhost software]#&nbsp;python test.py ('SYSCATALOG', 'SYNONYM', None) 1 connection string is&nbsp;&nbsp; :&nbsp; 192.168.43.130:1521/orcl the database version is:&nbsp; 10.2.0.1.0 </td> 
 </tr> 
 </tbody> 
</table>

4.SQL Server连接测试