From 2275d3d22d9cc40f2b90ef33d403f2a8535dc7cd Mon Sep 17 00:00:00 2001
From: Thibaut Horel <thibaut.horel@gmail.com>
Date: Fri, 14 Oct 2011 01:11:41 +0200
Subject: Load all the data from csv file into the database

Change in the database schema, volume is now a float,
update your database consequently
---
 database.py  | 25 +++++++++++++++++++------
 database.sql |  2 +-
 2 files changed, 20 insertions(+), 7 deletions(-)

diff --git a/database.py b/database.py
index c00ccd8..e29887a 100644
--- a/database.py
+++ b/database.py
@@ -22,22 +22,35 @@ def addTickerHistory(name, handler, cursor, conn):
     cursor.execute("SELECT stockid FROM stock WHERE ticker=%s",(name,))
     if cursor.rowcount == 0:
         cursor.execute("INSERT INTO stock(ticker) VALUES (%s) RETURNING stockid",(name,))
-        conn.commit()
         id = cursor.fetchone()[0]
     else:
-        id = cursor.fetchone()[0]
-        
+        id = cursor.fetchone()[0] 
+
     def clean(field):
         if field == "NA":
             return None
+        else:
+            return field
 
     for row in handler:
-        cursor.execute("INSERT INTO history(date, stockID, open) VALUES (%s, %s, %s)",
-                       (row['Date'], id, row['Open']))
+        for (key, value) in row.items():
+            row[key] = clean(value)
+
+        cursor.execute("INSERT INTO history(date, stockID, open, high, low,"
+                       "close, volume, adjClose, div, adjDiv, split) "
+                       "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                       (row['Date'], id, row['Open'], row['High'], row['Low'],
+                        row['Unadj.Close'], row['Volume'], row['Close'],
+                        row['Div'], row['Adj.Div'], row['Split'] ) )
         
     conn.commit()
         
 if __name__ == '__main__':
+
+    if len(argv) != 5:
+        print argv[0], "<database> <user> <passwd> <filename>"
+        exit(1)
+
     database = argv[1]
     user =  argv[2]
     password = argv[3]
@@ -46,7 +59,7 @@ if __name__ == '__main__':
 
     filename = argv[4]
     handler = open(filename,"r")
-    ticker = splitext(basename(filename))[0]
+    ticker = splitext(basename(filename))[0] #get the ticker from the filename
     csvHandler = csv.DictReader(handler)
     
     addTickerHistory(ticker, csvHandler, cursor, conn)
diff --git a/database.sql b/database.sql
index d5cb31d..8081b4f 100644
--- a/database.sql
+++ b/database.sql
@@ -11,7 +11,7 @@ CREATE TABLE history (
     low double precision DEFAULT NULL,
     close double precision DEFAULT NULL,
     adjClose double precision DEFAULT NULL,
-    volume integer DEFAULT NULL,
+    volume double precision DEFAULT NULL,
     div real DEFAULT NULL,
     adjDiv real DEFAULT NULL,
     split real DEFAULT NULL,
-- 
cgit v1.2.3-70-g09d2