2

I take a successful SELECT request for mysql but one row is a number but python converts it to an integer and the result is that the zero was removed from beginning. But I want that the zero isn't removed from beginning. Is there a way to do it?

My Code

sql = mysql.connector.connect(user='XXX', password='XXX',
                            host='localhost',
                            database='database'
                            )
cursor = sql.cursor()
cursor = sql.cursor(dictionary=True)
cursor.execute("SELECT * FROM database WHERE language='de' AND searchnumber='0290661' ")
myresult = cursor.fetchall()

for row in myresult: searchnumber = row['searchnumber'] # searchnumber = str(row['searchnumber']) has no effect

print(searchnumber) # return 290661 but it should be 0290661

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
AndyMan
  • 49
  • 5
  • The column type is likely integer, hence the automatic conversion by the mysql connector. This may help: https://stackoverflow.com/a/11165118/771848. – alecxe Nov 30 '19 at 18:09
  • Can you show the table schema? – snakecharmerb Nov 30 '19 at 18:52
  • columns are: id, searchnumber, content All columns are text – AndyMan Nov 30 '19 at 19:07
  • I can reproduce: using mysql-connector, values of TEXT columns containing only digits are returned with the BLOB type, that is they are effectively returned as ints. This seems to be a mysql-connector issue: using pymysql they are returned as strings, with all characters preserved. – snakecharmerb Dec 01 '19 at 10:20

1 Answers1

3

I have experienced something similar; data from a TEXT column being intermittently interpreted in Python as a numeric value. In my case the text was a 12-digit hex value and the problem occurred when the hex value had the form:

   XXXXXXXeXXXX

Where:

  • X = decimal digits 0-9
  • e = literally the 'e' character

In this situation the connector seems to interpret this as an integer with an exponent. Needless to say this (usually) produced a numeric overflow interpreted as 'Inf' which didn't have the desired result.

Today I found a change to the MySQL Connector for Python that might be relevant. Below is a link to the release note which describes a fix that seems relevant.

https://dev.mysql.com/doc/relnotes/connector-python/en/news-8-0-21.html

Please try this and share your experience with the group...

Dwight
  • 173
  • 2
  • 9