Get column names and last record of all tables in MySQL

Question

Is there a way to get the latest values of all tables and their column names instead of selecting each table. I came across the following select query but it only returns column names and if I use * instead of column_name there is a lot of unnecessary details that I don't need.

SELECT column_name 
FROM information_schema.columns 
where table_schema = 'classicmodels'  
order by table_name, ordinal_position

I only need the column name that contains the latest record in that column.

P粉211273535 · Answer

I can read the latest records of all tables using phyton sql connector. There may be a better way to do this, but since I'm not allowed to work in a running database, I chose this method.

import logging
import mysql.connector

mydb = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root",
    database="classicmodels")

mycursor = mydb.cursor(buffered=True , dictionary=True)

sql = "SELECT * FROM information_schema.tables where table_schema = 'classicmodels'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
tables = [d['TABLE_NAME'] for d in myresult]

for x in tables:
    sql1 = "select * from {}".format(x)
    mycursor.execute(sql1)
    myresult1 = mycursor.fetchone()
    for val, cal in myresult1.items():
        print(f'{val} is {cal}')