Cleaning up list selected from mysql database

I need to store lists which are generated by an order entry program.  The lists take the form of
[[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]] .... an example of such a list.

I am able to insert these lists into my database and then select them when required. Problem is that the selected lists
are always front ended and back ended by 'noise'.   [(b' is appended to the start of the selected list and ',)] to the end. (note, the noise at the front and back is different as show above... not a typo) This is shown below

[(b'[[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]]',)]    

Is is possible to trim the list to remove the noise ?   All the lists stored have exactly the same noise attached - regardless of their size.
Who is Participating?
clockwatcherConnect With a Mentor Commented:
The b is only there because you've defined your database column as a blob-- that will get read into a python binary string (an array of bytes) and when you call the print function on a byte array python prepends the 'b' to indicate to you it's a byte array.  The 'b' doesn't actually exist in the database nor is it in the string itself.  It's just represented that way when you print it.  And to put it more or less bluntly, you typically wouldn't want to print a raw byte array.  You're doing a lot of printing of things that, in general, you wouldn't typically directly output (e.g., lists, lists of lists, raw resultsets).  You typically don't rely on the print method to print out raw objects directly.  Usually, you write up some kind of output function to present them to your end-users in a friendlier way.

Anyway... that's what is going in with the 'b' and the quotes.   You might want to read up on objects, the __str__ method and byte arrays.

To get rid of the 'b' and the quotes when you print the thing, you need to do what I stated earlier and write up your own output method for the way that you want to see the data rather than rely on an object's default string coercion.  

Going to first walk through things to try and help explain what is going on.  Your Function6 returns a resultset which is a list of rows with each row made up of a list of columns.  You'll only have one row because you're pulling one record back but it still will be in a list.  The default string representation of that will print like this:

  [ row, ]

Your row object is a tuple of columns.  It, too, will only be a single column because you're only selecting a single column but it will still be in a tuple because that's what is returned by a call to fetch.  Putting both together, you've got a default string representation that looks like this:

   [ (column,) , ]

Because you've got the single column that you're returning defined as a blob, the data in it will be a byte array and the string representation of that will get a b prepended and enclosed in quotes and you finally end up with this:

   [(b'[[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]]',),]

Neither the 'b' nor the quotes are in the database or in the string.  They are only represented that way when they're coerced to a string.  So that's what you get when you call print on your raw result set.  

You wouldn't typically print a raw result set.  Instead, you would retrieve the data out of it that you want and print that.  In your example, you're only interested in the value in the first column of the first row.  That would be represented by:
resultset=selectOrderContent.Function6(db, cursor)
order = resultset[0][0]

Open in new window

Because of the way you're storing things in your database that 'order' variable is going to be a byte array when you get it back from your fetch.  The default print for that is going to get you the 'b' and the quotes.  So you can't just call the default print on it.  You'll need to convert it from a byte array to a standard string.  One of the easiest ways to do that is to call the decode method which tells python to read the binary data and interpret as a string with a particular encoding.  You're dealing with standard ascii data, so decoding it as ascii would be a pretty good choice.  In other words...
resultset=selectOrderContent.Function6(db, cursor)
order = resultset[0][0]
order = order.decode('ascii')

Open in new window

The above is typically how you would deal with things.  Very rarely do you rely on the default string representation of any moderately complex object (unless you've written the __str__ function yourself).  It's usually just there for debugging and for quick looks in an interactive session.  It's typically not something you would ever present to an end-user.
Walter RitzelSenior Software EngineerCommented:
Can you show the code that is doing this?
jameskaneAuthor Commented:
Hi, thanks very much for looking at this.  Below is the code in three segments. First two are embedded in the third. Also show at bottom is an output received when running the main (third below)
Also attached is snapshots of the mysql table.

included in OrderFormTHREE below
def insertcustomerorder(db,cursor):
      sql = "insert into customerorder(OrderContent)values('{0}')".format(sorted_list)
      print(sorted_list, "helllllo" "its uploaded !!")

Following is  which is imported to OrderFormTHREE.cgi page
def Function6(db,cursor):
    #sql = "SELECT IDENT_CURRENT(‘customerorder’) OrderContent;"
    sql = "select OrderContent from customerorder  WHERE OrderID = 43;"
    return order

Main page OrderformTHREE.cgi which includes the above codes

import cgi
#import pickle
import mysql.connector as conn
import Create2D
import ONEinsertion
import Kill_invert_cs
import selectOrderContent
def htmlTop():
            <!DOCTYPE html>
             <html lang="en">
                  <meta charset="utf-8"/>
                  <title> My Server-side template</title>

def htmlTail():
      print(""" its done</body>
def connectDB():
      db=conn.connect(host='localhost' ,user='root' ,passwd='' ,db='pizza')
      cursor = db.cursor()
      return db, cursor

def getData():
      formData = cgi.FieldStorage()
      #dropSize = []
      dropSize = formData.getlist('drop')
      dropName = formData.getlist('drop')
      return dropSize, dropName

def insertcustomerorder(db,cursor):
      sql = "insert into customerorder(OrderContent)values('{0}')".format(sorted_list)
      print(sorted_list, "helllllo" "its uploaded !!")

def main(mylist):
      #print(mylist,"this is mylist")
      integer_list = Kill_invert_cs.Function1(mylist)
      twod_list = Create2D.Function2(integer_list)
      inserted_list = ONEinsertion.Function3(twod_list)
      sorted_list = DUPLICATE_COUNTER.Function4(inserted_list)
      return sorted_list


#main program
if __name__== "__main__":
            db, cursor = connectDB()
            xyz = getData()
            sorted_list = main(mylist)
            order=selectOrderContent.Function6(db, cursor)
            print (order, "from database")
            print(sorted_list, "variable")

[[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]] hellllloits uploaded !! [(b'[[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]]',)] from database [[2, 25, 1], [1, 26, 2], [1, 27, 3], [1, 29, 10]] variable its done
jameskaneAuthor Commented:
Thank you SO much clockwatcher for taking the time to once again provide the solution AND the most valuable education. As you gather, I am very new to Python and this input is invaluable to me. Now I have a perfect list to work on. Will definitely read up on the pointers you gave.

My experience is with Coldfusion and I am (for fun and learning) rewriting (in python) an art school student registration and revenue/check management system which I developed. There are LOTS of end user database driven tables and forms in that ;)

But seriously - the 'prints' you see in the code are there exactly as you suggested they should be there for - to help me understand and debug the code. Tables come next, NOW that I have a proper list to work on !!!!

Thanks again !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.