pymysql: insert query throws error

[wardancer84]

hi,

i',m trying to parse sudo files and inserting the results into a mysql table for eaysier analyzing an so on. parsing works well sof far, but pymysql will not let me do the insert.

the script:

#!/usr/bin/env python3


import sys
import os
import pprint
from collections import defaultdict
import pymysql
import pymysql.cursors
from SudoersLib import *

input_path="/admin/sudoers"
infiles= os.listdir(input_path)

connection = pymysql.connect(host='localhost',
                             user='sudodb',
                             password='sudodb',
                             database='sudoersdb',
                             cursorclass=pymysql.cursors.DictCursor)


for infile in infiles:
    infile=os.path.join(input_path, infile)
    sudo_obj = Sudoers(path=infile)
    file_name = os.path.basename(infile)
    sudohost = file_name.split('-')[1]
    results = defaultdict(list)
    sudo_host_list = []
    sudo_host_list.append(sudohost)
    results['sudohost'].append(sudo_host_list)

    for key in sudo_obj.host_aliases:
        host_alias_list = []
        host_alias_list.append((key, sudo_obj.host_aliases[key]))
        results['hostalias'].append(host_alias_list)
    for key in sudo_obj.cmnd_aliases:
        cmnd_alias_list = []
        cmnd_alias_list.append((key, sudo_obj.cmnd_aliases[key]))
        results['cmndalias'].append(cmnd_alias_list)
    for key in sudo_obj.runas_aliases:
        runas_alias_list = []
        runas_alias_list.append((key, sudo_obj.runas_aliases[key]))
        results['runasalias'].append(runas_alias_list)
    for key in sudo_obj.user_aliases:
        user_alias_list = []
        user_alias_list.append((key, sudo_obj.user_aliases[key]))
        results['useralias'].append(user_alias_list)
    for rule in sudo_obj.rules:
        users_list = []
        hosts_list = []
        rules_list = []
        users_list.append((rule["users"]))
        results['users'].append(users_list)
        hosts_list.append((rule["hosts"]))
        results['hosts'].append(hosts_list)
        rules_list.append((rule["users"], rule["hosts"], (rule["commands"])))
        results['commands'].append(rules_list)
        #print("%s|%s|%s|%s" % (sudohost, ",".join(rule["users"]), ",".join(rule["hosts"]), ",".join(map(str, rule["commands"]))))

        cursor = connection.cursor()
        qmarks = ",".join('?' * len(results))
        qry = "INSERT INTO rules (%s) VALUES (%s)" % (qmarks, qmarks)
        qvals = *results.keys(), *results.values()
        qvals_stringed = ','.join(map(str, qvals))
        print(type(qvals_stringed))
        print(qry, qvals_stringed)
        cursor.execute(qry, qvals_stringed)
        connection.commit()

example insert line printout:

<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]

root@nimvie: /home/tremch/scripts # ./sudoers_parse.py
<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]
Traceback (most recent call last):
  File "./sudoers_parse.py", line 67, in <module>
    cursor.execute(qry, qvals_stringed)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

Error:
<class 'str'>
INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]
Traceback (most recent call last):
  File "./sudoers_parse.py", line 67, in <module>
    cursor.execute(qry, qvals_stringed)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 161, in execute
    query = self.mogrify(query, args)
  File "/opt/freeware/lib/python3.7/site-packages/pymysql/cursors.py", line 140, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

any tips welcome..

wbr

chris