0

Update: This issue was resolved in the comments and is awaiting an answer

When executing the following query in PyMySQL, I receive the error ‘1064, u"You have an error in your SQL syntax;’ (full error message below)

INSERT INTO `table_name` (`id`, `colName1`, `colName2`, `colName3`) 
VALUES (820, 'string', 5, 'N')
ON DUPLICATE KEY UPDATE
    `colName1`=VALUES(`colName1`),
    `colName2`=VALUES(`colName2`),
    `colName3`=VALUES(`colName3`)
-- Tried with and without ` surrounding column names after ON DUPLICATE KEY UPDATE

Syntax in Python: 

sql = "INSERT INTO `table_name` (`id`, `colName1`, `colName2`, `colName3`) VALUES (820, 'string', 5, 'N') ON DUPLICATE KEY UPDATE `colName1`=VALUES(`colName1`),`colName2`=VALUES(`colName2`),`colName3`=VALUES(`colName3`)"

I received this error both when trying to update many rows (as suggested elsewhere, using the format of the accepted answer), and for one row at a time.

As noted in the comments, the stripped down example didn't produce an error (although it was not executed with Python). The following is the actual query. Let me know if other information is needed.

Query string executed in Python:

INSERT INTO `Listings` (`id`, `row_last_updated`, `maxBidCountPreRsvMet`, `maxBidPreRsvMet`, `minBidCountPostRsvMet`, `occupancy_status`, `waitingForHnb`, `ownItNow_price`, `high_bid`, `prop_id`, `hot_property`, `status`, `end_date`, `reserve_met`, `hours`, `backupBidSet`, `listing_type`, `low_bid`, `winning_bid`, `bids`, `days`, `high_bid_updated`, `minutes`, `lowBidIsOpt1`) VALUES (820, '2018-01-28 19:16:02', '5', '234000', None, 'N', None, 0, 234000, u'9007092665103', 'N', 'New', '2018-1-31-22-0', 'N', 7, 0, 'AUCN', 0, 0, 5, 3, '2018-01-28 14:16:02.001906', 44, 0) ON DUPLICATE KEY UPDATE `row_last_updated`=VALUES(`row_last_updated`),`maxBidCountPreRsvMet`=VALUES(`maxBidCountPreRsvMet`),`maxBidPreRsvMet`=VALUES(`maxBidPreRsvMet`),`minBidCountPostRsvMet`=VALUES(`minBidCountPostRsvMet`),`occupancy_status`=VALUES(`occupancy_status`),`waitingForHnb`=VALUES(`waitingForHnb`),`ownItNow_price`=VALUES(`ownItNow_price`),`high_bid`=VALUES(`high_bid`),`prop_id`=VALUES(`prop_id`),`hot_property`=VALUES(`hot_property`),`status`=VALUES(`status`),`end_date`=VALUES(`end_date`),`reserve_met`=VALUES(`reserve_met`),`hours`=VALUES(`hours`),`backupBidSet`=VALUES(`backupBidSet`),`listing_type`=VALUES(`listing_type`),`low_bid`=VALUES(`low_bid`),`winning_bid`=VALUES(`winning_bid`),`bids`=VALUES(`bids`),`days`=VALUES(`days`),`high_bid_updated`=VALUES(`high_bid_updated`),`minutes`=VALUES(`minutes`),`lowBidIsOpt1`=VALUES(`lowBidIsOpt1`)

The full error message: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''9007092665103', 'N', 'New', '2018-1-31-22-0', 'N', 7, 0, 'AUCN', 0, 0, 5, 3, '2' at line 1")

Side notes: Python None values are converted to MySQL null values. The same error occurred when changing None values to strings.

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
  • MySql version ? – Lasse V. Karlsen Jan 28 '18 at 20:00
  • The query works fine: http://rextester.com/OTUD99946 - Please post a reproducible example. Also post the full error message. – Paul Spiegel Jan 28 '18 at 20:01
  • @LasseVågsætherKarlsen I believe the version is 5.6.27 – ballade4op52 Jan 28 '18 at 20:02
  • @PaulSpiegel Not sure if helpful, but someone edited out the original Python syntax (where the error was produced), and has been reposted. I'll see what else I can post that's reproducible. – ballade4op52 Jan 28 '18 at 20:06
  • 1
    @Phillip It was me, and I only added line breaks to make it more readable. The original one-liner also works: http://rextester.com/GKJAC56629 – Paul Spiegel Jan 28 '18 at 20:10
  • @PaulSpiegel More information may be needed (didn't want to overload the post), but the originally used query string has been added to the post – ballade4op52 Jan 28 '18 at 20:12
  • No wonder you don't find the error, if you don't use line breaks and don't read the error message. Look for `None` in your VALUES list. – Paul Spiegel Jan 28 '18 at 20:17
  • @PaulSpiegel In PyMySQL Python `None` values are converted to MySQL `null` values. I did read the error message, and added the full error message, if helpful. – ballade4op52 Jan 28 '18 at 20:18
  • Well.. now you know it's near `'9007092665103'`. So what is `u'9007092665103'` supposed to be? – Paul Spiegel Jan 28 '18 at 20:23
  • The column info for that data is: `name: prop_id, type: varchar(25) Default: NULL ` – ballade4op52 Jan 28 '18 at 20:26
  • To be sure, a test changing the None values to strings resulted in the same error – ballade4op52 Jan 28 '18 at 20:34
  • 2
    not sure about `u` prefix https://dev.mysql.com/doc/refman/5.7/en/string-literals.html – Deadooshka Jan 28 '18 at 21:21
  • @Deadooshka Updating the unicode to str fixed the error. Feel free to post as an answer at your convenience, and I will accept it. – ballade4op52 Jan 28 '18 at 21:48
  • Please post the complete error message. There is a part you have omitted starting 'NEAR ...'. – user207421 Jan 29 '18 at 01:14
  • The full error message was posted in an edit a few hours ago at "The full error message: " (just double checked the console to verify it was the full message), but I'll make an edit by the initial error message snippet – ballade4op52 Jan 29 '18 at 01:22

0 Answers0