pymysql: formating ouput of query - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: pymysql: formating ouput of query (/thread-13193.html) Pages:
1
2
|
RE: pymysql: formating ouput of query - wardancer84 - Oct-03-2018 code changed, still no inserts performed... 10/03/2018 04:35:27 PM insert:affected rows = 0 RE: pymysql: formating ouput of query - buran - Oct-03-2018 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 RE: pymysql: formating ouput of query - wardancer84 - Oct-04-2018 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 restartedactual 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() RE: pymysql: formating ouput of query - buran - Oct-04-2018 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 RE: pymysql: formating ouput of query - wardancer84 - Oct-04-2018 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! RE: pymysql: formating ouput of query - buran - Oct-04-2018 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. RE: pymysql: formating ouput of query - wardancer84 - Oct-04-2018 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. RE: pymysql: formating ouput of query - buran - Oct-04-2018 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 RE: pymysql: formating ouput of query - wardancer84 - Oct-04-2018 works perfectly well! |