Bulk import/load csv to sqlite using Python

Question

i have searched for a method to do a bulk import of a csv to sqlite using python script.

Currently i am using an insert which runs for every row of the csv:

with open('file_name.csv','rb') as fin:
        Read_csv = csv.DictReader(fin) # comma is default delimiter
        to_db = [(i['col1'],i['col2'],i['col3']) for i in Read_csv]
        DbCur.executemany("INSERT INTO table (col1,col2,col3) values (?,?,?);",to_db)

I need a method to bulk insert the entire data instead of inserting row by row which i think is not very optimized coding. Any help is appreciated.

score 1 · Accepted Answer · answered Jan 27 '15 at 12:50

Suppose you have a table like this:

class Sample(Base):
    __tablename__="Sample"
    X=Column(Integer, primary_key=True)
    Y=Column(Integer)
    Z=Column(Integer)

Suppose you have the following data in a file named "test.csv":

X,Y,Z
1,2,3
4,5,6

You can do bulk import, given that no duplicates in your data and no duplicates in your existing table:

import pyexcel as pe

...
pe.save_as(file_name="test.csv", dest_session=session, dest_table=Sample)

Here is the complete code for bulk import to sqlite:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column , Integer
from sqlalchemy.orm import sessionmaker
import pyexcel as pe

engine=create_engine("sqlite:///tmp.db")
Base=declarative_base()

class Sample(Base):
    __tablename__="Sample"
    X=Column(Integer, primary_key=True)
    Y=Column(Integer)
    Z=Column(Integer)

Session=sessionmaker(bind=engine)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = Session()
# produce test csv
adict = {
    "X": [1, 4],
    "Y": [2, 5],
    "Z": [3, 6]
}
sheet = pe.get_sheet(adict=adict)
sheet.save_as("test.csv")
# end production
# bulk import
pe.save_as(file_name="test.csv", dest_session=session, dest_table=Sample)
result = pe.get_dict(session=session, table=Sample)

# verify result
print(result)

In real world, columns are of differnt types, so you may want to format your columns after loading csv file before the bulk upload.

you are creating adict with all the values of the csv? I have lots of records in my csv. — user1474157, Jan 27 '15 at 14:22
@user1474157, am creating a list of dicts, [{"X": 1, "Y":2, "Z":3}, {"X":...}]. How big is your data set? — chfw, Jan 27 '15 at 14:25

Bulk import/load csv to sqlite using Python

1 Answers1