5

In sqlite3's client CLI, there is " .import file TABLE_name " to do it.

But, I do not want to install sqlite3 to my server at present.

In python sqlite3 module, we can creat and edit a DB.

But, I have not found a way to import data-file to a TABLE, except inserting rows one by one.

Any other way?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user343638
  • 51
  • 1
  • 2

1 Answers1

7

You could insert at one shot using executemany command instead of inserting one by one

Lets say I have users.csv with following contents

"Hugo","Boss"
"Calvin","Klein"

and basically open with csv module and pass it to .executemany function

import csv,sqlite3

persons= csv.reader(open("users.csv"))
con = sqlite3.connect(":memory:")

con.execute("create table person(firstname, lastname)")
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

for row in con.execute("select firstname, lastname from person"):
    print row

#(u'Hugo', u'Boss')
#(u'Calvin', u'Klein')
YOU
  • 120,166
  • 34
  • 186
  • 219
  • thank you, S.Mark. it is very useful. it looks like that there is not a ".import"-like function ? – user343638 May 18 '10 at 05:18
  • @cyaos: there is not. By the way, the question is very popular in SO, for example: http://stackoverflow.com/questions/2346074/execute-sqlite3-dot-commands-from-python-or-register-collation-in-command-line And one more thing you should know is that `.import` in CLI is rather primitive: it just splits on separator and will fail when the data contains separator as the corresponsing value is quoted. – newtover May 24 '10 at 09:30