在Linux / UNIX上用Python连接到Excel数据

用于Excel的CData ODBC驱动程序使您可以在Linux / UNIX计算机上创建Python应用程序，并连接到Excel数据。在Python中利用pyodbc模块进行ODBC。
 

丰富的Python模块生态系统使您可以更快地工作并更有效地集成您的系统。使用适用于Excel的CData Linux / UNIX ODBC驱动程序和pyodbc模块，您可以轻松构建与Excel连接的Python应用程序。本文介绍如何使用pyodbc内置函数连接到Excel数据，执行查询和输出结果。

在UNIX / Linux计算机上使用CData ODBC驱动程序

各种基于Red Hat和Debian的系统支持CData ODBC驱动程序，包括Ubuntu，Debian，RHEL，CentOS和Fedora。还需要几个库和软件包，默认情况下可以安装许多库，具体取决于您的系统。有关受支持的Linux操作系统版本和所需库的更多信息，请参阅帮助文档中的“入门”部分（在线安装和找到）。

安装驱动程序管理器

在安装驱动程序之前，请检查您的系统是否有驱动程序管理器。在本文中，您将使用unixODBC，这是一种广泛支持的免费开源ODBC驱动程序管理器。

对于像Ubuntu这样的基于Debian的系统，您可以使用APT包管理器安装unixODBC：

$ sudo apt-get install unixODBC unixODBC-dev

对于基于Red Hat Linux的系统，您可以使用yum或dnf安装unixODBC：

$ sudo yum install unixODBC unixODBC-devel

unixODBC驱动程序管理器从odbcinst.ini文件中读取有关驱动程序的信息，并从odbc.ini文件中读取有关数据源的信息。您可以通过在终端中输入以下命令来确定系统上配置文件的位置：

$ odbcinst -j

该命令的输出将显示ODBC数据源和已注册的ODBC驱动程序的配置文件的位置。用户数据源只能由odbc.ini所在的主文件夹所在的用户帐户访问。所有用户都可以访问系统数据源。以下是此命令输出的示例：

DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/myuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

安装驱动程序

您可以使用标准包格式下载驱动程序：Debian .deb包格式或.rpm文件格式。下载文件后，您可以从终端安装驱动程序。

驱动程序安装程序使用unixODBC注册驱动程序并创建系统DSN，稍后可在任何支持ODBC连接的工具或应用程序中使用该DSN。

对于像Ubuntu这样的基于Debian的系统，使用sudo或root 运行以下命令：

$ dpkg -i /path/to/package.deb

对于Red Hat系统和支持.rpms的其他系统，请使用sudo或root用户运行以下命令：

$ rpm -i /path/to/package.rpm

安装驱动程序后，您可以使用unixODBC驱动程序管理器列出已注册的驱动程序和已定义的数据源：

列出已注册的司机

$ odbcinst -q -d
CData ODBC Driver for Excel
...

列出定义的数据源

$ odbcinst -q -s
CData Excel Source
...

要使用带有unixODBC的CData ODBC驱动程序for Excel，请确保将驱动程序配置为使用UTF-16。为此，请编辑驱动程序的INI文件（cdata.odbc.excel.ini），该文件位于安装位置的l​​ib文件夹中（通常为/ opt / cdata / cdata-odbc-driver-for-excel） ， 如下：

cdata.odbc.excel.ini

...
 
[Driver]
DriverManagerEncoding = UTF-16

修改DSN

驱动程序安装预定义了系统DSN。您可以通过编辑系统数据源文件（/etc/odbc.ini）并定义所需的连接属性来修改DSN。此外，您可以创建不需要root访问权限的用户特定DSN，以便在$ HOME / .odbc.ini中进行修改。

必须将“身份验证”部分下的ExcelFile设置为有效的Excel文件。

 

/etc/odbc.ini或$ HOME / .odbc.ini

[CData Excel Source]
Driver = CData ODBC Driver for Excel
Description = My Description
Excel File = 'C:\MyExcelWorkbooks\SampleWorkbook.xlsx'

有关使用这些配置文件的具体信息，请参阅帮助文档（在线安装和找到）。

您可以按照以下步骤安装pyodbc并开始通过Python对象访问Excel。

安装pyodbc

您可以使用pip实用程序来安装模块：

pip install pyodbc

请务必使用以下内容导入模块：

import pyodbc

用Python连接到Excel数据

您现在可以使用ODBC连接字符串或DSN进行连接。以下是连接字符串的语法：

cnxn = pyodbc.connect('DRIVER={CData ODBC Driver for Excel};Excel File='C:\MyExcelWorkbooks\SampleWorkbook.xlsx';')

以下是DSN的语法：

cnxn = pyodbc.connect('DSN=CData Excel Sys;')

执行SQL到Excel

实例化Cursor并使用Cursor类的execute方法执行任何SQL语句。 

cursor = cnxn.cursor()

选择

您可以使用fetchall，fetchone和fetchmany来检索从SELECT语句返回的行：

import pyodbc
 
cursor = cnxn.cursor()
cnxn = pyodbc.connect('DSN=CData Excel Source;User=MyUser;Password=MyPassword')
cursor.execute("SELECT Name, Revenue FROM Sheet WHERE Name = 'Bob'")
rows = cursor.fetchall()
for row in rows:
print(row.Name, row.Revenue)

您可以在序列或参数列表中提供参数化查询：

cursor.execute(
"SELECT Name, Revenue
FROM Sheet
WHERE Name = ?", 'Bob',1)

插入

INSERT命令也使用execute方法; 但是，您必须在插入后调用commit方法，否则您将丢失更改：

cursor.execute("INSERT INTO Sheet (Name) VALUES ('Bob')")
cnxn.commit()

更新和删除

与插入一样，在调用execute进行更新或删除之后，还必须调用commit： 

cursor.execute("UPDATE Sheet SET Name = 'Bob'")
cnxn.commit()

元数据发现

您可以使用getinfo方法检索数据，例如有关数据源和驱动程序功能的信息。该程序getinfo方法通过输入到ODBC 的SQLGetInfo方法。

cnxn.getinfo(pyodbc.SQL_DATA_SOURCE_NAME)

现在，您已准备好在Linux / UNIX环境中使用CData ODBC Driver for Excel连接到Excel数据来构建Python应用程序。

参考网站：https://www.cdata.com/kb/tech/excel-odbc-python-linux.rst