sqlite3.OperationalError: no such column: sudo

Question

I'm exploring sqlite3 in python by adding values of ps -a in DB. However, Im getting the no such column: sudo error from my insert_sensor_reading() funtion.

I've tagged the entire code just in case if anyone wants to cross verify

#!/usr/bin/python

import sqlite3
import subprocess

dbConn = None

#create table Reading
def create_table_reading():
    dbConn.execute('''CREATE TABLE  IF NOT EXISTS MYTABLE
        (PID            INT            NOT NULL,
         CMD            TEXT           NOT NULL);''')
    print "Created";

#Insert sensor reading
def insert_sensor_reading():   
    for i in range(0,l):
        query =  "INSERT INTO MYTABLE (PID,CMD) \
                  VALUES (" + pidstr[i] +  ","+ cmdstr[i] +");"
        i = i + 1
        dbConn.execute(query)
        dbConn.commit()
        print "inserted";

#read from table
def select_sensor_reading():
    query = "SELECT * FROM MYTABLE;"
    cursor = dbConn.execute(query)
    for row in cursor:
        print "PID = ", row[0],"\n"
    print "read";

#read from shell
def read_from_shell():
    pid = subprocess.check_output("ps -a | awk '{print $1}'", shell =True)
    cmd = subprocess.check_output("ps -a | awk '{print $4}'", shell =True)

    pidstr = pid.splitlines( )
    cmdstr = cmd.splitlines()
    global pidstr,cmdstr,l
    pidstr = pidstr[1:]
    cmdstr = cmdstr[1:]
    l=len(pidstr)


#Main
if __name__ == '__main__':

    dbConn = sqlite3.connect('test.db')
    print "Opened database successfully";
    read_from_shell()
    create_table_reading()
    insert_sensor_reading()
    select_sensor_reading()

EDIT: Below is the output of ps -a Im storing all the PID values in pidstr and all commands in cmdstr. the error is probably because of some error while storing sudo in cmdstr but Im not sure why is it happening.

  PID TTY          TIME CMD
13280 pts/18   00:00:00 sudo
13281 pts/18   00:00:00 su
13282 pts/18   00:00:00 bash
17482 pts/17   00:00:00 ssh
19635 pts/19   00:00:00 ssh
24531 pts/1    00:00:00 sudo
24538 pts/1    00:00:00 su
24539 pts/1    00:00:00 bash

What exactly is the content of pidstr and cmdstr when used inside insert_sensor_reading()? — shmee
– shmee, Commented Apr 13, 2018 at 9:05
I'm running the ps -a command, pidstr is the list of PID's of all the running processes, cmdstr is all the commands — Abhay Nayak
– Abhay Nayak, Commented Apr 13, 2018 at 11:55
Yes, I was interested in what the actual output was, to be honest. The unknown column sudo is VERY likely to be coming from cmdstr. But I guess I figured it out. — shmee
– shmee, Commented Apr 13, 2018 at 12:13
What happens if the process list changes between your ps -a | awk calls? Then everything is out of sync. Is there a reason you don't just parse the output of ps -a in one piece? — glglgl
– glglgl, Commented Apr 13, 2018 at 14:10
@AbhayNayak As you would do it in opther languages, and as you will learn in any tutorial: by having the functions producing a value return it and by having functions using or consuming a value get it via a parameter. — glglgl
– glglgl, Commented Apr 16, 2018 at 9:04

shmee · Accepted Answer · 2018-04-13 14:28:39Z

2

Your table row CMD is defined as datatype TEXT. However, in your insert statement you actually pass sth like:

INSERT INTO MYTABLE (PID,CMD) VALUES (5, sudo)

while it needs to be

INSERT INTO MYTABLE (PID,CMD) VALUES (5, 'sudo')

# DO NOT DO IT LIKE THIS, SEE BELOW
query =  "INSERT INTO MYTABLE (PID,CMD) \
              VALUES (" + pidstr[i] +  ",'"+ cmdstr[i] +"');"

IMPORTANT: interpolating the query strings yourself is NOT recommended for security reasons. Rather use the execute method accordingly:

dbConn.execute("INSERT INTO MYTABLE (PID,CMD) VALUES (?, ?)", (pidstr[i], cmdstr[i]))

edited Apr 13, 2018 at 14:28

answered Apr 13, 2018 at 12:19

shmee

5,1512 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

glglgl Over a year ago

Your side note is very true. IMHO it shouldn't be a side note, but a very big red flag.

Abhay Nayak Over a year ago

dayyum, that was such a silly mistake, thank you @shmee and yes I'll look for the security aspect as well :)

shmee Over a year ago

@glglgl You do have a point there. I edited my answer accordingly.

Collectives™ on Stack Overflow

sqlite3.OperationalError: no such column: sudo

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related