量化分析第三课作业


使用冯老师的代码

!/usr/bin/python

-*- coding: utf-8 -*-

insert_symbols.py

from __future__ import print_function

import datetime
from math import ceil

import bs4
import MySQLdb as mdb
import requests
import mysql.connector
def obtain_parse_wiki_snp500():
    """
    Download and parse the Wikipedia list of S&P500 
    constituents using requests and BeautifulSoup.

    Returns a list of tuples for to add to MySQL.
    """
    # Stores the current time, for the created_at record
    now = datetime.datetime.utcnow()

    # Use requests and BeautifulSoup to download the 
    # list of S&P500 companies and obtain the symbol table
    response = requests.get(
        "http://en.wikipedia.org/wiki/List_of_S%26P_500_companies"
    )
    soup = bs4.BeautifulSoup(response.text)

    # This selects the first table, using CSS Selector syntax
    # and then ignores the header row ([1:])
    symbolslist = soup.select('table')[0].select('tr')[1:]

    # Obtain the symbol information for each 
    # row in the S&P500 constituent table
    symbols = []
    for i, symbol in enumerate(symbolslist):
        tds = symbol.select('td')
        symbols.append(
            (
                tds[0].select('a')[0].text,  # Ticker
                'stock', 
                tds[1].select('a')[0].text,  # Name
                tds[3].text,  # Sector
                'USD', now, now
            ) 
        )
    return symbols


def insert_snp500_symbols(symbols):
    """
    Insert the S&P500 symbols into the MySQL database.
    """
   # Connect to the MySQL instance
    db_host = 'localhost'
    db_user = 'root'
    db_pass = 'password'
    db_name = 'securities_master'
    con = mysql.connector.connect(
        host=db_host, user=db_user, passwd=db_pass, db=db_name
    )
    
    # Create the insert strings
    column_str = """ticker, instrument, name, sector, 
                 currency, created_date, last_updated_date
                 """
    insert_str = ("%s, " * 7)[:-2]
    final_str = "INSERT INTO symbol (%s) VALUES (%s)" % \
        (column_str, insert_str)
        
    # Using the MySQL connection, carry out 
    # an INSERT INTO for every symbol
    with con: 
        cur = con.cursor()
        cur.executemany(final_str, symbols)
if __name__ == "__main__":
    symbols = obtain_parse_wiki_snp500()
    insert_snp500_symbols(symbols)
    print("%s symbols were successfully added." % len(symbols))


一直在报错(如下),请问是什么原因,谢谢!

AttributeError Traceback (most recent call last)
<ipython-input-43-0bed839875f3> in <module>()
1 if __name__ == "__main__":
2 symbols = obtain_parse_wiki_snp500()
----> 3 insert_snp500_symbols(symbols)
4 print("%s symbols were successfully added." % len(symbols))

<ipython-input-42-1d04aae8d84c> in insert_snp500_symbols(symbols)
59 # Using the MySQL connection, carry out
60 # an INSERT INTO for every symbol
---> 61 with con:
62 cur = con.cursor()
63 cur.executemany(final_str, symbols)

AttributeError: __enter__
已邀请:

要回复问题请先登录注册

返回顶部