Python操作sqlite3快速、安全插入数据（防注入）的实例

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)