mySQL Syntax problem - uploading python list to mysql table.

I have the following type of list (mylist) created as part of an order processing process. It defines an order's content.
[[1, 27, 2], [1, 29, 3], [1, 26, 10], [2, 25, 1]]
Lists of this type will vary in length, depending of the number of items ordered.

Each list  needs to be entered into mysqL database table named customerorder.

I am attaching an image of customerorder table with the defined columns. The list needs insertion into the OrderContent column.

I am also attaching the sql error message which I get when running the action page ORDERFORM3.CGI. The following is the relevant section of code.


def insertcustomerorder(db,cursor):
      sql = "insert into customerorder(OrderContent)values('{0}')".format(mylist)

#main program
if __name__== "__main__":
            db, cursor = connectDB()
            xyz = getData()


Thanks for any help. Regards,
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
This little test program works fine in Python 2.7.
# Program to test string cat

mylist = "[[1, 27, 2], [1, 29, 3], [1, 26, 10], [2, 25, 1]] "
sql = "insert into customerorder(OrderContent)values('{0}')".format(mylist) 

print "SQL string is : " + sql
raw_input("Enter any key to continue...")
#End of Code

Open in new window

Other variations here:
Dave BaldwinFixer of ProblemsCommented:
You need to echo (print?) your sql statement.  A comma is used in MySQL as a separator for values.  It appears that MySQL may thinking that you're giving it a list where [[1 is the first value.  I can use your data in an insert in phpMyAdmin which quotes it properly.
jameskaneAuthor Commented:
Dave, thanks again for the reply. I have made some progress and the function below makes an entry made into the table. However, all I get in the OrderContent column is the name of the list 'mylist'- what I need is the value of the list - like you get from print(mylist) - but this does not work with sql - I just get print(mylist) entered as the value for OrderContent.  Also tried %(mylist)s, but this throws an error.

      sql = """INSERT INTO customerorder (OrderID, OrderContent)
          VALUES (100, mylist);"""

Would you happen to know the magic symbols which would be cause the VALUE of mylist to be entered instead of just its name ?

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

jameskaneAuthor Commented:
Hi Dave,
This works with your list. The function is shown below.

def insertcustomerorder(db,cursor):
      sql = "insert into customerorder(OrderContent)values('{0}')".format(mylist)

However, your value is mylist = "[[1, 27, 2], [1, 29, 3], [1, 26, 10], [2, 25, 1]] "
wheras the value my program feeds into the function would not have the "".  Without the "" around the list it does not work.

I tried changing the value of mylist to include the "" but this creates an error.
ie  mylist = "mylist".

I tried putting "" on mylist in the function and feeding it with mylist without "". That  inserts mylist into the table - not the value.

Close, but something still   wrong.

BTW, mylist is definitely generated by the program as I have it printed out each time i run it.
Dave BaldwinFixer of ProblemsCommented:
That doesn't make any sense.  I used your exact statement to create the 'sql' string.  'mylist' is a string and there is no reason for two double quotes.  Right after mylist=xyz[0] , do print(mylist) and tell me what you get.
jameskaneAuthor Commented:
The output I get from the program does not have "" Dave.

Attached is the error message from running my program, including trying to upload mylist to the table. You see at the top the result of print(mylist). Then underneath you see the error message relating to the uploading attempt.

If I run it again, but change mylist to include "" then the uploading takes place correctly.

The 'solution' would be to change the value of mylist produced by the program to include the "".  I don't know how to do that ??
Dave BaldwinFixer of ProblemsCommented:
The pic you posted shows that 'sorted_list' (which should be the string variable) doesn't exist.  ??
jameskaneAuthor Commented:
Very sorry Dave for the confusion. In my original posting I used mylist.  Realized that this was the wrong name, should have been sorted_list. Just left it at that to avoid confusion.  It is sorted_list which his passed to the insert function, not mylist. See the function below which creates the input sorted_list. You see the print(sorted_list) on the last line which appears in the error report I attached

def main(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)
jameskaneAuthor Commented:
That's working now Dave. Thanks for the sql.
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad you got it working.
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.