load csv files into postgres db sqlalchemy

1from numpy import genfromtxt
2from time import time
3from datetime import datetime
4from sqlalchemy import Column, Integer, Float, Date
5from sqlalchemy.ext.declarative import declarative_base
6from sqlalchemy import create_engine
7from sqlalchemy.orm import sessionmaker
8
9def Load_Data(file_name):
10    data = genfromtxt(file_name, delimiter=',', skip_header=1, converters={0: lambda s: str(s)})
11    return data.tolist()
12
13Base = declarative_base()
14
15class Price_History(Base):
16    #Tell SQLAlchemy what the table name is and if there's any table-specific arguments it should know about
17    __tablename__ = 'Price_History'
18    __table_args__ = {'sqlite_autoincrement': True}
19    #tell SQLAlchemy the name of column and its attributes:
20    id = Column(Integer, primary_key=True, nullable=False)
21    fid = Column(Integer, ForeignKey('Price_History.id'))
22    date = Column(Date)
23    opn = Column(Float)
24    hi = Column(Float)
25    lo = Column(Float)
26    close = Column(Float)
27    vol = Column(Float)
28
29if __name__ == "__main__":
30    t = time()
31
32    #Create the database
33    engine = create_engine('sqlite:///csv_test.db')
34    Base.metadata.create_all(engine)
35
36    #Create the session
37    session = sessionmaker()
38    session.configure(bind=engine)
39    s = session()
40
41    try:
42        file_name = "t.csv" #sample CSV file used:  http://www.google.com/finance/historical?q=NYSE%3AT&ei=W4ikVam8LYWjmAGjhoHACw&output=csv
43        data = Load_Data(file_name) 
44
45        for i in data:
46            record = Price_History(**{
47                'date' : datetime.strptime(i[0], '%d-%b-%y').date(),
48                'opn' : i[1],
49                'hi' : i[2],
50                'lo' : i[3],
51                'close' : i[4],
52                'vol' : i[5]
53            })
54            s.add(record) #Add all the records
55
56        s.commit() #Attempt to commit all the records
57    except:
58        s.rollback() #Rollback the changes on error
59    finally:
60        s.close() #Close the connection
61    print "Time elapsed: " + str(time() - t) + " s."