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()

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? — calico_
– calico_, Commented Jun 2, 2017 at 7:30

mata · Accepted Answer · 2017-06-02 09:57:44Z

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

Collectives™ on Stack Overflow

Python Script to Execute SQL in Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related