Python Script to Execute SQL in Loop

Question

I am trying to write python script which calls mysql SQL.

I am relying on first query result to run second query. Problem is that it's throwing error.

Error : cursor2.execute(field_sql,id)

import boto3
import importlib
import psutil
import pymysql
import pymysql.cursors
import subprocess
import sys
rdsConn = pymysql.connect(host = 'XXXX'),
 db = 'XXXX',
 user = 'XXXX',
 password = 'XXXX',
 charset = 'utf8mb4',
 cursorclass=pymysql.cursors.DictCursor)
cursor1 = rdsConn.cursor()
cursor2 = rdsConn.cursor()
name = 'Test'
sql = "select id from Table1 where name = %s"
cursor1.execute(sql,name)
result = cursor1.fetchall()
for id in result:
 field_sql= "select columnname from Table2 where id = %s"
 cursor2.execute(field_sql,id)
 fieldresult = cursor2.fetchall()
 for fieldrow in fieldresult:
 print(fieldrow)
cursor1.close()
cursor2.close()
rdsConn.close()

• mata: What error exactly? Please post the stacktrace.
• calico_: Have you tried looking at the results you get from the first query? It returns a dict, so your for loop will be iterating over the keys, but maybe you want the values instead?

Answer 1

Your query uses a dict cursor, so it will return a list of dicts, e.g:

[{'id': 1}, {'id': '2'}, ...]

Which means your id* will be a dict, not a tuple as it would be otherwise. Which means you're passing your arguments to the second query as a dict. If you do so, you need to use named parameters using the pyformat style:

for rowdict in result:
 field_sql = "select columnname from Table2 where id = %(id)s"
 cursor2.execute(field_sql, rowdict)
 fieldresult = cursor2.fetchall()
 for fieldrow in fieldresult:
 print(fieldrow)

You'll see that the printed fieldrows are also dicts.

Also, query parameters should be passed either as dict (named parameters) or as tuple (positional parameters). pymysql accepts the form cursor.execute(sql, "name"), other dbapi2 connectors don't. The canonical form would be cursor.execute(sql, ("name",)).

*btw, you shouldn't use id as name, it hides the builtin id function