Python MySQL Incrementing but not inserting

Question

In short, I have a script that takes our retail UPCs to compare prices.

The query does not insert the data but it does increment the ID number (in operations.. no data). When I paste the query and copy and paste it into MySQL it pastes successfully.

Steps of operation :

Grab UPC from upc.txt

Compare on website

Get Price and Website (website contains referral link (wait for the actual link)

Send to database

No errors display - just that it successfully inserted. No actual data was inserted - the only thing that was changed was the auto increment ID in operations.

Side note, this is completely a draft. I realize SQL injections are open.

import urllib
import re
import MySQLdb
import requests

upcfile = open("upc.txt")

upcslist = upcfile.read()


newupclist = upcslist.split("\n")

conn = MySQLdb.connect(host="127.0.0.1",user="root",passwd="",db="Items")
i=0
while i < len(upcslist):
    url = "https://www.(REMOVED).com/search"+newupclist[i]+"?view=list"
    htmlfile = urllib.urlopen(url)
    htmltext = htmlfile.read()
    regex = '<td class="price-column">(.+?)</td>'
    pattern = re.compile(regex)
    price = re.findall(pattern,htmltext)
    itemLinkRegex = '<td class="link-column"><a href="(.+?)" target="_blank">'
    itemLinkPattern = re.compile(itemLinkRegex)
    itemLink = re.findall(itemLinkPattern,htmltext)
    p=0
    try:
        while p <len(price):
            try:
                r = requests.get(itemLink[p], allow_redirects=False)
                itemLinkFormat = r.headers['Location']
            except:
                itemLinkFormat = itemLink[p]
                pass
            #print "Sent to Database Competitor Price: UPC ["+newupclist[i]+"] - Price: ", price[p]+" Item URL: ", itemLinkFormat
            pre = ''.join(price[p].split('$', 1))
            priceformat = ''.join(pre.split(',', 1))
            skuNumber = newupclist[i]
            try:
                query = "INSERT INTO Entry (SKU, Price, Item_link) VALUES ("
                query = query+"'"+skuNumber+"','"+priceformat+"','"+itemLinkFormat+"')"
                print query
                x = conn.cursor()
                x.execute(query)
                row = x.fetchall()
            except:
                print "Error, Not a valid number to enter into database."
                pass
                p+=1
            p+=1
        i+=1
    except:
        pass
        i+=1

Alfabravo · Accepted Answer · 2017-03-16 19:14:43Z

1

Don't see why doing fetchAll, try con.commit() and then cur.rowcount to check the rows affected.

Also, your catch could be like this

except cur.Error, e:

    if con:
        con.rollback()

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

answered Mar 16, 2017 at 19:14

Alfabravo

7,5786 gold badges49 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alfabravo Over a year ago

@hinteractive02 Glad it did :)

Collectives™ on Stack Overflow

Python MySQL Incrementing but not inserting

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related