Deleting a MySQL table using a Python Program

The drop table SQL statement removes a table from the MySQL database along with the table rows. The Python program uses pymysql module to execute the SQL statements at the server.

MySQL Updated 29 Jun 2024 1 min read

Overview

  • A table in the MySQL database can be deleted using the Drop table SQL Statement.
  • Deleting a table removes all the rows from the MySQL table.
  • Any indexes of the table are also deleted, as the indexes will have nothing to point to after the rows of the table and the table are deleted.

Example

python
# Example Python program that deletes a table from a MySQL database

            # import MySQL client library

            import pymysql

# Database server connection details

            serverIP       = "127.0.0.1"

            userName       = "root"

            pwd            = "BanniInside1$"

            charSet2Use    = "utf8mb4"

            whichCursor    = pymysql.cursors.DictCursor

connection2DB   = pymysql.connect(host=serverIP, user=userName, password=pwd,

                                         charset=charSet2Use, cursorclass=whichCursor)

try:

                # Create a MySQL table

                dbCur            = connection2DB.cursor()

                createTableSQL     = """create table workplace.Albums(Id INT NOT NULL AUTO_INCREMENT,

                                    Album VARCHAR(128),

                                    Artist VARCHAR(128),

                                    Released DATE,

                                    Genre  VARCHAR(128),

                                    Length TIME,

                                    Producer VARCHAR(128),

                                    PRIMARY KEY (Id)

                                    )"""

                dbCur.execute(createTableSQL)

                showTablesSQL = "show tables in workplace"

                # Print the list of tables

                dbCur.execute(showTablesSQL)

                tableList = dbCur.fetchall()

                print(tableList)

    # Delete the table

                deleteTableSQL = "drop table workplace.Albums"

                dbCur.execute(deleteTableSQL)

    # Print the new list of tables

                dbCur.execute(showTablesSQL)

                tableList = dbCur.fetchall()

                print(tableList)

except Exception as e:

                print("Exception occured:{}".format(e))

finally:

                connection2DB.close()

Output

Output
[{'Tables_in_workplace': 'Albums'}]

()