pymysql: insert query throws error - 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: insert query throws error (/thread-36154.html) Pages:
1
2
|
pymysql: insert query throws error - wardancer84 - Jan-21-2022 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 any tips welcome..wbr chris RE: pymysql: insert query throws error - wardancer84 - Jan-24-2022 nobody? RE: pymysql: insert query throws error - ibreeden - Jan-24-2022 Hi @wardancer84 , I have a lot of troubles to understand the logic of your program. I cannot see what data you are getting from the sudoers environment. But for a start: placeholders are for values, not column names. So this is wrong: INSERT INTO rules (?,?,?,?,?) VALUES (?,?,?,?,?)It should be: INSERT INTO rules (sudohost, users, hosts, commands, aixacodbt) VALUES (?,?,?,?,?)... assuming I understood it right that these are de column names of your "rules" table. Next problem: what the hell is this? Assuming "qry" contains the insert statement I proposed, you should do something like this:cursor.execute(qry, datatuple)... where datatuple is a tuple of exactly the five values to be inserted. It is possible to insert more than one row at a time. In that case you should use "executemany()" instead of "execute()" and you should provide a list of tuples, where each tuple again contains exactly the five values to be inserted. But there are more things I don't understand. for key in sudo_obj.host_aliases: host_alias_list = [] host_alias_list.append((key, list(sudo_obj.host_aliases[key]))) results['hostalias'].append(host_alias_list)Each iteration of the "for" loop you initialize "host_alias_list" to an empty list. Then you append something to that list. So "host_alias_list" will only hold the result of the last appended item. Is that what you want? RE: pymysql: insert query throws error - wardancer84 - Jan-24-2022 hi, thanks for answering. you are right, this needs more explaination. First, i use python-sudoers for parsing sudoers files. So this is part of the procedure of parsing various aspects of a sudoers file into python structures. for key in sudo_obj.host_aliases: host_alias_list = [] host_alias_list.append((key, list(sudo_obj.host_aliases[key]))) results['hostalias'].append(host_alias_list)this ist the output from the parsing library and represents a complete sudoers files. i added items= tablenames for clarification. [['aixacodbt']] = sudohost [[['root']]] = users [[['ALL']]] = hosts [[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]] = commandsthe next part prints out a complete insert statement... 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) <class 'str'> INSERT INTO rules (?,?,?,?) VALUES (?,?,?,?) sudohost,users,hosts,commands,[['aixacodbt']],[[['root']]],[[['ALL']]],[[(['root'], ['ALL'], [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}])]]updated original post with slightly modified script and outputs from a more simple sudoers file. wbr chris RE: pymysql: insert query throws error - ibreeden - Jan-24-2022 Thanks for the explanation. I now see the result of: qry = "INSERT INTO rules (%s) VALUES (%s)" % (qmarks, qmarks)... is: This is wrong. The first qmarks should be column_names so that the result would be: Then I see "qvals_stringed" contains: That is also wrong. It should contain a tuple with four elements like this: If you got that fixed you should be able to execute:cursor.execute(qry, qvals_stringed)... without error. RE: pymysql: insert query throws error - wardancer84 - Jan-24-2022 very helpful inputs...thanks for that. i changed the script according to your suggestions... #!/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() qcolname = ",".join(map(str, results.keys())) qmarks = ",".join('?' * len(results)) qry = "INSERT INTO rules (%s) VALUES (%s)" % (qcolname, qmarks) #qvals = ",".join(map(str, results.values())) qvals = list(results.values()) qvals_tupledx = tuple(qvals) #qvals_stringed = ','.join(map(str, qvals)) #print(qvals_stringed) print(type(qvals_tupledx)) print(qry, qvals_tupledx) #cursor.execute(qry, qvals_stringed) #connection.commit()output is now a tuple and the sql string looks more or less ok.. <class 'tuple'> INSERT INTO rules (sudohost,users,hosts,commands) VALUES (?,?,?,?) ([['aixacodbt']], [[['root']]], [[['ALL']]], [[(['root'], ['ALL'], [{'run_as': ['ALL'], 't ags': None, 'command': 'ALL'}])]])what puzzles me all time time with this kind of stuff...how to get rid of all those square brakets in the output as this looks not right. wbr chris RE: pymysql: insert query throws error - ibreeden - Jan-24-2022 (Jan-24-2022, 02:14 PM)wardancer84 Wrote: what puzzles me all time time with this kind of stuff...how to get rid of all those square brakets in the output as thisNo it does not look right. When I see [['aixacodbt']] I interpret it as a string in a list in a list. It needs unwrapping.a = [['aixacodbt']] b = a[0][0] b 'aixacodbt' print(b) aixacodbtYou could unwrap these data before adding them to the tuple, but why are they wrapped in the first place? You should attack the problem at the source. results = defaultdict(list) # This designates the values to be lists ... for key in sudo_obj.host_aliases: host_alias_list = [] host_alias_list.append((key, sudo_obj.host_aliases[key])) # host_alias_list is a list. Why double parentheses?? This would make a tuple of the contents of the list. Not useful in this stage. results['hostalias'].append(host_alias_list) # Here you append the list to the listYou should add print statements to test what exactly happens in these steps. I have the impression it would be sufficient to do this: for key in sudo_obj.host_aliases: results['hostalias'].append(sudo_obj.host_aliases[key])I also tried to understand the sudoers object so I followed your link to python-sudoers but the examples all start with "from pysudoers import Sudoers" while you are doing: "from SudoersLib import *". I find no manuals on the usage of SudoersLib so I can't help with that. RE: pymysql: insert query throws error - Pedroski55 - Jan-24-2022 Are you not overly complicating a simple matter? Admittedly, I am the only user on my laptop and I don't know much about this, but you are dealing with a simple text file. My /etc/sudoers file looks like this in nano: Quote:# What do you want to get from this? You could theoretically have 1 column for DEFAULTS and 5 columns for user, host, user_alias, group_alias, command in your db Quote:root ALL=(ALL:ALL) ALL Can't you just parse the text file to a dictionary and put in your db? RE: pymysql: insert query throws error - wardancer84 - Jan-25-2022 (Jan-24-2022, 11:55 PM)Pedroski55 Wrote: Are you not overly complicating a simple matter? might be, the thing is i have sudoers files with an insane linecount above 1000 with all variations/combinations of aliases and rules, so i would say the whole matter is kind if supercomplicated. RE: pymysql: insert query throws error - wardancer84 - Jan-25-2022 (Jan-24-2022, 06:52 PM)ibreeden Wrote: I also tried to understand the sudoers object so I followed your link to python-sudoers but the examples all start with "from pysudoers import Sudoers" while you are doing: "from SudoersLib import *". I find no manuals on the usage of SudoersLib so I can't help with that. its the same stuff, i just renamed and imported the file to make doing various edits easier. |