Untitled

SQLAlchemy: Problems Migrating to PostgreSQL from SQLite (e.g. sqlalchemy.exc.ProgrammingError:)

raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)

 

sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt 'INSERT INTO cnn_hot_stocks (datetime, list, ticker, price, change, "pctChange") VALUES (%(datetime)s, %(list)s, %(ticker)s, %(price)s, %(change)s, %(pctChange)s)' {'price': Decimal('7.94'), 'list': 'active', 'datetime': datetime.datetime(2012, 6, 23, 11, 45, 1, 544361), 'pctChange': u'+1.53%', 'ticker': u'BAC', 'change': Decimal('0.12')}

       

dbstring = "postgresql://postgres:postgres@localhost:5432/algo"

db = create_engine(dbstring)

db.echo = True  # Try changing this to True and see what happens

metadata = MetaData(db)

 

cnn_hot_stocks = Table('cnn_hot_stocks', metadata, autoload=True)

 

i = cnn_hot_stocks.insert() # running log from cnn hot stocks web-site

 

def scrape_data():

    try:

            html = urllib2.urlopen('http://money.cnn.com/data/hotstocks/').read()

            markup, errors = tidy_document(html)

            soup = BeautifulSoup(markup,)

    except Exception as e:

            pass

    list_map = { 2 : 'active',

                 3 : 'gainer',

                 4 : 'loser'

               }

    # Iterate over 3 tables on CNN hot stock web-site

    for x in range(2, 5):

            table = soup('table')[x]

            for row in table.findAll('tr')[1:]:

                    timestamp = datetime.now()

                    col = row.findAll('td')

                    ticker = col[0].a.string

                    price = Decimal(col[1].span.string)

                    change = Decimal(col[2].span.span.string)

                    pctChange = col[3].span.span.string

                    log_data = {'datetime'  : timestamp,

                                'list'      : list_map[x],

                                'ticker'    : ticker,

                                'price'     : price,

                                'change'    : change,

                                'pctChange' : pctChange

                               }

                    print log_data

                    # Commit to DB

                    i.execute(log_data)

       

cnn_hot_stocks = Table('cnn_hot_stocks', metadata, # log of stocks data on cnn hot stocks lists

            Column('datetime', DateTime, primary_key=True),

            Column('list', String), # loser/gainer/active

            Column('ticker', String),

            Column('price', Numeric),

            Column('change', Numeric),

            Column('pctChange', String),

            )