Solved

mySQL Syntax problem - uploading python list to mysql table.

Posted on 2015-01-04
10
332 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 83

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 83

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 83

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 83

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 83

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 48
how can I check for the firs three letters of the word with php? 8 64
Problem with SqlConnection 4 159
RDBMS and No sql database 4 42
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
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 strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…

912 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

21 Experts available now in Live!

Get 1:1 Help Now