not able to print contents of sql table in python using pretty table

Question

import os
import time
import mysql.connector as ms
from prettytable import from_db_cursor

mydb=ms.connect(host="localhost",user="root",passwd="12345")   
mycursor=mydb.cursor()
x=input("DATABASE NAME : ")
mycursor.execute("show databases")
result=mycursor.fetchall()

if (x,) in result:
    mydb=ms.connect(host="localhost",user="root",passwd="12345",database=x)
    mycursor=mydb.cursor()
    if mydb.is_connected():
        print("...........connection established.............")
else:
    print("no database")

def display():
    mycursor.execute("select * from student")
    result=mycursor.fetchall()
    print("""
    no OF RECORDS IS: """,mycursor.rowcount)
    print("""
              """)
    if mycursor.rowcount>0:
        x = from_db_cursor(mycursor)
        print(x)
    else:
        print("the table is empty")

display()
    

while executing i am getting the following table but it dont have any contents in it which i saved in the table it just shows heading of the tables only please help me with this

Answer 1

The from_db_cursor() function uses the fetchall() method. You can find it here.

But since we have a fetchall() in line 22 (i.e., below the query execution in display( ) function), there's nothing left to fetch after that. So, to get the rows again, you'd have to execute the same query again (without another fetchall()).

So, the new display( ) should look like:

def display():
    mycursor.execute("select * from student")
    result=mycursor.fetchall()
    print("""
    no OF RECORDS IS: """,mycursor.rowcount)
    print("""
              """)
    if mycursor.rowcount>0:
        mycursor.execute("select * from student")
        x = from_db_cursor(mycursor)
        print(x)
    else:
        print("the table is empty")

You may read more about fetchall() here.