MySQL error from Python

Discussion in 'Python' started by Paul M, Jun 27, 2004.

  1. Paul M

    Paul M Guest

    I encountered the following error when trying to perform a SQL UPDATE to a
    MySQL database table from Python.

    I would apprciate any assistance. In the Python code I have tried integer
    and decimal format specifiers in addition to the string specifier and
    nothing worked.

    Traceback (most recent call last):
    File "e:\my_python_scripts\commercecraft.py", line 36, in ?
    cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
    AND sku = '%s'",(myprice,mysku))
    File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 95, in
    execute
    return self._execute(query, args)
    File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 114, in
    _execute
    self.errorhandler(self, exc, value)
    File "E:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in
    defaulterrorhandler
    raise errorclass, errorvalue
    _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
    syntax. Check the manual that cor
    responds to your MySQL server version for the right syntax to use near
    '139.80'' WHERE competitorID=1 AND sk
    u = ''50300288''' at line 1")




    Describe product;

    Field Type Null Key Default Extra
    ------------ ------------ ------ ------ ------- --------------
    productID int(11) PRI (NULL) auto_increment
    sku varchar(50) YES (NULL)
    description varchar(60) YES (NULL)
    price decimal(7,2) YES (NULL)
    scrape_date datetime YES (NULL)
    competitorID int(11) YES (NULL)



    **************************************************************
    import test_iopus
    import MySQLdb
    import sys
    import string


    try:
    conn = MySQLdb.connect (host = "localhost",
    user = "root",
    passwd = "xyz",
    db = "commerce")
    except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

    cursor = conn.cursor()
    cursor.execute ("SELECT sku FROM product WHERE competitorID=1")
    while (1):
    row = cursor.fetchone ()
    if row == None:
    break
    mysku = row[0]
    print mysku
    print "%d rows were returned" % cursor.rowcount

    result = test_iopus.Scrape(mysku)
    price = result.split(" ")
    tmpprice = str(price[0])

    conversion = string.maketrans("$"," ")
    myprice = tmpprice.translate(conversion)


    print myprice

    cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1 AND
    sku = '%s'",(myprice,mysku))
    cursor.close()
    conn.close()
     
    Paul M, Jun 27, 2004
    #1
    1. Advertising

  2. Paul M

    Jeff Epler Guest

    You probably should write
    cursor.execute ("UPDATE product SET price = %s WHERE competitorID=1"
    " AND sku = %s",(myprice,mysku))
    (remove the single-quotes -- the splitting of the string makes no
    difference vs a single line) not
    > cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
    > AND sku = '%s'",(myprice,mysku))


    Jeff

    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.4 (GNU/Linux)

    iD8DBQFA3hDOJd01MZaTXX0RAgIqAJ4toIJNhq3Mc2UOSYvBg6hNxXyO5gCeMdiA
    ntIqmZLA3Ik5YWYqM3nL8TA=
    =PZl8
    -----END PGP SIGNATURE-----
     
    Jeff Epler, Jun 27, 2004
    #2
    1. Advertising

  3. On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <> declaimed
    the following in comp.lang.python:


    > cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
    > AND sku = '%s'",(myprice,mysku))


    Get rid of the single quotes around the %s fields... The
    ..execute method should, in theory, determine the data type and needed
    quoting internally.

    --
    > ============================================================== <
    > | Wulfraed Dennis Lee Bieber KD6MOG <
    > | Bestiaria Support Staff <
    > ============================================================== <
    > Home Page: <http://www.dm.net/~wulfraed/> <
    > Overflow Page: <http://wlfraed.home.netcom.com/> <
     
    Dennis Lee Bieber, Jun 27, 2004
    #3
  4. Paul M

    Paul M Guest

    That fixed it.

    Thank You!


    "Dennis Lee Bieber" <> wrote in message
    news:...
    > On Sun, 27 Jun 2004 00:04:54 GMT, "Paul M" <> declaimed
    > the following in comp.lang.python:
    >
    >
    > > cursor.execute ("UPDATE product SET price = '%s' WHERE

    competitorID=1
    > > AND sku = '%s'",(myprice,mysku))

    >
    > Get rid of the single quotes around the %s fields... The
    > .execute method should, in theory, determine the data type and needed
    > quoting internally.
    >
    > --
    > > ============================================================== <
    > > | Wulfraed Dennis Lee Bieber KD6MOG <
    > > | Bestiaria Support Staff <
    > > ============================================================== <
    > > Home Page: <http://www.dm.net/~wulfraed/> <
    > > Overflow Page: <http://wlfraed.home.netcom.com/> <
     
    Paul M, Jun 27, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JL
    Replies:
    0
    Views:
    1,147
  2. Ravi
    Replies:
    6
    Views:
    1,410
    Suchandra Thapa
    Jul 21, 2003
  3. Replies:
    2
    Views:
    6,196
  4. washakie
    Replies:
    4
    Views:
    925
    washakie
    Jan 15, 2008
  5. dave
    Replies:
    4
    Views:
    1,371
    Steve Holden
    Nov 18, 2010
Loading...

Share This Page