Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Any other way to import data files(like .csv) in python sqlite3 module ? [not insert one by one]

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?

like image 327
user343638 Avatar asked May 18 '10 04:05

user343638


People also ask

What are the two different ways to import CSV module?

There are two ways to read data from a CSV file using csv . The first method uses csv. Reader() and the second uses csv. DictReader() .

How we can call the import CSV file in sqlite3?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.


1 Answers

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')
like image 164
YOU Avatar answered Oct 20 '22 11:10

YOU