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

Question

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??

Wondercricket · Accepted Answer · 2017-06-07 16:23:31Z

4

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()

edited Jun 7, 2017 at 16:23

answered Jun 7, 2017 at 16:19

Wondercricket

7,9222 gold badges44 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Over a year ago

That did it. Thankyou

Joe Over a year ago

It was the fetchall() line I missed. I know I have done this a bunch of times before without the loop. fetchall() fixed it. Thankyou. Ill mark this as correct once the timer lets me

Wondercricket Over a year ago

@Joe I'm glad I could be of assistance

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related