Importing a CSV file into a sqlite3 database table using Python

Question

I have a CSV file and I want to bulk-import this file into my sqlite3 database using Python. the command is ".import .....". but it seems that it cannot work like this. Can anyone give me an example of how to do it in sqlite3? I am using windows just in case. Thanks

Answer 1

import csv, sqlite3  con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db' cur = con.cursor() cur.execute("CREATE TABLE t (col1, col2);") # use your column names here  with open('data.csv','r') as fin: # `with` statement available in 2.5+     # csv.DictReader uses first line in file for column headings by default     dr = csv.DictReader(fin) # comma is default delimiter     to_db = [(i['col1'], i['col2']) for i in dr]  cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db) con.commit() con.close() 

Answer 2

Creating an sqlite connection to a file on disk is left as an exercise for the reader ... but there is now a two-liner made possible by the pandas library

df = pandas.read_csv(csvfile) df.to_sql(table_name, conn, if_exists='append', index=False)