Solved

Cleaning up list selected from mysql database

Posted on 2015-01-16
4
113 Views
Last Modified: 2015-01-17
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.
0
Comment
Question by:jameskane
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Walter Ritzel
ID: 40554034
Can you show the code that is doing this?
0
 

Author Comment

by:jameskane
ID: 40554380
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)
      cursor.execute(sql)
      db.commit()
      print(sorted_list, "helllllo" "its uploaded !!")


Following is selectOrderContent.py  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;"
    cursor.execute(sql)
    order=cursor.fetchall()
    #print(order)
    return order

Main page OrderformTHREE.cgi which includes the above codes

#!C:\Python34\python.exe
import cgi
#import pickle
import mysql.connector as conn
import Create2D
import ONEinsertion
import Kill_invert_cs
import DUPLICATE_COUNTER
import selectOrderContent
def htmlTop():
      print("""Content-type:text/html\n\n
            <!DOCTYPE html>
             <html lang="en">
                 <head>
                  <meta charset="utf-8"/>
                  <title> My Server-side template</title>
             </head>
             <body>""")

def htmlTail():
      print(""" its done</body>
             </html>""")
     
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)
      cursor.execute(sql)
      db.commit()
      print(sorted_list, "helllllo" "its uploaded !!")
     

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




     

     
     
     
     
     
     
     
#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            xyz = getData()
            mylist=xyz[0]
            main(mylist)
            sorted_list = main(mylist)
            insertcustomerorder(db,cursor)
            order=selectOrderContent.Function6(db, cursor)
            print (order, "from database")
            print(sorted_list, "variable")
            htmlTail()
      except:
            cgi.print_exception()

OUTPUT
[[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
CustomerOrderTable.jpg
OrderContentColumn.jpg
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 40554908
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')
print(order)

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.
0
 

Author Closing Comment

by:jameskane
ID: 40555150
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 !
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now