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)
Advertisement
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;