table通过使用下面语句创建：

复制代码 代码如下:create table userinfo(name text, email text)

更快地插入数据

在此用time.clock()来计时，看看以下三种方法的速度。

复制代码 代码如下:
import sqlite3
import time

def create_tables(dbname): 
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.execute(\’\’\’create table userinfo(name text, email text)\’\’\’)
    conn.commit()
    cursor.close()
    conn.close()
def drop_tables(dbname):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.execute(\’\’\’drop table userinfo\’\’\’)
    conn.commit()
    cursor.close()
    conn.close()

def insert1():
    users = [(\’qq\’,\’qq@example.com\’),
            (\’ww\’,\’ww@example.com\’),
            (\’ee\’,\’ee@example.com\’),
            (\’rr\’,\’rr@example.com\’),
            (\’tt\’,\’tt@example.com\’),
            (\’yy\’,\’yy@example.com\’),
            (\’uu\’,\’uu@example.com\’)
            ]
    start = time.clock()
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    for user in users:
        cursor.execute(\”insert into userinfo(name, email) values(?, ?)\”, user)
        conn.commit()
    cursor.close()
    conn.close()
    end = time.clock()
    print start, end, end-start

def insert2():
    users = [(\’qq\’,\’qq@example.com\’),
            (\’ww\’,\’ww@example.com\’),
            (\’ee\’,\’ee@example.com\’),
            (\’rr\’,\’rr@example.com\’),
            (\’tt\’,\’tt@example.com\’),
            (\’yy\’,\’yy@example.com\’),
            (\’uu\’,\’uu@example.com\’)
            ]
    start = time.clock()
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    for user in users:
        cursor.execute(\”insert into userinfo(name, email) values(?, ?)\”, user)
    conn.commit()
    cursor.close()
    conn.close()
    end = time.clock()
    print start, end, end-start

def insert3():
    users = [(\’qq\’,\’qq@example.com\’),
            (\’ww\’,\’ww@example.com\’),
            (\’ee\’,\’ee@example.com\’),
            (\’rr\’,\’rr@example.com\’),
            (\’tt\’,\’tt@example.com\’),
            (\’yy\’,\’yy@example.com\’),
            (\’uu\’,\’uu@example.com\’)
            ]
    start = time.clock()
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.executemany(\”insert into userinfo(name, email) values(?, ?)\”, users)
    conn.commit()
    cursor.close()
    conn.close()
    end = time.clock()
    print start, end, end-start

if __name__ == \’__main__\’:
    dbname = \’test.db\’
    create_tables(dbname)
    insert1()
    drop_tables(dbname)
    create_tables(dbname)
    insert2()
    drop_tables(dbname)
    create_tables(dbname)
    insert3()
    drop_tables(dbname)

某次运行结果：

复制代码 代码如下:
4.05223164501e-07 0.531585119557 0.531584714334
0.755963264089 0.867329935942 0.111366671854
1.0324360882 1.12175173111 0.0893156429109
另外一次运行结果：
复制代码 代码如下:
4.05223164501e-07 0.565988971446 0.565988566223
0.768132520942 0.843723660494 0.0755911395524
1.04367819446 1.13247636739 0.0887981729298
在运行结果中，第三列表示插入数据使用的时间。综合看来，方法insert1()的速度很慢，原因在于每次insert都commit()。

更安全地操作数据库

先上代码：

复制代码 代码如下:
import sqlite3

def create_tables(dbname): 
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.execute(\’\’\’create table userinfo(name text, email text)\’\’\’)
    conn.commit()
    cursor.close()
    conn.close()

def drop_tables(dbname):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.execute(\’\’\’drop table userinfo\’\’\’)
    conn.commit()
    cursor.close()
    conn.close()

def insert():
    users = [(\’qq\’,\’qq@example.com\’),
            (\’ww\’,\’ww@example.com\’),
            (\’ee\’,\’ee@example.com\’),
            (\’rr\’,\’rr@example.com\’),
            (\’tt\’,\’tt@example.com\’),
            (\’yy\’,\’yy@example.com\’),
            (\’uu\’,\’uu@example.com\’)
            ]
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    cursor.executemany(\”insert into userinfo(name, email) values(?, ?)\”, users)
    conn.commit()
    cursor.close()
    conn.close()

def insecure_select(text):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    print \”select name from userinfo where email=\’%s\’\” % text
    for row in cursor.execute(\”select name from userinfo where email=\’%s\’\” % text):
        print row
def secure_select(text):
    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()
    print \”select name from userinfo where email=\’%s\’\” % text
    for row in cursor.execute(\”select name from userinfo where email= ? \”, (text,)):
        print row

if __name__ == \’__main__\’:
    dbname = \’test.db\’
    create_tables(dbname)
    insert()
    insecure_select(\”uu@example.com\”)
    insecure_select(\”\’ or 1=1;–\”)
    secure_select(\”uu@example.com\”)
    secure_select(\”\’ or 1=1;–\”)
    drop_tables(dbname)

运行结果：
复制代码 代码如下:
select name from userinfo where email=\’uu@example.com\’
(u\’uu\’,)
select name from userinfo where email=\’\’ or 1=1;–\’
(u\’qq\’,)
(u\’ww\’,)
(u\’ee\’,)
(u\’rr\’,)
(u\’tt\’,)
(u\’yy\’,)
(u\’uu\’,)
select name from userinfo where email=\’uu@example.com\’
(u\’uu\’,)
select name from userinfo where email=\’\’ or 1=1;–\’

函数insecure_select(text)和secure_select(text)的本意都是根据email获取对应的用户名信息。但是insecure_select(text)的实现容易引起sql注入。

insecure_select(\”\’ or 1=1;–\”)便是一个例子。在insecure_select()中cursor.execute()只有一个参数，即sql语句，这个生成的sql语句如果有问题，还是会照常执行。

secure_select(text)的实现可以防止sql注入，cursor.execute()的第一个参数使用了占位符?表示要被替代的内容，第二个参数指定每个占位符对应的值，在底层实现上，这种方法（至少）转义了特殊字符，可以防止sql注入。