Inserting hexadecimal value as integer to MySQL

Question

I have a MySQL table where a field have a type of INT. In my Python script I am reading the hexadecimal value (0xc558, for example) and trying to insert this value in the table as INT field type.

Doing so gives me:

(1265, "Data truncated for column at row 1)

An INT field type should be a standard integer with 4 bytes which should accept the value of 0xC558, which is 50520 in a decimal world.

I'm not doing any conversion, and the code I use is:

self.cur.execute("INSERT INTO my_table VALUES(NULL, %s)", (dataDict["hex_value"],)

where dataDict["hex_value"] is 0xC558.

Trying to use int(dataDict["hex_value"]) also gives an error: Invalid literal for int() with base 10: 0xC558.

Trying to use int(dataDict["hex_value"],base=16) also gives an error: int() can't convert non-string with explicit base.

EDIT: the value is of type 'str', as print type(dataDict["hex_value"]) shows (type 'str')

If it is str I wonder why on earth you get the exception when you do: int(dataDict["hex_value"],base=16) — Paulo Bu
– Paulo Bu, Commented Mar 6, 2014 at 23:54
MySQL hex literals use the syntax X'C558', not 0xC558. So it's treating that value as a string, not a hex number. I suggest you convert it to an integer in Python before doing the INSERT. — Barmar
– Barmar, Commented Mar 7, 2014 at 0:01

holdenweb · Accepted Answer · 2014-03-06 23:38:22Z

1

Note that you AREN'T trying to insert a string or an integer.

You say "dataDict["hex_value"] is 0xC558" but you don't say whether it's a string or (as the absence of quotation marks would imply) an integer. It can't be an integer because of the first error you replt. It's clearly not a string due to the second error.

So the issue lies with the type of whatever you have stored in dataDict, and you don't shoe the code for that. Yet ...

answered Mar 6, 2014 at 23:38

holdenweb

37.8k7 gold badges62 silver badges80 bronze badges

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

2 Comments

holdenweb Over a year ago

Probably. I was going to edit it, but with an acceptable answer in place maybe I should just delete it?

holdenweb Over a year ago

I don't think anyone really sees it as that helpful, and despite the upvote I would be happy to let it go (since the real answer was nothing to do with the data types).

Barmar · Accepted Answer · 2014-03-07 00:17:42Z

0

Use:

int(dataDict["hex_value"][2:], base=16)

The substring [2:] extracts the part of the string after the 0x prefix. More completely:

self.cur.execute("INSERT INTO my_table VALUES(NULL, %s)", (int(dataDict["hex_value"][2:], base=16), ))

edited Mar 7, 2014 at 0:17

answered Mar 7, 2014 at 0:05

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

Inserting hexadecimal value as integer to MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related