asked on

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.

Walter Ritzel

Can you show the code that is doing this?

jameskane

ASKER

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

ASKER CERTIFIED SOLUTION

clockwatcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jameskane

ASKER

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 !