csv文件导入sqlite3

实验用的csv文件格式很简单，[1, 'www.qq.com']这种格式
步骤如下：
1.新建文件下sqlite,命令行到此目录下，命令：sqlite3 test.db新建数据库
2.create table images(_id integer primary key not null, url vachar(500));
新建表images，表建了后，可在该目录下找到test.db文件
例子：

create table title_temp(title vachar(200),titlehash vachar(100));

create table image_temp(title vachar(200) ,titlehash vachar(100), url vachar(500));

create table title(_id integer primary key autoincrement,title vachar(200),titlehash vachar(100));

create table image(_id integer primary key autoincrement,title vachar(200),titlehash vachar(100), url vachar(500));

3. .separator ","非常重要，确定分隔符
4. .import temp.csv images导入完成
   附加：
   1.打开已有的数据库文件 .open test.db
   2.主键自增问题
   create table title(_id integer primary key autoincrement,title vachar(200),titlehash vachar(100));
   直接插入cvs文件可能出现insert failed data type mismatch或者filling the rest with NULL，这时要新建个临时表
   create table title_temp(title vachar(200),titlehash vachar(100));

.import title.csv title_temp

.import image.csv image_temp

cvs表导入临时表后，再敲命令

INSERT INTO title(title, titlehash) SELECT * FROM title_temp;

INSERT INTO image(title, titlehash,url) SELECT * FROM image_temp;

delete from title_temp;

delete from image_temp;

附上生成csv文件的python代码

import csv
def create_csv():
    url1 = [1, 'www.qq.com']
    url2 = [2, 'www.163.com']
    url3 = [3, 'www.google.com']
    csv_file = open('temp.csv', 'wb')
    csv_write = csv.writer(csv_file, dialect='excel')
    csv_write.writerow(url1)
    csv_write.writerow(url2)
    csv_write.writerow(url3)

if __name__ == '__main__':
    create_csv()