sql - Update PostgreSQL database with daily stock prices in Python -

so found great script on @ quantstate had great walk-through on setting own securities database , loading in historical pricing information. however, i'm not trying modify script can run daily , have latest stock quotes added.

i adjusted initial data load download 1 week worth of historicals, i've been having issues writing sql statement see if row exists before adding. can me out this. here's have far:

def insert_daily_data_into_db(data_vendor_id, symbol_id, daily_data):   """takes list of tuples of daily data , adds    database. appends vendor id , symbol id data.    daily_data: list of tuples of ohlc data (with    adj_close , volume)"""    # create time   = datetime.datetime.utcnow()    # amend data include vendor id , symbol id   daily_data = [(data_vendor_id, symbol_id, d[0], now, now,     d[1], d[2], d[3], d[4], d[5], d[6]) d in daily_data]    # create insert strings   column_str = """data_vendor_id, symbol_id, price_date, created_date,            last_updated_date, open_price, high_price, low_price,            close_price, volume, adj_close_price"""   insert_str = ("%s, " * 11)[:-2]   final_str = "insert daily_price (%s) values (%s) not exists (select 1 daily_price symbol_id = symbol_id , price_date = insert_str[2])" % (column_str, insert_str)    # using postgre connection, carry out insert every symbol   con:      cur = con.cursor()     cur.executemany(final_str, daily_data) 

some notes regarding code above:

it's easier defer now() in pure sql try in python whenever possible. avoids lots of potential pitfalls timezones, library differences, etc.

if construct list of columns, can dynamically generate string of %s's based on size, , don't need hardcode length repeated string sliced.

since appears insert_daily_data_into_db meant called within loop on per-stock basis, don't believe want use executemany here, require list of tuples , different semantically.

you comparing symbol_id in sub select, instead of particular value (which mean it's true).

to prevent possible sql injection, should interpolate values in where clause, including sub selects.

note: i'm assuming you're using psycopg2 access postgres, , primary key table tuple of (symbol_id, price_date). if not, code below need tweaked @ least bit.

with points in mind, try (untested, since don't have data, db, etc. syntactically valid python):

def insert_daily_data_into_db(data_vendor_id, symbol_id, daily_data):     """takes list of tuples of daily data , adds     database. appends vendor id , symbol id data.      daily_data: list of tuples of ohlc data (with     adj_close , volume)"""       column_list = ["data_vendor_id", "symbol_id", "price_date", "created_date",                     "last_updated_date", "open_price", "high_price", "low_price",                     "close_price", "volume", "adj_close_price"]      insert_list = ['%s'] * len(column_str)      values_tuple = (data_vendor_id, symbol_id, daily_data[0], 'now()', 'now()', daily_data[1],                     daily_data[2], daily_data[3], daily_data[4], daily_data[5], daily_data[6])      final_str = """insert daily_price ({0})                      values ({1})                      not exists (select 1                                        daily_price                                        symbol_id = %s                                          , price_date = %s)""".format(', '.join(column_list), ', '.join(insert_list))      # using postgre connection, carry out insert every symbol     con:         cur = con.cursor()         cur.execute(final_str, values_tuple, values_tuple[1], values_tuple[2])