0

I have this query, it works great and does exactly what I want it to do. Pull stock data for companies given the users input, symbols:

stock_info = {}    
for stock in symbols:
        stock_info[stock] = get_dict_resultset("SELECT 
                                                date, close          
                                                FROM security_price 
                                                WHERE 
                                                security_price.id=%s;", [stock])

get_dict_resultset() connects to my postgreSQL db, pulls data, and stores it into a python dictionary. Here is the definition:

def get_dict_resultset(query, param):
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute(query, param)
    ans =cur.fetchall()
    dict_result = []
    for row in ans:
        dict_result.append(dict(row))
    return dict_result

What I don't like about my query, is the for loop that it's nested in. I want to limit the amount of transactions happening with my database and the user. Given the amount of values in symbols, it seems a bit silly having it in a for loop and iterating the same calls to the dB when I could just use one simple statement.

I've tried playing around with executemany and execute_batch, but struggling to implement that into my code above. How can I structure my query without using a for-loop? I've already sanitized my inputs, just trying to implement this one last step!

andres
  • 1,558
  • 7
  • 24
  • 62
  • 2
    Use `IN`, check this -> https://stackoverflow.com/a/10738459/8150371 – Stack Oct 01 '20 at 16:30
  • I pointed you in wrong direction [here](https://stackoverflow.com/questions/64146325/how-can-i-fix-my-python-f-string-in-my-query-to-not-be-vulnerable-to-sql-injecti/64147623?noredirect=1#comment113435407_64147623). `executemany` and `execute_batch` don't work well with `SELECT` as they don't return the results. You would have to do another query to get them. – Adrian Klaver Oct 01 '20 at 16:56

1 Answers1

1

As an example:

import psycopg2
con = psycopg2.connect("dbname='test' host='localhost' user=aklaver")
cur = con.cursor()
cp = (8, 18, 32)
sql_str = "select * from cell_per where cell_per in %s"
cur.execute(sql_str, [cp])
rs = cur.fetchall()
rs
[('H PREM 3.5', 18),
 ('HERB 2.5', 32),
 ('H PREM 2.5', 32),
 ('GER SC 3.5', 18),
 ('VEG OPK', 8),
 ('SUCCULENTS', 18)]

So in your case get the stock symbols into a tuple and then use that as the argument to the parameter(in %s).

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Thank you for snippet! The links you shared to me were very useful, no harm done on the previous post. The only problem is I now need to re-design my dictionary as the for-loop helped me build it. – andres Oct 01 '20 at 18:48
  • If I'm following using a [real dict cursor](https://www.psycopg.org/docs/extras.html#real-dictionary-cursor) should do that for you in the `rs = cur.fetchall()` part. In that it will return a `list` of `dict`s. – Adrian Klaver Oct 01 '20 at 19:13
  • Awesome, I used the real dict cursor and I'm almost there. Wondering if you know why this line of code: `my_dict = {d["symbol"]: d for d in stock_info}` does not retrieve all the rows of data, it's only giving me the last row of data. – andres Oct 01 '20 at 20:07
  • Without knowing the structure of `stock_info` it is hard to say. Though at a guess `d["symbol"]` is returning the same symbol for all instances of `d`. Since dict keys have to be unique, later keys that are the same as earlier will overwrite the earlier and you will end up with only one `key: value`. – Adrian Klaver Oct 02 '20 at 00:36