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

table通过使用下面语句创建：
代码如下:create table userinfo(name text, email text)

更快地插入数据

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 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
在运行结果中威县网站优化看看以下三种方法的速度。
代码如下:
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()

标签： textdbnamenameuserinfo