New programmer needs help putting a list through a function or for loop

Python Help
1

I am a pretty new programmer. I have a list of values that correlate to a sql database and I need to create a function that goes through each Id and analyzed it and saves the max value to another database but my for loop goes through all the contracts instead of ending

2

I can’t understand the problem. If you want to find the maximum value, shouldn’t that involve looking at every value? If you stop part way, how could you be sure that there isn’t a greater value, in the part that wasn’t looked at yet?

3

Each contract that I’m going through has 20000 rows and I have to find the highest z score but the code goes through all the contracts making the z score become impossible to calculate

4

Could you please show a small example of what the database looks like (the part that you actually queried for); show the code that you tried so far; and explain exactly what should happen when you run this code, exactly what does happen instead, and how that is different?

2 Likes
5

Welcome.

If you can recognise when you’ve finished, then maybe the break statement is what you need? It’s the way to leave a loop early.

Or if I’ve misunderstood, post a simple code example, and we’ll know what you’re talking about. When you post code, be sure to follow this advice.

1 Like
6 
Contract_list = [contracta, etc]

For x in contract_list:
       qry = “SELECT * FROM … WHERE CONTRACT_ID = “
        Var = qry + x
        Contract_x = pd.read_sql(var,cnxn)

And I have some code under it but if I printed contract_x right there it pulls all the code already. And sorry if formatting is bad I’m on mobile

7

I have to find the zscore for each contract then go to the next but it pulls all the contracts right away

8 
Contract_list = [contracta, etc]

For x in contract_list:
       qry = “SELECT * FROM … WHERE CONTRACT_ID = “
        Var = qry + x
        Contract_x = pd.read_sql(var,cnxn)
9

Guessing pd is pandas.

If x is a string, should it be quoted in your query?

10

It pulls the data fine

11

Just to be clear, AIUI the table contains rows, each labeled with a contract id in the column CONTRACT_ID, and what you’re expecting back is a pandas data frame with all the rows for a given contract id. There’s another column you’re calling “z score” that you will get pandas to add up. But the query seems to return all the rows, not just those matching the contract id.

The code as posted is wrong (indenting, capitalisation, undefined variables, kind of quotes) which makes it a bit difficult to evaluate, but let’s say that’s all down to the device you’re stuck with.

The help pandas.read_sql — pandas 2.1.0 documentation hints that you should be using read_sql_query but that doesn’t seem fundamental.

The problem should occur irrespective of the loop, so I would try just one x value, print the query to see it is really saying what you need it to, then is there another way to check that query does what you think?

If that doesn’t diagnose it, I’m stumped because if that all works it suggests the function doesn’t work, which seems unlikely.

12

Don’t build queries with string concatenation or string formatting; use a parameterised query :

qry = "SELECT * FROM … WHERE CONTRACT_ID = %s"

for x in contract_list:
    contract_x = pd.read_sql(var, cnxn, params=(x,))

The %s in the query acts as a placeholder. Some database engines use %s and others use ?.