pymysql ipython notebook - error during SQL query resultset fetchall()

765 views Asked by At

Given a MySQL database of Wordpress 4.0. I'm running the following query on the database:

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='geheim', db='mysite')

cur = conn.cursor()
cur1 = conn.cursor()

cur.execute("select table_name,column_name from information_schema.columns where table_schema = 'mysite' order by table_name,ordinal_position")


tables=Set()
columns=Set()
for r in cur.fetchall():
    cur1.execute("select " + r[1] + " from " + r[0] )
    for s in cur1.fetchall():
        print r[0],r[1],s
    tables.add(r[0])
    columns.add(r[1])

print tables

for t in tables:
    print t # to find out in which table the request crashes
    cur1.execute("SELECT table_name,column_name FROM information_schema  WHERE table_name='\" + `t` + \"'\"")
    for r in cur1.fetchall():
        print r

When I run this code in an ipython notebook (but also as python file.py on a DOS command line) I'm getting an error quite at the end of the request:

wp_usermeta <<<<<<<<<<<<<<<<<<<<< this seems to be the table in which the error
            is occuring.

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-42-bcb10f00a985> in <module>()
     32 for t in tables:
     33     print t
---> 34     cur1.execute("SELECT table_name,column_name FROM information_schema  WHERE table_name='\" + `t` + \"'\"")
     35     for r in cur1.fetchall():
     36         print r

C:\Users\me\Anaconda\lib\site-packages\pymysql\cursors.pyc in execute(self, query, args)
    130             query = query % self._escape_args(args, conn)
    131 
--> 132         result = self._query(query)
    133         self._executed = query
    134         return result

C:\Users\me\Anaconda\lib\site-packages\pymysql\cursors.pyc in _query(self, q)
    269         conn = self._get_db()
    270         self._last_executed = q
--> 271         conn.query(q)
    272         self._do_get_result()
    273         return self.rowcount

C:\Users\me\Anaconda\lib\site-packages\pymysql\connections.pyc in query(self, sql, unbuffered)
    724             sql = sql.encode(self.encoding)
    725         self._execute_command(COM_QUERY, sql)
--> 726         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    727         return self._affected_rows
    728 

C:\Users\me\Anaconda\lib\site-packages\pymysql\connections.pyc in _read_query_result(self, unbuffered)
    859         else:
    860             result = MySQLResult(self)
--> 861             result.read()
    862         self._result = result
    863         if result.server_status is not None:

C:\Users\me\Anaconda\lib\site-packages\pymysql\connections.pyc in read(self)
   1062     def read(self):
   1063         try:
-> 1064             first_packet = self.connection._read_packet()
   1065 
   1066             # TODO: use classes for different packet types?

C:\Users\me\Anaconda\lib\site-packages\pymysql\connections.pyc in _read_packet(self, packet_type)
    824         """
    825         packet = packet_type(self)
--> 826         packet.check_error()
    827         return packet
    828 

C:\Users\me\Anaconda\lib\site-packages\pymysql\connections.pyc in check_error(self)
    368             errno = unpack_uint16(self.read(2))
    369             if DEBUG: print("errno =", errno)
--> 370             raise_mysql_exception(self._data)
    371 
    372     def dump(self):

C:\Users\me\Anaconda\lib\site-packages\pymysql\err.pyc in raise_mysql_exception(data)
    114 def raise_mysql_exception(data):
    115     errinfo = _get_error_info(data)
--> 116     _check_mysql_exception(errinfo)

C:\Users\me\Anaconda\lib\site-packages\pymysql\err.pyc in _check_mysql_exception(errinfo)
    107     errorclass = error_map.get(errno, None)
    108     if errorclass:
--> 109         raise errorclass(errno,errorvalue)
    110 
    111     # couldn't find the right error number

ProgrammingError: (1064, u'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"\' at line 1')

I don't see an obvious reason why the request should bail out just at this point. When I change the amount of output a bit, the command bails out at other tables. At this point the output has reached about 800KB and around 11000 lines.

Could this be a mass problem resp. a bug in pymysql? I could write a MySQL function and try to execute it in mysql directly, but I'm not skilled in writing MySQL functions or procedures. Could someone give me start? Thanks.

0

There are 0 answers