iopro.pyodbc 第一步¶

iopro.pyodbc 使用允许将数据直接提取到 numpy 容器中的方法扩展了 pyodbc。这些函数比 pyodbc 中的常规 fetch 调用更快，还提供了在适合快速分析的容器中返回的便利。

本笔记本旨在作为 iopro.pyodbc 的教程。大多数材料适用于pyodbc（并基于pyodbc教程）。将有一些特定于 iopro.pyodbc 的示例。在这种情况下，它会被注意到。

概念¶

在pyodbc中有两个主要的类需要理解：

联系
光标

顾名思义，连接就是到数据源的连接。数据源是您的数据库。它可能是由 DBMS 处理的数据库，也可能只是一个普通文件。游标允许您与语句交互。与查询和其他命令的交互是通过游标执行的。游标与连接相关联，并且在游标上的命令通过该连接执行到数据源。为了使用 iopro.pyodbc，您必须导入它：

>>> import iopro.pyodbc as pyodbc

连接到数据源¶

为了使用 pyodbc 进行操作，您需要连接到数据源。通常这将是一个数据库。这是通过创建连接对象来完成的。要创建连接对象，您需要一个连接字符串。此字符串描述要使用的数据源以及一些额外的参数。您可以在此处了解有关连接字符串的更多信息。：

>>> connection_string = '''DSN=SQLServerTest;DATABASE=Test'''
>>> connection = pyodbc.connect(connection_string)

pyodbc.connect 支持关键字参数 autocommit。这控制了连接的处理方式。默认值 (False) 表示修改数据库语句的命令需要显式提交。提交之间的所有命令将形成单个事务。如果启用了自动提交，则将发出并提交每个命令。也可以在建立连接后更改自动提交状态。：

>>> connection.autocommit = True #enable autocommit
>>> connection.autocommit = False # disable autocommit

当不处于自动提交模式时，您可以通过提交或回滚它来结束事务。：

In[6]: connection.commit() # commit the transaction
In[7]: connection.rollback() # rollback the transaction

请注意，提交/回滚始终在连接级别执行。pyodbc 在游标对象中提供了提交/回滚方法，但它们将作用于关联的连接。

使用游标¶

pyodbc 中的命令执行是通过游标处理的。您可以使用 cursor() 方法从连接创建游标。第一步是创建一个游标：

In[8]: cursor = connection.cursor()

创建游标后，我们可以开始使用 execute 方法发出 SQL 命令。

创建示例表¶

首先，在数据库中创建一个示例表。以下代码将创建一个包含三列不同类型的示例表。：

>>> def create_test_table(cursor):
...    try:
...        cursor.execute('drop table test_table')
...    except:
...        pass
...    cursor.execute('''create table test_table (
...                                    name varchar(10),
...                                    fval float(24),
...                                    ival int)''')
...    cursor.commit()

>>> create_test_table(cursor)

用样本数据填充样本表¶

创建表后，可以通过执行插入表来插入行。请注意，您可以通过放置 ? 进入 SQL 语句。将采用这些参数，以便序列出现在下一个参数中。：

>>> cursor.execute('''insert into test_table values (?,?,?)''', ('foo', 3.0, 2))
>>> cursor.rowcount
1

使用 executemany 可以将一系列参数传递给 SQL 语句，并且该语句将执行多次，每次使用不同的参数集。这使我们可以轻松地将多行插入到数据库中，这样我们就有了一个小的测试集：

>>> cursor.executemany('''insert into test_table values (?,?,?)''', [
...                        ('several', 2.1, 3),
...                        ('tuples', -1.0, 2),
...                        ('can', 3.0, 1),
...                        ('be', 12.0, -3),
...                        ('inserted', 0.0, -2),
...                        ('at', 33.0, 0),
...                        ('once', 0.0, 0)
...                        ])

请记住，如果关闭自动提交，除非我们提交，否则任何其他连接都不会看到更改：

>>> cursor.commit() # remember this is a shortcut to connection.commit() method

从样本表中查询样本数据¶

填充我们的示例数据库后，我们可以通过执行 select 语句来检索插入的数据：

>>> cursor.execute('''select * from test_table''')
<pyodbc.Cursor at 0x6803510>

使用 select 语句调用 execute 后，我们需要检索数据。这可以通过调用游标中的 fetch 方法来实现 fetchone 获取游标中的下一行，并在元组中返回它：

>>> cursor.fetchone()
('foo', 3.0, 2)

fetchmany 在元组列表中一次检索多行：

>>> cursor.fetchmany(3)
[('several', 2.0999999046325684, 3), ('tuples', -1.0, 2), ('can', 3.0, 1)]

fetchall 检索元组列表中的所有剩余行：

>>> cursor.fetchall()
[('be', 12.0, -3), ('inserted', 0.0, -2), ('at', 33.0, 0), ('once', 0.0, 0)]

对任何类型的提取的所有调用都会使游标前进，因此下一次提取在提取的最后一行之后的行中开始。执行返回游标对象。这对于通过链接 fetchall 检索完整查询非常方便。这导致一个单行::

>>> cursor.execute('''select * from test_table''').fetchall()
[('foo', 3.0, 2),
 ('several', 2.0999999046325684, 3),
 ('tuples', -1.0, 2),
 ('can', 3.0, 1),
 ('be', 12.0, -3),
 ('inserted', 0.0, -2),
 ('at', 33.0, 0),
 ('once', 0.0, 0)]

iopro.pyodbc 扩展¶

使用 iopro.pyodbc 时，可以将查询结果直接检索到 numpy 容器中。这是通过使用新的游标方法 fetchdictarray 和 fetchsarray 来实现的。

fetchdictarray ¶

fetchdictarray 在字典中获取查询结果。默认情况下，fetchdictarray 获取游标中所有剩余的行。：

>>> cursor.execute('''select * from test_table''')
>>> dictarray = cursor.fetchdictarray()
>>> type(dictarray)
dict

字典中的键是列名：

>>> dictarray.keys()
['ival', 'name', 'fval']

每个列名都映射到一个 numpy 数组（ndarray）作为其值：

>>> ', '.join([type(dictarray[i]).__name__ for i in dictarray.keys()])
'ndarray, ndarray, ndarray'

numpy 数组的类型是从数据库列信息中推断出来的。所以对于我们的列，我们得到了一个合适的 numpy 类型。请注意，在 name 的情况下，即使在 test_table 中定义为 varchar(10)，类型也是 11 个字符的字符串。额外的参数用于空终止字符串::

>>> ', '.join([repr(dictarray[i].dtype) for i in dictarray.keys()])
"dtype('int32'), dtype('|S11'), dtype('float32')"

numpy 数组的形状包含一个维度，其中包含获取的行数：

>>> ', '.join([repr(dictarray[i].shape) for i in dictarray.keys()])
'(8L,), (8L,), (8L,)'

不同列数组中的值是索引一致的。因此，为了获得与给定行相关联的值，使用适当的索引访问每一列就足够了。以下代码段显示了这种对应关系::

>>> print '\n'.join(
... [', '.join(
...     [repr(dictarray[i][j]) for i in dictarray.keys()])
...         for j in range(dictarray['name'].shape[0])])
2, 'foo', 3.0
3, 'several', 2.0999999
2, 'tuples', -1.0
1, 'can', 3.0
-3, 'be', 12.0
-2, 'inserted', 0.0
0, 'at', 33.0
0, 'once', 0.0

将结果放在 numpy 容器中可以很容易地使用 numpy 来分析数据：

>>> import numpy as np
>>> np.mean(dictarray['fval'])
6.5124998092651367

fetchdictarray 接受一个可选参数，该参数设置要获取的行数的上限。如果游标中没有足够的元素可供提取，则生成的数组将相应调整大小。这样就可以在大块的行中处理大表。：

>>> cursor.execute('''select * from test_table''')
>>> dictarray = cursor.fetchdictarray(6)
>>> print dictarray['name'].shape
(6L,)
>>> dictarray = cursor.fetchdictarray(6)
>>> print dictarray['name'].shape
(2L,)

fetchsarray ¶

fetchsarray 在 numpy 结构化数组中获取查询结果。：

>>> cursor.execute('''select * from test_table''')
>>> sarray = cursor.fetchsarray()
>>> print sarray
[('foo', 3.0, 2) ('several', 2.0999999046325684, 3) ('tuples', -1.0, 2)
 ('can', 3.0, 1) ('be', 12.0, -3) ('inserted', 0.0, -2) ('at', 33.0, 0)
 ('once', 0.0, 0)]

结果的类型是一个numpy数组（ndarray）::

>>> type(sarray)
numpy.ndarray

numpy 数组的 dtype 包含列及其类型的描述：

>>> sarray.dtype
dtype([('name', '|S11'), ('fval', '&lt;f4'), ('ival', '&lt;i4')])

数组的形状将是一维的，基数等于获取的行数：

>>> sarray.shape
(8L,)

也可以得到圆柱的形状。通过这种方式，它看起来类似于使用 dictarray 时所需的代码：

>>> sarray['name'].shape
(8L,)

在结构化数组中，按行或按列访问数据很容易：

>>> sarray['name']
array(['foo', 'several', 'tuples', 'can', 'be', 'inserted', 'at', 'once'],
      dtype='|S11')

>>> sarray[0]
('foo', 3.0, 2)

将数据输入 numpy 函数也非常简单有效：

>>> np.mean(sarray['fval'])
6.5124998092651367

fetchdictarray 与 fetchsarray ¶

这两种方法都提供了将数据从数据库输入到 numpy 友好容器中的方法。结构化数组版本以更简单的方式提供了更大的灵活性来提取行。主要区别在于结果对象的内存布局。对此的深入分析超出了本笔记本的范围。可以这么说，您可以将内存中的 dictarray 视为数组结构（实际上，字典或数组），而结构化数组将像结构数组一样放置在内存中。在处理大量数据时，这可以在性能方面产生很大的不同。