Python Forum

hi,

im stuck at the mentioned error. guess its easily fixable, but i cant get my head around it.

#!/usr/bin/env python

'''
populates ansible inventory db from
aix registry db
'''

# -*- coding: utf-8 -*-

import os
import sys
import pymysql as db
try:
    import json
    from json import JSONDecodeError
except ImportError:
    import simplejson as json
    from simplejson import JSONDecodeError

connections = {
    'conn1': db.connect('remotehost', 'aix_regread', '***', 'aix_registry', charset='utf8'),
    'conn2': db.connect('localhost', 'ansible', '***', 'ansible_inv', charset='utf8')
}

c1 = connections['conn1'].cursor()
c2 = connections['conn2'].cursor()

try:
    sql = """SELECT DISTINCT NODE,IP FROM ansible_hosts_view;"""
    c1.execute(sql)
    sql = """SELECT DISTINCT NODE,IP FROM ansible_hosts_view;"""
    c2.execute(sql)
    diff_to_del = (list(set(c2.fetchall()) - set(c1.fetchall())))
    for idx,row in enumerate(diff_to_del, start=1):
        host, hostname = row
        sql = """DELETE FROM host WHERE  host='{}';""".format(host)
        c2.execute(sql % (host))
    print("delete:affected rows = {}".format(idx))
    for idx,row in enumerate(c1, start=1):
        host, hostname = row
        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))
    print("insert:affected rows = {}".format(idx))
except db.Error,e:
    print e[0], e[1]
    connections['conn2'].rollback()
    c1.close()
    c2.close()
    [conn.close() for key, conn in connections.items()]

connections['conn2'].commit()
c1.close()
c2.close()
[conn.close() for key, conn in connections.items()]

error:

Traceback (most recent call last):
File "./reg2inv.py", line 38, in <module>
print("delete:affected rows = {}".format(idx))
NameError: name 'idx' is not defined

any hints?

It seems in both cases (line 38 and 47), the print lines should be indented so that they are inside their respective for loops.

(Sep-17-2018, 09:30 AM)wardancer84 Wrote: [ -> ]

#!/usr/bin/env python
....
    for idx,row in enumerate(diff_to_del, start=1):
        host, hostname = row
        sql = """DELETE FROM host WHERE  host='{}';""".format(host)
        c2.execute(sql % (host))
    print("delete:affected rows = {}".format(idx))

error:

Traceback (most recent call last):
File "./reg2inv.py", line 38, in <module>
print("delete:affected rows = {}".format(idx))
NameError: name 'idx' is not defined

any hints?

Looks like diff_to_del is an empty sequence, and you loop is not entered, leaving idx undefined. Pre-set it to 0

(Sep-17-2018, 10:13 AM)volcano63 Wrote: [ -> ]

(Sep-17-2018, 09:30 AM)wardancer84 Wrote: [ -> ]

#!/usr/bin/env python
....
    for idx,row in enumerate(diff_to_del, start=1):
        host, hostname = row
        sql = """DELETE FROM host WHERE  host='{}';""".format(host)
        c2.execute(sql % (host))
    print("delete:affected rows = {}".format(idx))

error:

Traceback (most recent call last):
File "./reg2inv.py", line 38, in <module>
print("delete:affected rows = {}".format(idx))
NameError: name 'idx' is not defined

any hints?

Looks like diff_to_del is an empty sequence, and you loop is not entered, leaving idx undefined. Pre-set it to 0

this solved my issue, thank you very much!

root@lpgaixmgmtlx01:/etc/ansible/aix/dyninv>./reg2inv.py
delete:affected rows = 0
insert:affected rows = 370

Glad to be of help

I was looking back at your code - couple of pointers about this one

[conn.close() for key, conn in connections.items()]

• You can iterate over connections.values()
  
• If you iterate over a list of lists/tuples and you want to skip some values - you may leave them unnamed, using multiples of undescore(1 for first, 2 for second, etc.), like for _, conn
  
• Using a list comprehension instead of a cycle to call functions/methods is unhealthy - you create a parasitic list in memory, and in a big data environment, you may actually cause a harmful resource consumption - you are building as a side effect a list which is thrown away (IMO, it is also un-Pythonic - since you abuse the mechanism)
  

  for conn in connections.items():
      connn.close()

  probably looks less sexy - but this is the proper way