Python Forum
pymysql: insert query throws error
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
pymysql: insert query throws error
#11
Too many sudoers spoil the broth!
Reply
#12
Hi @wardancer84 ,
I had some time left and got interested in your problem. I also found a sudoers file on my laptop. It only contained "rules", so I played around with it.
The important thing with relational databases is that the data is organized in tables and tables have columns. Column types can only be string, numeric or date (and some other special types). So the first step must be to break down your data in these elementary types. You cannot just insert a dictionary in a text column. Breaking down the data can be done as follows.
from pysudoers import Sudoers

# Variables for print_nested().
level = 0
tab = "\t"

def print_nested(x: object) -> None:
    """ Print the data of a nested data stucture so it can be understood. """
    global level
    if isinstance(x, str):
        print(f"{tab * level}{x}")
    elif isinstance(x, list):
        print(f"{tab * level}LIST:")
        level += 1
        for i in x:
            print_nested(i)
        level -= 1
    elif isinstance(x, dict):
        print(f"{tab * level}DICT:")
        level += 1
        for k, v in x.items():
            print_nested(f"{k} (key)")
            level += 1
            print_nested(v)
            level -= 1
        level -= 1
    elif x == None:
        print_nested("None")
    else:
        print(f"Unknown type: {type(x)}")

sobj = Sudoers(path="/home/ibreeden/tmp/sudoers-mylaptop")

print("Rules")
for rule in sobj.rules:
    print("DEBUG: sudoers-rule", rule)
    print("Decomposition:")
    print_nested(rule)
Output:
Rules DEBUG: sudoers-rule {'users': ['root'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL', 'ALL'], 'tags': None, 'command': 'ALL'}]} Decomposition: DICT: users (key) LIST: root hosts (key) LIST: ALL commands (key) LIST: DICT: run_as (key) LIST: ALL ALL tags (key) None command (key) ALL DEBUG: sudoers-rule {'users': ['%admin'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}]} Decomposition: DICT: users (key) LIST: %admin hosts (key) LIST: ALL commands (key) LIST: DICT: run_as (key) LIST: ALL tags (key) None command (key) ALL DEBUG: sudoers-rule {'users': ['%sudo'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL', 'ALL'], 'tags': None, 'command': 'ALL'}]} Decomposition: DICT: users (key) LIST: %sudo hosts (key) LIST: ALL commands (key) LIST: DICT: run_as (key) LIST: ALL ALL tags (key) None command (key) ALL Process finished with exit code 0
Here we learn there are only string and None values. So we can design a table with these column types.
You must realize this is only an example. You should really design your datamodel into more tables, related with foreign keys. Where you see a list or a dictionary in the breakdown is a candidate for a new table. It also depends on how you will be going to query the database.
But to keep the example simple, I created one simple table, based on the break-down. And then the data can be inserted in that table.
from pysudoers import Sudoers
import sqlite3


def do_insert(tablename: str, columnnames: list, columnvalues: list) -> None:
    todo = "insert into " + tablename + " ("
    todo += ", ".join(columnnames)
    todo += ") values ("
    todo += ", ".join('?' * len(columnnames))
    todo += ")"
    colvalues = tuple(columnvalues)
    print("DEBUG: ", todo, colvalues)
    cur.execute(todo, colvalues)

def insert_nested(x: object, colnames: list = [], colvals: list = []) -> None:
    """ generate sql inserts for nested data stucture so it can be inserted in a table. """
    ccolnames = colnames[:] # Deep copy.
    ccolvals = colvals[:]   # Deep copy.
    if isinstance(x, str):
        ccolvals.append(x)
        do_insert("sudo_rules", ccolnames, ccolvals)
    elif isinstance(x, list):
        for i in x:
            insert_nested(i, ccolnames, ccolvals)
    elif isinstance(x, dict):
        for k, v in x.items():
            if len(ccolnames) == len(ccolvals):
                insert_nested(v, ccolnames + [k], ccolvals)
            else:
                insert_nested(v, ccolnames + [k], ccolvals + [k])
    elif x == None:
        insert_nested("NULL", ccolnames, ccolvals)
    else:
        print(f"Unknown type: {type(x)}")


con = sqlite3.connect("sudoers.db")
cur = con.cursor()
# CREATE THE TABLE (ONCE).
cur.execute("""create table if not exists sudo_rules
        (sudohost text, 
        users text, 
        hosts text, 
        commands text, 
        run_as text, 
        tags text, 
        command text) """)
cur.execute("delete from sudo_rules")   # For testing, start with blank table.
con.commit()

sobj = Sudoers(path="/tmp/sudoers-mylaptop")

print("Rules")
for rule in sobj.rules:
    print("DEBUG: sudoers-rule: ", rule)
    insert_nested(rule, ["sudohost"], ["mylaptop"])
con.commit()

print()
print("Contents of table")
cur.execute("select * from sudo_rules")
table = cur.fetchall()
for row in table:
    print(row)
cur.close()
con.close()
Output:
Rules DEBUG: sudoers-rule: {'users': ['root'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL', 'ALL'], 'tags': None, 'command': 'ALL'}]} DEBUG: insert into sudo_rules (sudohost, users) values (?, ?) ('mylaptop', 'root') DEBUG: insert into sudo_rules (sudohost, hosts) values (?, ?) ('mylaptop', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, run_as) values (?, ?, ?) ('mylaptop', 'run_as', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, run_as) values (?, ?, ?) ('mylaptop', 'run_as', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, tags) values (?, ?, ?) ('mylaptop', 'tags', 'NULL') DEBUG: insert into sudo_rules (sudohost, commands, command) values (?, ?, ?) ('mylaptop', 'command', 'ALL') DEBUG: sudoers-rule: {'users': ['%admin'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL'], 'tags': None, 'command': 'ALL'}]} DEBUG: insert into sudo_rules (sudohost, users) values (?, ?) ('mylaptop', '%admin') DEBUG: insert into sudo_rules (sudohost, hosts) values (?, ?) ('mylaptop', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, run_as) values (?, ?, ?) ('mylaptop', 'run_as', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, tags) values (?, ?, ?) ('mylaptop', 'tags', 'NULL') DEBUG: insert into sudo_rules (sudohost, commands, command) values (?, ?, ?) ('mylaptop', 'command', 'ALL') DEBUG: sudoers-rule: {'users': ['%sudo'], 'hosts': ['ALL'], 'commands': [{'run_as': ['ALL', 'ALL'], 'tags': None, 'command': 'ALL'}]} DEBUG: insert into sudo_rules (sudohost, users) values (?, ?) ('mylaptop', '%sudo') DEBUG: insert into sudo_rules (sudohost, hosts) values (?, ?) ('mylaptop', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, run_as) values (?, ?, ?) ('mylaptop', 'run_as', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, run_as) values (?, ?, ?) ('mylaptop', 'run_as', 'ALL') DEBUG: insert into sudo_rules (sudohost, commands, tags) values (?, ?, ?) ('mylaptop', 'tags', 'NULL') DEBUG: insert into sudo_rules (sudohost, commands, command) values (?, ?, ?) ('mylaptop', 'command', 'ALL') Contents of table ('mylaptop', 'root', None, None, None, None, None) ('mylaptop', None, 'ALL', None, None, None, None) ('mylaptop', None, None, 'run_as', 'ALL', None, None) ('mylaptop', None, None, 'run_as', 'ALL', None, None) ('mylaptop', None, None, 'tags', None, 'NULL', None) ('mylaptop', None, None, 'command', None, None, 'ALL') ('mylaptop', '%admin', None, None, None, None, None) ('mylaptop', None, 'ALL', None, None, None, None) ('mylaptop', None, None, 'run_as', 'ALL', None, None) ('mylaptop', None, None, 'tags', None, 'NULL', None) ('mylaptop', None, None, 'command', None, None, 'ALL') ('mylaptop', '%sudo', None, None, None, None, None) ('mylaptop', None, 'ALL', None, None, None, None) ('mylaptop', None, None, 'run_as', 'ALL', None, None) ('mylaptop', None, None, 'run_as', 'ALL', None, None) ('mylaptop', None, None, 'tags', None, 'NULL', None) ('mylaptop', None, None, 'command', None, None, 'ALL') Process finished with exit code 0
I hope this helps you with your problem.
Reply
#13
ah...very nice, thank you. i will post code here when the prototype is finished, might be usefull for someone.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  sqlite3 Conn Insert Value Error TylerDunbar 3 676 Sep-04-2023, 06:32 PM
Last Post: deanhystad
  pymysql; insert on duplicate key update fails wardancer84 4 1,938 Jun-28-2022, 08:14 PM
Last Post: Larz60+
  Mysql Syntax error in pymysql ilknurg 4 2,290 May-18-2022, 06:50 AM
Last Post: ibreeden
  Mysql error message: Lost connection to MySQL server during query tomtom 6 15,682 Feb-09-2022, 09:55 AM
Last Post: ibreeden
  Query Syntax Error hammer 2 1,586 Jan-03-2022, 02:30 PM
Last Post: hammer
  pymysql can't do SELECT * Pedroski55 3 2,902 Sep-11-2021, 10:18 PM
Last Post: Pedroski55
  TypeError: sequence item 0: expected str instance, float found Error Query eddywinch82 1 5,026 Sep-04-2021, 09:16 PM
Last Post: eddywinch82
  Error using mariadb select query with form in python? shams 2 1,956 Jul-29-2021, 12:30 PM
Last Post: shams
  pyarrow throws oserror winerror 193 1 is not a valid win32 application aupres 2 3,722 Oct-21-2020, 01:04 AM
Last Post: aupres
  Function throws error but then works? Milfredo 10 3,698 Sep-12-2020, 05:16 AM
Last Post: Milfredo

Forum Jump:

User Panel Messages

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