Python Forum
pymysql: formating ouput of query
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
pymysql: formating ouput of query
#11
code changed, still no inserts performed...

10/03/2018 04:35:27 PM insert:affected rows = 0
Reply
#12
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#13
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()
Reply
#14
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#15
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!
Reply
#16
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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#17
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.
Reply
#18
Wall Wall Wall Wall Wall
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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#19
works perfectly well!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Mysql Syntax error in pymysql ilknurg 4 2,287 May-18-2022, 06:50 AM
Last Post: ibreeden
  Formating generated .data file to XML malcoverc 3 1,315 Apr-14-2022, 09:41 PM
Last Post: malcoverc
  pymysql: insert query throws error wardancer84 12 4,417 Jan-28-2022, 06:48 AM
Last Post: wardancer84
  pymysql can't do SELECT * Pedroski55 3 2,899 Sep-11-2021, 10:18 PM
Last Post: Pedroski55
  propper formating paracelsusx 2 1,861 Jul-16-2021, 09:17 AM
Last Post: perfringo
  Adding graph points and formating project_science 4 2,345 Jan-24-2021, 05:02 PM
Last Post: project_science
  pymysql won't handle some diacritic characters awarren2001AD 0 1,235 Apr-16-2020, 08:58 AM
Last Post: awarren2001AD
  pyMySQL - ROW_NUMBER in SQL statement JayCee 1 2,339 Apr-12-2020, 08:40 PM
Last Post: JayCee
  pyMySQL How do I get the row id JayCee 3 2,667 Apr-12-2020, 08:38 PM
Last Post: JayCee
  PyMySQL return a single dictionary Valon1981 2 1,732 Feb-20-2020, 04:07 PM
Last Post: Valon1981

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020