代码如下:
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测试数据mit()
cursor.close()
conn.close()
def drop_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''drop table userinfo''')
conn测试数据mit()
cursor.close()
conn.close()
def insert1():
users = [('qq','qq@example测试数据'),
('ww','ww@example测试数据'),
('ee','ee@example测试数据'),
('rr','rr@example测试数据'),
('tt','tt@example测试数据'),
('yy','yy@example测试数据'),
('uu','uu@example测试数据')
]
start = time.clock()
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
for user in users:
cursor.execute("insert into userinfo(name, email) values(?, ?)", user)
conn测试数据mit()
cursor.close()
conn.close()
end = time.clock()
print start, end, end-start
def insert2():
users = [('qq','qq@example测试数据'),
('ww','ww@example测试数据'),
('ee','ee@example测试数据'),
('rr','rr@example测试数据'),
('tt','tt@example测试数据'),
('yy','yy@example测试数据'),
('uu','uu@example测试数据')
]
start = time.clock()
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
for user in users:
cursor.execute("insert into userinfo(name, email) values(?, ?)", user)
conn测试数据mit()
cursor.close()
conn.close()
end = time.clock()
print start, end, end-start
def insert3():
users = [('qq','qq@example测试数据'),
('ww','ww@example测试数据'),
('ee','ee@example测试数据'),
('rr','rr@example测试数据'),
('tt','tt@example测试数据'),
('yy','yy@example测试数据'),
('uu','uu@example测试数据')
]
start = time.clock()
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.executemany("insert into userinfo(name, email) values(?, ?)", users)
conn测试数据mit()
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)
某次运行结果：
另外一次运行结果：
在运行结果中，第三列表示插入数据使用的时间。综合看来，方法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测试数据mit()
cursor.close()
conn.close()
def drop_tables(dbname):
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.execute('''drop table userinfo''')
conn测试数据mit()
cursor.close()
conn.close()
def insert():
users = [('qq','qq@example测试数据'),
('ww','ww@example测试数据'),
('ee','ee@example测试数据'),
('rr','rr@example测试数据'),
('tt','tt@example测试数据'),
('yy','yy@example测试数据'),
('uu','uu@example测试数据')
]
conn = sqlite3.connect(dbname)
cursor = conn.cursor()
cursor.executemany("insert into userinfo(name, email) values(?, ?)", users)
conn测试数据mit()
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测试数据")
insecure_select("' or 1=1;--")
secure_select("uu@example测试数据")
secure_select("' or 1=1;--")
drop_tables(dbname)
运行结果：
代码如下:
select name from userinfo where email='uu@example测试数据'
(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测试数据'
(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注入。
查看更多关于Python操作sqlite3快速、安全插入数据（防注入）的实例的详细内容...