mardi 14 juin 2016

Python values into MySQL

Using Python and ystockquote - I import a set in the format {'previous_close': u'2.16','shares_owned': u'-38.30%',....} of around 80+ values. I've setup a MySQL table with receiving columns to get the values.

I'm getting stuck on storing the python values in MySQL. I have broken the dictionary into just the values - so only the back half of the tuples are imported into the SQL table. How do I create storage statement to get the Python returns into the respective MySQL columns?

import MySQLdb
import ystockquote

mydb = MySQLdb.connect(host='localhost',
   user= '****',
   passwd='****',
   db='****')
cur = mydb.cursor()

def store (values):
    cur.execute('INSERT IGNORE INTO YAHOO (previous_close, shares_owned, change_from_52_week_low, revenue, Average_daily_volume, todays_range_realtime, today_open, Last_trade_realtime_time, fiftytwo_week_high, Ebitda, fifty_sma, pe_realtime, ask_realtime, percent_change_50_sma, percent_change_from_52_week_low, price_paid, pe, holdings_value, price_eps_estimate_next_year, dividend_per_share, twohundred_sma, trade_links, market_cap, todays_value_change_realtime, , change_realtime, low_limit, volume, todays_high, ask_size, holdings_gain, shares_outstanding, holdings_gain_realtime, last_trade_price, notes, change_percent, change_percent_realtime, ticker_trend, todays_low, bid_realtime, change_50_sma, more_info, price_book, price_eps_estimate_current_year, order_book_realtime, todays_range, change_from_52_week_high, book_value, market_cap_realtime, dividend_yield, percent_change_from_52_week_high, holdings_gain_percent, stock_exchange, annualized_gain, high_limit, todays_value_change, short_ratio, company_name, trade_date, dividend_pay_date, change_200_sma, peg, bid_size, last_trade_time_plus, eps_estimate_next_quarter, eps_estimate_current_year, fiftytwo_week_range, fiftytwo_week_low, last_trade_date, holdings_value_realtime, ex_dividend_date, after_hours_change_realtime, price_sales, _change, eps_estimate_next_year, holdings_gain_percent_realtime,  eps, one_year_target, last_trade_time, float_shares, change_percent_change, last_trade_size) VALUES  ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")',(previous_close, shares_owned, change_from_52_week_low, revenue, Average_daily_volume, todays_range_realtime, today_open, Last_trade_realtime_time, fiftytwo_week_high, Ebitda, fifty_sma, pe_realtime, ask_realtime, percent_change_50_sma, percent_change_from_52_week_low, price_paid, pe, holdings_value, price_eps_estimate_next_year, dividend_per_share, twohundred_sma, trade_links, market_cap, todays_value_change_realtime, change_realtime, low_limit, volume, todays_high, ask_size, holdings_gain, shares_outstanding, holdings_gain_realtime, last_trade_price, notes, change_percent, change_percent_realtime, ticker_trend, todays_low, bid_realtime, change_50_sma, more_info, price_book, price_eps_estimate_current_year, order_book_realtime, todays_range, change_from_52_week_high, book_value, market_cap_realtime, dividend_yield, percent_change_from_52_week_high, holdings_gain_percent, stock_exchange, annualized_gain, high_limit, todays_value_change, short_ratio, company_name, trade_date, dividend_pay_date, change_200_sma, peg, bid_size, last_trade_time_plus, eps_estimate_next_quarter, eps_estimate_current_year, fiftytwo_week_range, fiftytwo_week_low, last_trade_date, holdings_value_realtime, ex_dividend_date, after_hours_change_realtime, price_sales, _change, eps_estimate_next_year, holdings_gain_percent_realtime, eps, one_year_target, last_trade_time, float_shares, change_percent_change, last_trade_size))
    cur.connection.commit()

name = raw_input('please input a symbol_')
data = ystockquote.get_all(name)
values = data.values
store(values)
print data.values() #this prints only the values - key : value

When I execute the program with the SQL code I get - NameError: global name 'cur' is not defined

When I execute without the SQL I get - [u'1.51', u'-61.03%', u'0.93', u'0.17', u'45700', u'"1.44 - 1.55"', u'1.44', u'1.55', u'N/A', u'-0.01', u'-1.37%', u'0.16', u'N/A', u'-0.02', u'-2.38', u'N/A', u'6.90M', u'-1.24', u'N/A', u'N/A', u'1.54', u'18616', u'441548', u'N/A', u'N/A', u'N/A', u'N/A', u'1.44', u'"NYQ"', u'N/A', u'"0.93 - 3.90"', u'N/A', u'"4:01pm - 1.52"', u'"Ciber', u'"+0.66%"', u'N/A', u'12200', u'N/A', u'N/A', u'-37.72%', u'N/A', u'2.84', u'N/A', u'N/A', u'N/A', u'0.59', u'N/A', u'122.63M', u'N/A', u'+63.44%', u'2.63', u'N/A', u'N/A', u'684998', u'1.52', u'-0.65', u'73289000', u'N/A', u'N/A', u'-0.92', u'0.53', u'80680000', u'N/A', u'N/A', u'N/A', u' Inc. Common Stock"', u'3.90', u'"6/13/2016"', u'-0.16', u'N/A', u'N/A', u'760.00M', u'+0.01', u'N/A', u'N/A', u'N/A', u'2.44', u'"4:01pm"', u'N/A', u'"+0.01 - +0.66%"', u'441548']

How can I get the above values into their corresponding columns in MySQL?

Aucun commentaire:

Enregistrer un commentaire