MySQLdb returns "ValueError: invalid literal for float():" for cursor.execute('show databases')

[Tim Williams]

I'm trying to write a simple python program to access a MySQL
database. I'm having a problem with using MySQLdb to get the results
of a SQL command in a cursor. Sometimes the cursor.execute works,
sometimes not.

From mysql:

mysql> show databases;
+-----------+
| Database |
+-----------+
| menagerie |
| test |
+-----------+
2 rows in set (0.09 sec)


This is the database that comes with the MySQL tutorial. I'm trying
to keep things simple here.

When I try the same thing in Python 2.3.2 using MySQLdb I get:

Python 2.3.2 (#6, Dec 10 2003, 08:44:50)
[GCC 3.2 20020903 (Red Hat Linux 8.0 3.2-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 95, in execute
return self._execute(query, args)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 114, in _execute
self.errorhandler(self, exc, value)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for float(): menagerie
It seems that if I do a c.fetchall(), I can at least do c.execute
every *other* time:
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 274, in fetchall
self._check_executed()
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 53, in _check_executed
self.errorhandler(self, ProgrammingError, "execute() first")
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: execute() firstTraceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 95, in execute
return self._execute(query, args)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 114, in _execute
self.errorhandler(self, exc, value)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for float(): menagerie
Thanks in advance for any help.

 

[wes weston]

I'm trying to write a simple python program to access a MySQL
database. I'm having a problem with using MySQLdb to get the results
of a SQL command in a cursor. Sometimes the cursor.execute works,
sometimes not.

From mysql:

mysql> show databases;
+-----------+
| Database |
+-----------+
| menagerie |
| test |
+-----------+
2 rows in set (0.09 sec)


This is the database that comes with the MySQL tutorial. I'm trying
to keep things simple here.

When I try the same thing in Python 2.3.2 using MySQLdb I get:

Python 2.3.2 (#6, Dec 10 2003, 08:44:50)
[GCC 3.2 20020903 (Red Hat Linux 8.0 3.2-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 95, in execute
return self._execute(query, args)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 114, in _execute
self.errorhandler(self, exc, value)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for float(): menagerie


It seems that if I do a c.fetchall(), I can at least do c.execute
every *other* time:


Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 274, in fetchall
self._check_executed()
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 53, in _check_executed
self.errorhandler(self, ProgrammingError, "execute() first")
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: execute() first

2L

Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 95, in execute
return self._execute(query, args)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/cursors.py",
line 114, in _execute
self.errorhandler(self, exc, value)
File "/project/c4i/Users_Share/williams/Linux/lib/python2.3/site-packages/MySQLdb/connections.py",
line 33, in defaulterrorhandler
raise errorclass, errorvalue
ValueError: invalid literal for float(): menagerie

2L

(('menagerie',), ('test',))


Thanks in advance for any help.

Tim,
I tried this code and, as you can see, it worked:

import MySQLdb

db = MySQLdb.connect(user="wes", passwd="?????",db="PortfolioMySql",port=3306,unix_socket="/tmp/mysql.sock")
cursor= db.cursor()
sql = "show databases"
cursor.execute(sql)
while 1:
t = cursor.fetchone()
if not t:
break
print t
cursor.close()
db.close()

('PortfolioMySql',)
('mysql',)
('test',)

wes

 

[Tim Williams]

(snip)


Tim,
I tried this code and, as you can see, it worked:

import MySQLdb

db = MySQLdb.connect(user="wes", passwd="?????",db="PortfolioMySql",port=3306,unix_socket="/tmp/mysql.sock")
cursor= db.cursor()
sql = "show databases"
cursor.execute(sql)
while 1:
t = cursor.fetchone()
if not t:
break
print t
cursor.close()
db.close()


wes

I think I found the problem. I installed mysql and MySQLdb in
non-standard places so I wouldn't need root access. (I'm not an
admin.) It turns out that the switch --enable-thread-safe-client is
*not* used by default in the mysql install, but the default for
MySQLdb is to assume that it is used. What was happening was that
MySQL db was loading in /usr/lib/mysql/libmysqlclient_r.so.10 since my
install dir for mysql didn't have that library built.

If I change the MySQL setup.py to

# set this to YES if you have the thread-safe mysqlclient library
thread_safe_library = NO

and reinstall it, things work.

I'm working on building my version of mysql with
--enable-thread-safe-client and MySQLdb with thread_safe_library =
YES to see if this works too. I expect so.