0

I am creating a table using mysql.connector in Python. When I insert an integer value into my table, SQL converts it to some other integer value. Here is my code:

cursor.execute("CREATE TABLE cleaned_data (myid VARCHAR(255), mynum INTEGER(255))")

cursor.execute("INSERT INTO cleaned_data(myid, mynum) VALUES(%s,%s)", ('mytext',25683838382092010098988))

rows = cursor.execute('select * from cleaned_data;')
for row in cursor:
    print(row)

Here is the output of the printed row:

('mytext', 2147483647)

Any idea what is going on? I want my table to store my integer number, even if it is huge.

myname
  • 1,337
  • 2
  • 11
  • 17
  • 2
    Then use [`BIGINT`](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html) on MySQL side. It's not limitless either but would give you a wider range. – PM 77-1 Oct 18 '21 at 22:21
  • I tried cursor.execute("CREATE TABLE cleaned_data (myid VARCHAR(255), mynum BIGINT)") and my integer is now 9223372036854775807, which is still not large enough. Is there no way to save an even larger integer? – myname Oct 18 '21 at 22:22
  • 1
    Store as strings and convert on application side. – PM 77-1 Oct 18 '21 at 22:25
  • Good idea. Thanks! – myname Oct 18 '21 at 22:25
  • 1
    Python integers can be of arbitrary size. But databases have more limited datatypes that are determined by the number of bytes they set aside to store the value. All DBMSs are explicit in their documentation about the range of values that a given datatype can store. – BoarGules Oct 18 '21 at 22:31

2 Answers2

1

INTEGER(255) doesn't do what you think.

Max value of an integer is 2147483647 (signed) or 4294967295 (unsigned).

See this answer for more detail What is the size of column of int(11) in mysql in bytes?

John Gordon
  • 29,573
  • 7
  • 33
  • 58
0

To store numbers up to 65 digits, you can use the decimal type. For instance, for integers with 25 digits, use decimal(25,0)

ysth
  • 96,171
  • 6
  • 121
  • 214