Janko Janko - 4 months ago 16
MySQL Question

python: adding data as string in mysql gives weird error

I'm having a weird problem with a piece of python code.

The idea how it should work:
1. a barcode is entered (now hardcode for the moment);
2. barcode is looked up in local mysqldb, if not found, the barcode is looked up via api from datakick, if it's not found there either, step 3
3. i want to add the barcode to my local mysqldatabase and request some input.

Now the problem: it works! als long as you fill in numbers for the

. If you use letters (eg. I filled in Bla as productname), I get a weird SQL-error
(_mysql_exceptions.OperationalError: (1054, "Unknown column 'Bla' in 'field.list'")

I have checked the tables in mysql and the types are all ok. The table where name should end up in is text. I have also tried a hardcoded string which works fine. Using the sql-query from the mysql console also works perfectly. My guess is something is going wrong with the inputpart, but I can't figure out what.

(code is still not really tidy with the exceptions, I know ;) Working on it step by step)


def barcodeFunctie(sql):
con = mdb.connect ('localhost', 'python', 'python', 'stock')
cur = con.cursor()
ver = cur.fetchone();
return ver

#barcode = '8710624957278'
#barcode = '2147483647'
barcode = '123'

#zoeken op barcode. Barcode is ook de sleutel in de tabel.
sql = "select * from Voorraad where Id=%s" % barcode

if barcodeFunctie(sql) == "None":
print "geen output"
data = json.load(urllib2.urlopen(url))
print data['brand_name'], data['name']
#barcode komt niet voor in eigen db en niet in db van datakick, in beide toevoegen
print barcode, " barcode als input"

naamProduct = str(raw_input("Wat is de naam van het product? "))
hoeveelheidProduct = raw_input("Hoeveel inhoud heeft het product? ")

sql = "insert into Voorraad (Id, NaamProduct,HoeveelHeidProduct) values (%s,%s,%s)" % (barcode, naamProduct, hoeveelheidProduct)
print "meuktoegevoegd! :D"


sql = "select * from Voorraad where Id=%s" % barcode

Your problem is that you are missing quotes for your ID. Change that line above to this:

sql = "select * from Voorraad where Id='%s'" % barcode