Python Forum
New to MySQL, can't work out why code doesn't work
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
New to MySQL, can't work out why code doesn't work
#1
I'm creating a script called rootrun, it runs things as root that were put in a MySQL database but only if they match regular expressions in the database. It should be telling me "hello word" but it doesn't.

The database is pretty simple:
MariaDB [rootrun]> SHOW TABLES;
+-------------------+
| Tables_in_rootrun |
+-------------------+
| allowed           |
| operations        |
+-------------------+
2 rows in set (0.001 sec)

MariaDB [rootrun]> SELECT * FROM allowed;
+----+------+
| id | rule |
+----+------+
|  1 | (.*) |
+----+------+
1 row in set (0.000 sec)

MariaDB [rootrun]> SELECT * FROM operations;
+----+------------------+
| id | oper             |
+----+------------------+
|  1 | echo hello world |
+----+------------------+
1 row in set (0.000 sec)
Keep in mind the regexp there is meant to just let everything through for now.

Here is the script which uses that database, but doesn't seem to be working. It doesn't return any output like it should.

#!/usr/bin/python3
import mysql.connector
import re
import os

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    for rule in result:
        if (re.match(str(rule), str(rootcmd))):
            if (itran == False):
                os.system(rootcmd)
            itran = True


def runOperations():
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    for operid in result:
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(operid) +"'")
        operation = cursor.fetchall()
        allowExec(operation)
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(operid) +"'")

runOperations()
Reply
#2
1. You are not doing anything with the result of the cursor.execute() on line 35, e.g. fetch, print/return. etc.
2. even if you did, the result will not be what you expect. result (line 32) is tuple of tuples. So operid is tuple and str(operid) will have extra brackets, so your SELECT will return empty tuple
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
#3
The 'result' variable on line 32 is used in the foreach loop for operid. Can you demonstrate your point with code?
Reply
#4
(Feb-18-2019, 04:32 PM)kintarowonders Wrote: The 'result' variable on line 32 is used in the foreach loop for operid. Can you demonstrate your point with code?
yes it is used in the for loop and operid (which is tuple) is used in second SELECT query, but you do nothing with the result of this second query
def runOperations():
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()
 
    for operid in result:
        print(operid)
        cmd = "SELECT oper FROM operations WHERE id=\'" + str(operid) +"'"
        print(cmd)
        cursor.execute(cmd)
        print(cursor.fetchall())
by the way the way you construct cmd is potentially dangerous. Use like this
cursor.execute("SELECT oper FROM operations WHERE id=%s", operid)
not tested, you may need '
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
#5
Alright, here is how I fixed it up, but for some reason the row isn't deleted by the database on line 48, and now I'm stuck there.

#!/usr/bin/python3
import mysql.connector
import re
import os
from itertools import chain

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    lresult = list(result)

    for rule in lresult:
        frule = rule[0]
        if (re.match(str(frule), str(rootcmd[0]))):
            if (itran == False):
                os.system(rootcmd[0])
            itran = True

    print(itran)


def runOperations():
    global mydb
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    lresult = list(result)

    for operid in lresult:
        idstr = operid[0]
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(idstr) +"'")
        operation = cursor.fetchall()
        allowExec(list(operation)[0])
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(idstr) +"'")

runOperations()
Reply
#6
You need to commit the changes
add mydb.commit()
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
#7
Works now.. Thanks so much!

#!/usr/bin/python3
import mysql.connector
import re
import os
from itertools import chain

global mydb

mydb = mysql.connector.connect(
        host="localhost",
        user="rootrun",
        passwd="4tFZYUfey8Lv5Vg3Gc7q8XE4",
        database="rootrun")

def allowExec(rootcmd):
    global mydb;
    cursor = mydb.cursor()
    cursor.execute("SELECT rule FROM allowed")
    result = cursor.fetchall()

    itran = False

    lresult = list(result)

    for rule in lresult:
        frule = rule[0]
        if (re.match(str(frule), str(rootcmd[0]))):
            if (itran == False):
                os.system(rootcmd[0])
            itran = True

    print(itran)


def runOperations():
    global mydb
    cursor = mydb.cursor()
    cursor.execute("SELECT id FROM operations")
    result = cursor.fetchall()

    lresult = list(result)

    for operid in lresult:
        idstr = operid[0]
        cursor.execute("SELECT oper FROM operations WHERE id=\'" + str(idstr) + "'")
        operation = cursor.fetchall()
        allowExec(list(operation)[0])
        cursor.execute("DELETE FROM operations WHERE id=\'" + str(idstr) + "'")
        mydb.commit()

runOperations()
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  print doesnt work in a function ony 2 233 Mar-11-2024, 12:42 PM
Last Post: Pedroski55
  Need some help trying to get my Python mortgage amortization calculator to work prope IamSirAskAlot 4 15,675 Feb-12-2024, 10:53 PM
Last Post: BerniceBerger
  Multiprocessing: Threads work well. Processes don't work. viyubu 11 1,666 Dec-03-2023, 08:50 PM
Last Post: snippsat
  Pydoc documentation doesnt work Cosmosso 5 4,266 Nov-25-2023, 11:17 PM
Last Post: vidito
  hi need help to make this code work correctly atulkul1985 5 702 Nov-20-2023, 04:38 PM
Last Post: deanhystad
  How to copy work sheet data one workbook to other? sayyedkamran 2 646 Nov-03-2023, 09:10 AM
Last Post: Larz60+
  newbie question - can't make code work tronic72 2 626 Oct-22-2023, 09:08 PM
Last Post: tronic72
  Mysql and mysql.connector error lostintime 2 612 Oct-03-2023, 10:25 PM
Last Post: lostintime
  Why wont this path work one way, but will the other way? cubangt 2 623 Sep-01-2023, 04:14 PM
Last Post: cubangt
  PyOpenGL is not work PoseidonLin 5 1,178 Jul-12-2023, 02:51 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

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