pymysql: formating ouput of query

[buran]

csv_data is generator object and that's why it doesn't work. My mistake, sorry. It's exhausted when we iterate over it on the next line and that's why it is empty later on. It worked because you created it again.

make csv_data a list, like this:

#!/usr/bin/env python
 
'''
populates ansible inventory db from
aix registry db
'''
 
# -*- coding: utf-8 -*-
 
import os
import sys
import unicodecsv
import pymysql as db
try:
    import json
    from json import JSONDecodeError
except ImportError:
    import simplejson as json
    from simplejson import JSONDecodeError
import logging
logging.basicConfig(filename='/tmp/reg2inv.log',level=logging.DEBUG, format='%(asctime)s %(message)s', datefmt='%m/%d/%Y %I:%M:%S %p')
logger = logging.getLogger(__name__)
 
 
conn = db.connect(host='localhost', user='ansible', passwd='ansible', db='ansible_inv', charset='utf8')
 
c2 = conn.cursor()
 
try:
    rdr = unicodecsv.reader(file('ansible_load_file.txt'), encoding='utf-8')
    csv_data = [(row[0], row[3]) for row in rdr]
    csv_hosts_nim = {row[0] for row in csv_data}
    sql = """SELECT DISTINCT NODE FROM ansible_hosts_view;"""
    c2.execute(sql)
    lresult = {item[0] for item in c2.fetchall()}
    print("csv_hosts_nim contains:", csv_hosts_nim)
    diff_to_del = list(lresult.difference(csv_hosts_nim))
    print("diff_to_del contains:", diff_to_del)
    idx = 0
    for idx,row in enumerate(diff_to_del, start=1):
        host = row
        sql = """DELETE FROM host WHERE  host='%s';""".format(host)
        c2.execute(sql % (host))
    logger.debug("hosts to delete: {}".format(diff_to_del))
    logger.debug("delete:affected rows = {}".format(idx))
    for idx,row in enumerate(csv_data, start=1):
        host = row[0]
        hostname = row[1]
        var_data = {'inventory_lparname': host}
        var_json = json.dumps(var_data)
        ena = "1"
        sql = """INSERT INTO host(host, hostname, variables, enabled) VALUES ('%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE
        host='{}', hostname='{}',variables='{}',enabled='{}';""".format(host, hostname, var_json, ena)
        c2.execute(sql % (host, hostname, var_json, ena))
    logger.debug("hosts to insert: {}".format(csv_hosts_nim))
    logger.debug("insert:affected rows = {}".format(idx))
except db.Error,e:
    print e[0], e[1]
    conn.rollback()
    c2.close()
    conn.close()
 
conn.commit()
c2.close()
conn.close()

and csv_hosts_nim is/was not same structure - it's a set object