MySQL parsed data missing tables

[DevashishX]

Describe the bug
When this data is parsed the table "student" is not returned by the parsed output.

DDL =

CREATE TABLE `department` (
  `dept_name` varchar(20) NOT NULL,
  `building` varchar(15) DEFAULT NULL,
  `budget` decimal(12,2) DEFAULT NULL,
  PRIMARY KEY (`dept_name`)
) ;
CREATE TABLE `student` (
  `ID` varchar(5) NOT NULL,
  `name` varchar(20) NOT NULL,
  `dept_name` varchar(20) DEFAULT NULL,
  `tot_cred` decimal(3,0) DEFAULT NULL,
  `timestamp` datetime NOT NULL DEFAULT '2009-04-01 08:00:00',
  PRIMARY KEY (`ID`),
  KEY `dept_name` (`dept_name`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL
) ;

To Reproduce
Steps to reproduce the behavior:

1. Run this code

from simple_ddl_parser import DDLParser
results_from_text = DDLParser(DDL).run(output_mode="mysql")
print(results_from_text)

3. Output on command line

[{'alter': {},
  'checks': [],
  'columns': [{'check': None,
               'default': None,
               'name': '`dept_name`',
               'nullable': False,
               'references': None,
               'size': 20,
               'type': 'varchar',
               'unique': False},
              {'check': None,
               'default': 'NULL',
               'name': '`building`',
               'nullable': True,
               'references': None,
               'size': 15,
               'type': 'varchar',
               'unique': False},
              {'check': None,
               'default': 'NULL',
               'name': '`budget`',
               'nullable': True,
               'references': None,
               'size': (12, 2),
               'type': 'decimal',
               'unique': False}],
  'index': [],
  'partitioned_by': [],
  'primary_key': ['`dept_name`'],
  'schema': None,
  'table_name': '`department`',
  'tablespace': None}]

Expected behavior
List of Dicts returned should contain both student and department tables

Desktop (please complete the following information):

• OS: Windows 10 22H2 64 bit
• Python: Python 3.10.15 | packaged by Anaconda, Inc. | (main, Oct 3 2024, 07:22:19) [MSC v.1929 64 bit (AMD64)] on win32

Additional context
just trying to parse a simple mysql DDL

[zhaoyuhong5]

Here is a brief explanation of using Python to deal with the issue of missing tables when parsing data from MySQL:

Prerequisites

First, make sure you have the mysql-connector-python library installed. This library enables Python to interact with MySQL databases. You can install it using pip install mysql-connector-python.

Code Example and Explanation

import mysql.connector

# Establish a connection to the MySQL database
mydb = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object to execute SQL queries
mycursor = mydb.cursor()

# Try to query the table that might be missing
table_name = "your_table_name"
query = f"SELECT * FROM {table_name}"
try:
    mycursor.execute(query)
    results = mycursor.fetchall()
    for row in results:
        print(row)
except mysql.connector.errors.ProgrammingError as e:
    if "Table 'your_table_name' doesn't exist" in str(e):
        print(f"The table {table_name} is missing. You may need to create it or check its existence in the database.")
    else:
        print(f"An error occurred: {e}")
finally:
    # Close the cursor and the connection
    mycursor.close()
    mydb.close()

1. Connection Establishment: We use the mysql.connector.connect function to set up a connection to the MySQL database by providing details like the host, user, password, and the specific database name.
2. Cursor Creation: A cursor object is created with mydb.cursor(). The cursor is used to execute SQL queries on the database.
3. Query Execution and Error Handling: We attempt to execute a SELECT query on the specified table. If the table is missing, MySQL will raise a ProgrammingError. In the except block, we check the error message to see if it indicates that the table doesn't exist. If so, we print a message suggesting that the table is missing and what actions could be taken. If there's another type of error, we print that error message as well.
4. Cleanup: In the finally block, we close the cursor and the database connection to release resources properly.

This is a simple Python approach to handle the situation where a table might be missing when parsing data from MySQL. Depending on the actual requirements, additional actions like creating the table or logging the error in a more detailed way can be implemented.