simple csv to sql lite template

litedbbuild.py

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

"""
Write a csv file to sql lite data base if it exists and if the file exists
For the first one, you will need to create the db, dropping a db of the same name
Furthermore, comment out those lines (see try section in write_csv_to_db), and 
just use the insertion statement
"""

import sqlite3
import csv
import sys

def databaseconnection():

    """
    Creates a connection to an existing sql lite database
    Returns a cursor on the connection
    """

    # default for the connection parameter
    con = None

    try:
        # create a connection object called con
        con = sqlite3.connect('biosource.db')
        cur = con.cursor()

    except sqlite3.Error, e:

        if con:
            con.rollback()

        # if a connection can't be made, print an error with the arguements included
        print "Error %s:" % e.args[0]
        sys.exit(1)

    # return the cursor object    
    return (cur, con)

def write_csv_to_database(filename, cursor, connection):

    """
    Writes the csv file, row by row, into a database (table)
    """

    # choices of filename should be: "TP001_indv_r1.csv", "TP001_indv_e1.csv" etc.
    # eventually I throw into a loop which is why I do one by one row
    with open(filename,"r") as readfile:
        reader = csv.reader(readfile, delimiter=',', quotechar = '"')

        
        try:
          #  cur.execute("DROP TABLE IF EXISTS indvTree")
          #  cur.execute("CREATE TABLE indvTree(dbCode TEXT, entity INT, treeid TEXT, dbh REAL, year INT, study TEXT, stand TEXT, plot TEXT, species TEXT, proxy TEXT, proxy_qual TEXT, density REAL, biomass REAL, bioflag TEXT, jenkins REAL, volume REAL, basal REAL, source TEXT)")

            to_db = [(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17]) for row in reader]

            cur.executemany("INSERT INTO indvTree VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", to_db)
           
            con.commit()
    
        except lite.Error, e:
    
            if con:
                con.rollback()

            print "Error %s:" % e.args[0]
            sys.exit(1)
    
        finally:
    
            if con:
               con.close() 


    print "finished transaction"

if __name__ == "__main__":

   # list_of_files = ["TP001_indv_r1.csv", "TP001_indv_e1.csv", "TP001_indv_w1.csv", "TP001_indv_w2.csv"]

    filename = "TP001_indv_w2.csv"
    (cur, con) = databaseconnection()
    write_csv_to_database(filename, cur, con)