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.