Python Script to run SQL query UPDATE statement to loop through each row in result set and update co-CodePudding

Newby working on my first project. Sorry for this explanation.

I have 2 x tables: t1: master table with single rows (unique project-id) and 3 status fields, s1,s2,s3

t2: list table with repeating project_id's with 3 status fields s1,s2,s3 (and other data not relevant here). The value in the s1-3 fields is either true(1) or false(0)

table1: project_id, status1, status2, status3

table2: recordid, project_id, name, status1, status2, status3

I am using Python to run mysql queries.

I want to loop through the rows in t1 which has unique record project_id, then with each t1.project_id query t2 join t1.projectid and update t1 status fields from t2 last status entry for each project/status field.

So far I have done this but cant pass the id to query 2. Again, sorry, I really am hacking my way through this trying to learn something along the way.

import mysql.connector as msql
from mysql.connector import Error

Host = "192.168.0.10"
Port = 3306
User = ""       
Password = ""           
database = "projectdata"

conn = msql.connect(host=Host,port=Port, user=User, password=Password, database=database)
cursor1 = conn.cursor() 
cursor2 = conn.cursor()

sql = ("select id from table1 t1") cursor1.execute(sql) 
t1 = cursor1.fetchall()

for row in t1:

sql2 = ("SELECT recordid,project_id, s1, s2, s3
FROM table2 t2
WHERE t2.project_id = 't1.project_id'
ORDER by id, recordid DESC LIMIT 1")

cursor2.execute(sql) t2 = cursor2.fetchall()

I havent got to the update t1 part as I havent worked out how to do the loop through t1 and query t2 for the results to update t1.

t1 before update
|project_id |s1 |s2 |s3 |
|-----------|---|---|---|
|1003       |   |   |   |
|1005       |   |   |   |
|1001       |   |   |   |
|1002       |   |   |   |

t2 example 
|recordid   |project_id |s1         |s2         |s3         |
|-----------|-----------|-----------|-----------|-----------|
|1          |1001       |           |0          |1          |
|2          |1002       |1          |           |0          |
|3          |1003       |           |1          |           |
|4          |1001       |0          |           |           |
|5          |1002       |0          |0          |           |
|6          |1005       |           |1          |           |
|7          |1003       |1          |           |1          |

t1 example of desired result. The live status field is the last status change for that specific project_id sorted by recordid of t2

t1 after update
|project_id |s1         |s2         |s3         |
|-----------|-----------|-----------|-----------|
|1003       |1          |1          |1          |
|1005       |null       |1          |null       |
|1001       |0          |0          |1          |
|1002       |0          |0          |null       |

Thanks in advance and sorry, newby. I could do it using vb and msAccess queries but need to learn a better way than using msAccess.

CodePudding user response：

if I understand you correctly you want to give the id dynamically. To achive that you need to use placeholders.

sql2 = ("SELECT recordid,project_id, s1, s2, s3
FROM table2 t2
WHERE t2.project_id = %s and s1 = '0' or '1'
ORDER by id, recordid DESC LIMIT 1")

You set the part with as variable as %s placeholder. Then you need to build a list with the variable in order. In your case it seems to be only the id.

 placeholder = [row[1]]

Then you give the cusor the statement and the placeholder list.

 cursor1.execute(sql,placeholder)

Hope it helps!

--------------------------Loop through result of query1------------

I cant comment so I need to do it like this. If there are structure changes planned or possible it would be better to use pandas, like you said, since the dataframes are more like dicts and you wouldnt need to use index.

r1 = cursor1.fetchall()
for row in r1:
  sql2 = ("SELECT recordid,project_id, s1, s2, s3
              FROM table2 t2
              WHERE t2.project_id = %s
              ORDER by id, recordid DESC LIMIT 1")
   id = [row[0]]
   cursor2.execute(sql2,id)
   cursor2.commit 
   update = ("UPDATE t1
              set s1 = %s, s2 = %s, s3 = %s 
              where project_id = %s")
   line = cursor2.fetchone()
   upt_data = [line[2],line[3],line[4],line[1],]
   cursor3.execute(update,upt_data)
   cursor3.commit