Importing a CSV file into a SQLite3 database table in Python

For this you need to import csv, sqlite3 libraries:

import csv, sqlite3

Lets use an in-memory database and create a simple table with two columns:

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);")

Now, lets load our .csv file into a dictionary:

with open('data.csv','rb') as fin:
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['col1'], i['col2']) for i in dr]

csv.DictReader uses first line in the .csv file for column headings by default. Comma is also the default delimiter. The actual import:

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()

If you want to play around with an example, you can try this:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys, csv, sqlite3

def main():
    con = sqlite3.connect(sys.argv[1]) # database file input
    cur = con.cursor()
    cur.executescript("""
        DROP TABLE IF EXISTS t;
        CREATE TABLE t (COL1 TEXT, COL2 TEXT);
        """) # checks to see if table exists and makes a fresh table.

    with open(sys.argv[2], "rb") as f: # CSV file input
        reader = csv.reader(f, delimiter=',') # no header information with delimiter
        for row in reader:
            to_db = [unicode(row[0], "utf8"), unicode(row[1], "utf8")] # Appends data from CSV file representing and handling of text
            cur.execute("INSERT INTO neto (COL1, COL2) VALUES(?, ?);", to_db)
            con.commit()
    con.close() # closes connection to database

if __name__=='__main__':
    main()

Please not that the code above, we encode the input.