Search code examples
pythonmysqlpython-3.xpymysql

MySQL 'SHOW TABLES' Returns count instead of list (Python)

I'm troubleshooting a script I am using to query the database. To make sure I had everything working right I stripped it down to a simple 'SHOW TABLES' query. The problem is that it is returning a count of the tables instead of the list of names it should return. 

import pymysql

connection = pymysql.connect(host='10.0.0.208', user='admin', passwd='Passwrd')

cursor = connection.cursor()
sqlstring = 'SHOW TABLES;'
cursor.execute('USE CustDB')
x = cursor.execute(sqlstring)

print(x)

This is only returning '17'. What am I missing??

Solution

  • Per the documentation, execute returns the number of rows affected

    Returns: Number of affected rows

    In order to get the desired results, you need to loop through the cursor

    cursor.execute('USE CustDB')
tables = [c for c in cursor]

    or use fetchall

    cursor.execute('USE CustDB')
tables = cursor.fetchall()