0

I am trying to use MySQL Connector/Python to work with a database. Connection is establishing succesfully, e.g. I was able to insert new data to the table. And SELECT queries are working just fine with normal integer or char values.

But when I try to execute SELECT query with one of the columns being int(small, medium, big, doesn't matter) I meet ValueError: invalid literal for int() with base 0: '00002'

How to repeat:

  • Create a table where one of the columns of type int unsigned zerofill (or any other integer type with zerofill). For example, do CREATE TABLE test_table (id INT UNSIGNED ZEROFILL); and then INSERT INTO test_table VALUES (1), (2), (3);

  • Try to do cursor.execute(query) with SELECT query that includes zerofill field, e.g. SELECT * FROM test_table;

What should happen: program outputs

ValueError: invalid literal for int() with base 0: '0000000001'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\user\Documents\testSQL.py", line 25, in <module>
    cursor.execute(query)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 272, in execute
    self._handle_result(result)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 163, in _handle_result
    self._handle_resultset()
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 651, in _handle_resultset
    self._rows = self._cnx.get_rows()[0]
  File "C:\Users\user\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 273, in get_rows
    row = self._cmysql.fetch_row()
SystemError: <built-in method fetch_row of _mysql_connector.MySQL object at 0x000002555DDBC350> returned a result with an error set

It looks like mysql-connector is trying to do int(some_number, base=0) when converts database integer to python int() value. But it actually is a number with base 10. What can I do to avoid such an error?

Andrew Vee
  • 1
  • 1
  • Could you include an example SQL query that you are trying to send? – Tomasz Swider Dec 17 '18 at 16:20
  • @TomaszSwider Just a usual `SELECT * FROM test_table;` for example. Where test_table has zerofill column of course, even if it is the only column – Andrew Vee Dec 17 '18 at 16:23
  • Do you need the zerofill thing in MySQL? Maybe create your tables with out it, if you need it in the display you can do zero filing with the format function in python. https://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql – Tomasz Swider Dec 17 '18 at 16:30
  • @TomaszSwider Yes, of course, I could do that. It just bothers me that mysql-connector has this unexpected unwanted behaviour with so simple queries. Makes me think there is some troubles with my mysql-connector installation and new errors will apear. Can you confirm that this is its usual behaviour and I don't need to worry? – Andrew Vee Dec 17 '18 at 16:38
  • What is the version of mysql-connector you have installed ? – Tomasz Swider Dec 17 '18 at 16:45
  • @TomaszSwider version 8.0.13 for Python 3.6 on Windows 10 64 bit – Andrew Vee Dec 17 '18 at 17:00
  • Sorry but I do not have windows to install it on, It might be a bug in the connector or in mysql server version that you have. These things happen, and yes new errors will definitely appear it is called programming :). My point here is just remove the zero fill (that you do not need) from your table definition and you will be fine. – Tomasz Swider Dec 17 '18 at 17:12

0 Answers0