14.6  重复指令

对于数据库程序员来说，一个需要面对的普遍问题是执行很多类似的指令。例如：您或许需要为一个表添加成千上万的新记录。对于SQL语言来说，这就意味着要执行成千上万条INSERT INTO指令。

这效率其实是很低的。SQL实际上是一种语言，所以数据库服务器必须解释您的指令。同时，网络延迟也是一个问题，在每条指令之后，客户端通常必须等待应答来检查错误。

很多SQL服务器支持优化，这样就可以降低和重复指令有关的性能问题。在Python的DB-API中，有两个解决办法：发送含有一列数据的一个指令，或者发送多次相同的指令，但却是不同的数据。

14.6.1  参数风格

通常情况下，指令都含有内置的数据。例如：INSERT INTO ch14 VALUES (12, 'Twelve')包含实际的数据（12 和Twelve），它会被添加到表中。为避免为每条记录都反复解释指令，必须有方法能把数据从指令中分离出来。试想一下，例如，您想把下列数据加到数据库中：

12 Twelve

13 Thirteen

14 Fourteen

15 Fifteen

也许您会把它们转化成类似下面的Python代码：

cur.execute("INSERT INTO ch14 VALUES (12, 'Twelve')")

cur.execute("INSERT INTO ch14 VALUES (13, 'Thirteen')")

cur.execute("INSERT INTO ch14 VALUES (14, 'Fourteen')")

cur.execute("INSERT INTO ch14 VALUES (15, 'Fifteen')")

但是这样是低效的，因为所有的调用都必须被单独解释。我们需要的是找到一个方法，可以把类似这样的简单调用组合成一个简单指令集。DB-API提供这种方法。

不幸地是，对于DB-API程序员来说，共有五种方法来完成这个任务。每个数据库模块可以选择一种支持的方法。您的程序必须使用您选择的数据库所支持的模块之中的方法。如果您使用一个不同的方法，您的程序将不能工作。这个程序将告诉您，您的数据库需要使用哪个方法：

#!/usr/bin/env python

# Parameter style - Chapter 14 - paramstyle.py

import psycopg

print psycopg.paramstyle

每个数据库要求声明一个paramstyle变量，您可以查询到。参数类型定义了代码中占位符的格式。下面是可能的类型，针对DB-API说明书，以使用频度由小变大的顺序介绍：

l qmark。表示question-mark风格。指令字符串中的数据的每一位都被用一个问号替换，参数以list或tuple的形式给出。例如：INSERT INTO ch14 VALUES (?, ?)。

l format。使用和printf()一样的类型格式，不支持对于指定参数Python的扩展名。它带一个list或tuple来转换。例如：INSERT INTO ch14 VALUES(%d, %s)

l numeric。表示numeric风格。指令字符串中的数据的每一位都被一个后面是数字的冒号替换（数字以1开始），参数以list或tuple的形式给出。例如：INSERT INTO ch14 VALUES(:1, :2)

l  named。表示named风格。和numeric类似，但是在冒号后面用名称取代数字。带一个dictionary用来转换。例如：INSERT INTO ch14 VALUES(:number, :text)

l pyformat。支持Python风格的参数，带dictionary用来转换。例如：INSERT INTO ch14 VALUES(%(number)d, %(text)s)。

在本章讨论的三个数据库模块中，PostgreSQL使用pyformat，MySQL使用format，zxJDBC使用qmark。在这一章中，将示范pyformat。

14.6.2  使用executemany()

executemany()函数带一个指令和一列该指令运行的记录。列表上的每条记录要么是一个list，要么是一个dictionary，这取决于使用的数据库模块的参数风格。下面是例子：

#!/usr/bin/env python

# executemany() example - Chapter 14 - executemany.py

import psycopg

rows = ({'num': 0, 'text': 'Zero'},

         {'num': 1, 'text': 'Item One'},

         {'num': 2, 'text': 'Item Two'},

         {'num': 3, 'text': 'Three'})

def getdsn(db = None, user = None, passwd = None, host = None):

    if user == None:

        # Default user to the one they're logged in as

        import os, pwd

        user = pwd.getpwuid(os.getuid())[0]

    if db == None:

        # Default to the username.

        db = user

    dsn = 'dbname=%s user=%s' % (db, user)

    if passwd != None:

        dsn += ' password=' + passwd

    if host != None:

        dsn += ' host=' + host

    return dsn

dsn = getdsn()

print "Connecting to %s" % dsn

dbh = psycopg.connect(dsn)

print "Connection successful."

cur = dbh.cursor()

cur.execute("DELETE FROM ch14")

cur.executemany("INSERT INTO ch14 VALUES (%(num)d, %(text)s)", rows)

dbh.commit()

dbh.close()

这个程序将会把rows中定义的四个记录插入到数据库中。对那些无论用何种方式提高效率的数据来说会发生。在大多数的数据库后端，都会发生优化。然而，一些旧的或是小的数据库可能不会支持优化。这样的话，executemany()也可以执行，但是就失去了性能优化的优点。

注意：在字符串两边不需要引号。即使这是SQL所要求的。执行pyformat的数据库模块会自动加上。

14.6.3  处理那些不适合executemany()的情况

尽管很多情况下，executemany()都工作的很好，但还是有一些情况下不适合使用。它的一个主要的缺点是，在需要执行指令前把所有的记录放在内存中。如果数据大的话，这就是一个问题，它会占有系统的所有内存资源。

如果executemany()不能满足您的需要，那么除了execute()之外，还是有可能取得性能优化的。根据DB-API说明，当execute()被周期性调用时，数据库后端可以执行优化。但是它的第一个参数必须指向同一个对象，而不是一个含有相同值的字符串，即在内存中的同一个字符串对象。和executemany()一样，这样并不能保证优化，并且也不能期望execute()运行得比executemany()快。但是如果不能使用executemany()，这就是一个最好的选择。

#!/usr/bin/env python

# optimized execute() for multiple rows example - Chapter 14

# execute-multiple.py

import psycopg

rows = ({'num': 0, 'text': 'Zero'},

         {'num': 1, 'text': 'Item One'},

         {'num': 2, 'text': 'Item Two'},

         {'num': 3, 'text': 'Three'})

def getdsn(db = None, user = None, passwd = None, host = None):

    if user == None:

        # Default user to the one they're logged in as

        import os, pwd

        user = pwd.getpwuid(os.getuid())[0]

    if db == None:

        # Default to the username.

        db = user

    dsn = 'dbname=%s user=%s' % (db, user)

    if passwd != None:

        dsn += ' password=' + passwd

    if host != None:

        dsn += ' host=' + host

    return dsn

dsn = getdsn()

print "Connecting to %s" % dsn

dbh = psycopg.connect(dsn)

print "Connection successful."

cur = dbh.cursor()

cur.execute("DELETE FROM ch14")

# It is best to set this query before the loop!

query = "INSERT INTO ch14 VALUES (%(num)d, %(text)s)"

for row in rows:

    cur.execute(query, row)

dbh.commit()

dbh.close()

尽管在这个例子中，rows list也保存在内存中，您可以根据从文件或其他数据源读入的数据尽早建立单独的row dictionary。

既然您可以每次为一条记录执行一个指令，也就不用担心内存的问题了。

然而，请记住在本章前面部分的警告，如果您要操作的数据非常大的话，最好还是定期调用commit()。