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',user) conn.commit() cursor.close() conn.close() end = time.clock() print start,end-start
def insert3(): users = [('qq','uu@example.com') ] start = time.clock() conn = sqlite3.connect(dbname) cursor = conn.cursor() cursor.executemany("insert into userinfo(name,users) conn.commit() cursor.close() conn.close() end = time.clock() print start,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','uu@example.com') ] conn = sqlite3.connect(dbname) cursor = conn.cursor() cursor.executemany("insert into userinfo(name,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注入。 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|