pymysql: formating ouput of query

[wardancer84]

code changed, still no inserts performed...

10/03/2018 04:35:27 PM insert:affected rows = 0

[buran]

It's possible I have mistake in my code, but it's also possible you implemented the change wrong. In any case it's difficult to debug. Please, post the code, without it I don't see what else I could suggest

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

[buran]

1. csv_data is tuple of 2-element tuples, so line 48 should be
hostname = row[1]

as to debug why it does not execute insert statement I would suggest try to print csv_data after it is created, then print again just before line 46

[wardancer84]

tata...

working version

#!/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))
    rdr = unicodecsv.reader(file('ansible_load_file.txt'), encoding='utf-8')
    csv_data = ((row[0], row[3]) for row in rdr)
    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()

thank you very much for your help!

[buran]

That doesn't make sense - the try/except should not catch the IndexError. You did not report any error logs (i.e. the output at line 60)....Anyway glad it work, but I think there is some hidden problem present in the code.

[wardancer84]

you might be right, because "csv_data" is not printable, and it is not what it seems to be.

a print of "csv_data" yields this -> <generator object <genexpr> at 0x3fff75314820>
beside this, "csv_hosts_nim", which is the same kind of structure is perfectly printable.
all in all, quite strange stuff.

[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

[wardancer84]

works perfectly well!