pymysql: formating ouput of query

[wardancer84]

i let it run through the pdb debugger and the insert part is simply not executed. quite strange.

(Pdb) n
> /etc/ansible/aix/dyninv/reg2inv.py(55)<module>()
-> logger.debug("hosts to insert: {}".format(csv_hosts_nim))
(Pdb) n
> /etc/ansible/aix/dyninv/reg2inv.py(56)<module>()
-> logger.debug("insert:affected rows = {}".format(idx))
(Pdb) n
> /etc/ansible/aix/dyninv/reg2inv.py(63)<module>()
-> conn.commit()
(Pdb) n
> /etc/ansible/aix/dyninv/reg2inv.py(64)<module>()
-> c2.close()
(Pdb) n
> /etc/ansible/aix/dyninv/reg2inv.py(65)<module>()
-> conn.close()
(Pdb) n
--Return--
> /etc/ansible/aix/dyninv/reg2inv.py(65)<module>()->None
-> conn.close()
(Pdb) n
--Return--
> <string>(1)<module>()->None
(Pdb) n
The program finished and will be restarted

actual code

#!/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[3]
        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()