INSERT table names using SELECT statement in MySQL

In MySQL, I know I can list the tables in a database with:

SHOW TABLES

But I want to insert a table name into a specified table, for example:

INSERT INTO dataset_names (dataset)
SELECT table_name FROM information_schema.tables
WHERE table_schema = '%s';

But when I execute the above statement, the dataset_names table does not get updated. I created the master table using:

CREATE TABLE dataset_names (id INT AUTO_INCREMENT PRIMARY KEY, dataset text);

Here is the python code.

    dataset_name_query = """
                        INSERT INTO dataset_names (dataset) values(
                        SELECT table_name FROM information_schema.tables
                        WHERE table_schema = '%s');
                        """% (tablename)
    
    csv_obj.read()
    csv_obj.create()
    cursor = createConnection()
    #Cursor executing the dataset insert into master table query
    cursor.execute(dataset_name_query)
    closeCursor(cursor)

Answer

INSERT INTO table_names(name) (SELECT table_name FROM information_schema.tables WHERE table_schema='database_name')

Here the order on insert values and select values must match;

Advertisement

Answer