How to put a text file in sqlite just using the Python API?

Question

I have a tab delimited file in the format:

sentenceID (sid) documentID (scid) sentenceText (sent) 

eg.

 100004 100 即便您喜爱流连酒吧，也定然在这轻松安闲的一隅，来一场甜蜜沉醉的约会。 100005 100 您可以慢慢探究菜单上所有的秘密惊喜。 

I want to put it in sqlite3 with the following schema:

 CREATE TABLE sent ( sid INTEGER PRIMARY KEY, scid INTEGER, sent TEXT, ); 

Is there a quick way to use the python API for sqlite ( http://docs.python.org/2/library/sqlite3.html ) to put them in a table?

I did it as such:

 #!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys, codecs con = lite.connect('mycorpus.db') with con: cur = con.cursor() cur.execute("CREATE TABLE Corpus(sid INT, scid INT, sent TEXT, PRIMARY KEY (sid))") for line in codecs.read('corpus.tab','r','utf8'): sid,scid,sent = line.strip().split("\t") cur.execute("INSERT INTO Corpus VALUES("+sid+","+scid+"'"+sent+"')") 

Answer 1

Here is an example of using unicodecsv :

 #!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 import unicodecsv con = sqlite3.connect('mycorpus.db') cur = con.cursor() cur.execute("CREATE TABLE Corpus(sid INT, scid INT, sent TEXT, PRIMARY KEY (sid))") with open('corpus.tab', 'rb') as input_file: reader = unicodecsv.reader(input_file, delimiter="\t") data = [row for row in reader] cur.executemany("INSERT INTO Corpus (sid, scid, sent) VALUES (?, ?, ?);", data) con.commit() 

See also:

• Reading a UTF8 CSV file using Python
• Import CSV file into sqlite3 database table using Python

Hope this helps.

Answer 2

  #!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite con = lite.connect('myCorpus.db') cur = con.cursor() cur.execute("CREATE TABLE Corpus(sid INT, scid INT, sent TEXT, PRIMARY KEY (sid))") data=[row.split('\t') for row in file('myfile.tab','r').readlines()] cur.executemany("INSERT INTO Corpus (sid, scid,sent) VALUES (?, ?, ?);", data) con.commit()