connect Python to MYSQL, obtain SP500 symbols
<p>The code is below:</p>

<pre><code>#!/usr/bin/python
# -*- coding: utf-8 -*-

# insert_symbols.py
#table name should be 's_master', password should be '*******', user name should be 's_user'

from __future__ import print_function

import datetime
from math import ceil

import bs4
import MySQLdb as mdb
import requests


def obtain_parse_wiki_snp500():
    """
    Download and parse the Wikipedia list of S&amp;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&amp;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 is the original version, i replaced with the code below
    soup = bs4.BeautifulSoup(response.text, "html.parser")

    # 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&amp;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&amp;P500 symbols into the MySQL database.
    """
    # Connect to the MySQL instance
    db_host = 'localhost'
    db_user = 's_user'
    db_pass = '*******'
    db_name = 's_master'
    con = mdb.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))
</code></pre>

<p>The error message I got is below:</p>

<pre><code>Traceback (most recent call last):
  File "insert_symbols.py", line 86, in &lt;module&gt;
    insert_snp500_symbols(symbols)
  File "insert_symbols.py", line 81, in insert_snp500_symbols
    cur.executemany(final_str, symbols)
  File "/home/haolun/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 285, in executemany
    r = self._query(qs)
  File "/home/haolun/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 378, in _query
    rowcount = self._do_query(q)
  File "/home/haolun/.local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 341, in _do_query
    db.query(q)
  File "/home/haolun/.local/lib/python2.7/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1146, "Table 's_master.symbol' doesn't exist")
</code></pre>

<p>I don't know why I am getting this _"mysql_exceptions.ProgrammingError: (1146, "Table 's_master.symbol' doesn't exist") message. I did create the s_master table. Is there anything wrong with beautifulsoup commend or anything else? </p>

<p>I think you don't create the table correctly.I write a sql file <code>init.sql</code>, and import it to the mysql database by using the following command:</p>

<pre><code>mysql -u root -p &lt; init.sql
</code></pre>

<p>This is the content of the init.sql</p>

<pre><code>create database if not exists s_master default charset utf8 collate utf8_unicode_ci;
-- chage the password to 123456
grant all on s_master.* to s_user@'localhost' identified by '123456';
use s_master;
create table if not exists symbol(
    ticker varchar(40),
    instrument varchar(40),
    name varchar(40),
    sector varchar(40),
    currency varchar(40),
    created_date datetime,
    last_updated_date datetime
);
</code></pre>

<p>After that I was able to run your program correctly, the result of the program is:</p>

<pre><code>â /tmp [sof]$ python yourprogram.py
(u'MMM', 'stock', u'3M Company', u'Industrials', 'USD', datetime.datetime(2016, 9, 26, 5, 24, 11, 62336), datetime.datetime(2016, 9, 26, 5, 24, 11, 62336))
505 symbols were successfully added.
â /tmp [sof]$ 
</code></pre>

python mysql web-scraping beautifulsoup