Solved

mySQL Syntax problem - uploading python list to mysql table.

Posted on 2015-01-04
10
328 Views
Last Modified: 2015-01-11
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)
      cursor.execute(sql)
      db.commit()


#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            xyz = getData()
            mylist=xyz[0]
            main(mylist)
            insertcustomerorder(db,cursor)
            htmlTail()
      except:
            cgi.print_exception()

====================================================================

Thanks for any help. Regards,
table.jpg
SQLErrorMessage.jpg
0
Comment
Question by:jameskane
  • 5
  • 5
10 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40530028
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.
0
 

Author Comment

by:jameskane
ID: 40530301
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);"""
      cursor.execute(sql)
      db.commit()

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

Thanks
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 40530532
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: http://www.pythonforbeginners.com/concatenation/string-concatenation-and-formatting-in-python
0
 

Author Comment

by:jameskane
ID: 40531052
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)
      cursor.execute(sql)
      db.commit()

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

Expert Comment

by:Dave Baldwin
ID: 40531072
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jameskane
ID: 40531106
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 ??
RESULTS.jpg
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40531122
The pic you posted shows that 'sorted_list' (which should be the string variable) doesn't exist.  ??
0
 

Author Comment

by:jameskane
ID: 40531193
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):
      #print(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)
      print(sorted_list)
0
 

Author Closing Comment

by:jameskane
ID: 40542780
That's working now Dave. Thanks for the sql.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40542786
You're welcome, glad you got it working.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Python Assistance 7 31
move records one table to another 14 51
mysql Encryption with PHP 8 47
Python On Mouseover Save URL/Link 4 32
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
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…

707 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

17 Experts available now in Live!

Get 1:1 Help Now