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

[wes weston]

Tim Williams wrote:
> 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.
> 
>>>>import MySQLdb
>>>>db=MySQLdb.connect(unix_socket='/tmp/mysql.sock')
>>>>c=db.cursor()
>>>>c.execute('show databases')
> 
> 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:
> 
> 
>>>>c.fetchall()
> 
> 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
> 
>>>>c.execute('show databases')
> 
> 2L
> 
>>>>c.execute('show databases')
> 
> 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
> 
>>>>c.execute('show databases')
> 
> 2L
> 
>>>>c.fetchall()
> 
> (('menagerie',), ('test',))
> 
> 
> Thanks in advance for any help.
> 
> ----
> Tim Williams

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