2

Just to give a bit of background, this is a stock system that calls and retrieves data from a database, everything works except I cant get it to insert values into the database. the code is below and the comments annotate what does and doesn't work, there is a lot more to the code however this works.

    try:
            cursor.execute("SELECT * FROM LastOrderNum WHERE ID=0")
            data=cursor.fetchone()
            ordernumber=data[1] + 1
            print (ordernumber)# this is ok - it does update the database number
            try:
                cursor.execute ("UPDATE LastOrderNum SET PurchaseOrder =? WHERE ID=?",(ordernumber, 0)) # this works
                #cursor.execute("UPDATE LastOrderNum SET PurchaseOrder = ordernumber WHERE ID = 0")
                connect.commit()
                print("Got past updating order number")
                print(ordernumber, productId, supplierId, OrderQty) # this works it prints the 4 values
                cursor.execute("INSERT INTO Order VALUES (?,?,?,?)",(ordernumber,productId,supplierId,OrderQty)) # this will not put them into the database
                connect.commit()
                print("correct") 

                content=""
                content=content+"\n"+str(row3[num][1]+" "+str(row3[num][2]))
                if supplier != supplierId and supplier != None:
                    content=str("Hello "+str(row3[num][5])+" "+str(row3[num][6])+",\nI am placing an order for these items,\n\n"+content+"\n\nThank you,\nJohn.")
            except:
                print ("Problem wrting the order details")
        except:
             print("Problem picking up the last used order number")

the code breaks at the cursor.execute("INSERT INTO ......line) Everything else works and the connection is correct however at this line it just spits it back to the "problem writing order details" exception.

Please help!!!

browny87
  • 21
  • 1
  • I don't get an error, since the exception catches it, it just displays the exception error, if I remove the exception handling I still don't get an error it just doesn't execute the insert line, everything else reads and writes from the database, just that line doesn't! – browny87 Apr 27 '17 at 08:41
  • pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.') – browny87 Apr 27 '17 at 08:46
  • ive tried using %s as well and other methods that people recommended on here and none work! – browny87 Apr 27 '17 at 08:46
  • 1
    `Order` is a reserved word in SQL. You should not name your tables like that. And if you do, you *must* quote the table name in your SQL everywhere. See MySQL reference [10.3 Keywords and Reserved Words](https://dev.mysql.com/doc/refman/5.7/en/keywords.html). But really, just don't name your table like that. That's asking for problems. – Tomalak Apr 27 '17 at 08:48
  • it works, thank you so much, I feel really daft now I actually knew that, definite case of staring at something for 3 days and not spotting the obvious mistake! – browny87 Apr 27 '17 at 08:51
  • Another thing is that you should not use Python code to increment database counters. This will blow up at some point. Use database autoincrement fields. See [How do I get the “id” after INSERT into MySQL database with Python?](http://stackoverflow.com/q/2548493/18771) – Tomalak Apr 27 '17 at 08:54

0 Answers0