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

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

更快地插入数据

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

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)
</div>

某次运行结果：

更安全地操作数据库

先上代码：

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(