iopro.pyodbc 增强功能¶

演示代码显示 iopro.pyodbc 子模块的增强功能¶

该演示展示了 iopro.pyodbc 模块的基本功能。它首先将通过 ODBC 与您选择的数据库连接，创建并填充一个新表（市场），然后使用不同的方法（fetchall()、fetchdictarray() 和 fetchsarray()）检索数据。

作者：Francesc Alted，Anaconda

>>> import iopro.pyodbc as pyodbc
>>> # Open the database (use the most appropriate for you)
>>> connect_string = 'DSN=odbcsqlite;DATABASE=market.sqlite'  # SQLite
>>> #connect_string = 'Driver={SQL Server};SERVER=MyWinBox;DATABASE=Test;USER=Devel;PWD=XXX'  # SQL Server
>>> #connect_string = 'DSN=myodbc3;UID=devel;PWD=XXX;DATABASE=test'  # MySQL
>>> #connect_string = 'DSN=PSQL;UID=devel;PWD=XXX;DATABASE=test'   # PostgreSQL
>>> connection = pyodbc.connect(connect_string)
>>> cursor = connection.cursor()

创建测试表（如果已经完成，则可选）¶

>>> try:
...     cursor.execute('drop table market')
... except:
...     pass
>>> cursor.execute('create table market (symbol_ varchar(5), open_ float, low_ float, high_ float, close_ float, volume_ int)')

填写测试表（如果已经完成，则可选）¶

>>> from time import time
>>> t0 = time()
>>> N = 1000*1000
>>> for i in xrange(N):
...     cursor.execute(
...         "insert into market(symbol_, open_, low_, high_, close_, volume_)"
...         " values (?, ?, ?, ?, ?, ?)",
...         (str(i), float(i), float(2*i), None, float(4*i), i))
>>> cursor.execute("commit")             # not supported by SQLite
>>> t1 = time() - t0
>>> print "Stored %d rows in %.3fs" % (N, t1)

以传统方式进行查询¶

>>> # Query of the full table using the traditional fetchall
>>> query = "select * from market"
>>> cursor.execute(query)
>>> %time all = cursor.fetchall()
CPU times: user 5.23 s, sys: 0.56 s, total: 5.79 s
Wall time: 7.09 s

执行查询并获取 NumPy 数组的字典¶

>>> # Query of the full table using the fetchdictarray (retrieve a dictionary of arrays)
>>> cursor.execute(query)
>>> %time dictarray = cursor.fetchdictarray()
CPU times: user 0.92 s, sys: 0.10 s, total: 1.02 s
Wall time: 1.44 s

查看检索到的数据¶

>>> dictarray.keys()
['high_', 'close_', 'open_', 'low_', 'volume_', 'symbol_']
>>> dictarray['high_']
array([ nan,  nan,  nan, ...,  nan,  nan,  nan])
>>> dictarray['symbol_']
array(['0', '1', '2', ..., '99999', '99999', '99999'], dtype='|S6')

执行查询并获得一个 NumPy 结构化数组¶

>>> # Query of the full table using the fetchsarray (retrieve a structured array)
>>> cursor.execute(query)
>>> %time sarray = cursor.fetchsarray()
CPU times: user 1.08 s, sys: 0.11 s, total: 1.20 s
Wall time: 1.99 s

查看检索到的数据¶

>>> sarray.dtype
dtype([('symbol_', 'S6'), ('open_', '&lt;f8'), ('low_', '&lt;f8'), ('high_', '&lt;f8'), ('close_', '&lt;f8'), ('volume_', '&lt;i4')])
>>> sarray[0:10]
array([('0', 0.0, 0.0, nan, 0.0, 0), ('1', 1.0, 2.0, nan, 4.0, 1),
       ('2', 2.0, 4.0, nan, 8.0, 2), ('3', 3.0, 6.0, nan, 12.0, 3),
       ('4', 4.0, 8.0, nan, 16.0, 4), ('5', 5.0, 10.0, nan, 20.0, 5),
       ('6', 6.0, 12.0, nan, 24.0, 6), ('7', 7.0, 14.0, nan, 28.0, 7),
       ('8', 8.0, 16.0, nan, 32.0, 8), ('9', 9.0, 18.0, nan, 36.0, 9)],
      dtype=[('symbol_', 'S6'), ('open_', '&lt;f8'), ('low_', '&lt;f8'), ('high_', '&lt;f8'), ('close_', '&lt;f8'), ('volume_', '&lt;i4')])
>>> sarray['symbol_']
array(['0', '1', '2', ..., '99999', '99999', '99999'], dtype='|S6')