CODEWITHSUNDEEP
pythonmysql
Pranab
Pranab

Reputation: 2797

DROP TABLE IF EXISTS says "Unknown table"? [MySQL]

In my python script, when I try to DROP a table that does not exist, using DROP ... IF EXISTS, I still get an error. 

import mysql.connector
cnx = mysql.connector.connect(**my_database_config)
cursor = cnx.cursor()
cursor.execute("DROP TABLE IF EXISTS nonexistent_table;")

Why is this so?

mysql.connector.errors.DatabaseError: 1051: Unknown table 'mydb.nonexistent_table'

The full error:

  File "myfile.py", line 199, in myfunction
    cursor.execute("DROP TABLE IF EXISTS nonexistent_table;")
  File "\venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "\venv\lib\site-packages\mysql\connector\cursor.py", line 470, in _handle_result
    self._handle_noresultset(result)
  File "\venv\lib\site-packages\mysql\connector\cursor.py", line 440, in _handle_noresultset
    self._warnings[0][1], self._warnings[0][2])
mysql.connector.errors.DatabaseError: 1051: Unknown table 'mydb.nonexistent_table'

In this related question the user seems to be getting a Warning instead of an Error for similar code.

Upvotes: 0

Views: 3481

Answers (4)

Owen1998
Owen1998

Reputation: 1

I think 'warning' is not standing for 'mistake'. It's just a kind of tip. And actually, if you are going to create 'nonexistent_table', the warning will not hinder this process. Your 'nonexistent_table' will be created without stopping. Clearly, the 'warning' makes us feel uneasy, although it doesn't block our process, we still want to make it disappear. So, you can try this to achieve it.

import warnings
warnings.filterwarnings('ignore')

Upvotes: 0

ysth
ysth

Reputation: 98398

You say it shows as a warning in phpmyadmin. So your client configuration is set to treat warnings as errors.

See https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-raise-on-warnings.html and then either disable that or catch the error and disregard it.

Upvotes: 1

furkanayd
furkanayd

Reputation: 872

This seems like an MySQL related issue. So, your SQL statement:

DROP TABLE IF EXISTS nonexistent_table;

is in correct Syntax Drop Table From MySQL:

DROP [TEMPORARY] TABLE [IF EXISTS] 
      tbl_name [, tbl_name] 
      [RESTRICT | CASCADE]

but you need to sure on some subjects:

  1. You must have the DROP privilege for each table.
  2. If any tables named in the argument list do not exist, DROP TABLE behavior depends on whether the IF EXISTS clause is given:
    • Without IF EXISTS, the statement fails with an error indicating which nonexisting tables it was unable to drop, and no changes are made.
    • With IF EXISTS, no error occurs for nonexisting tables. The statement drops all named tables that do exist, and generates a NOTE diagnostic for each nonexistent table. These notes can be displayed with SHOW WARNINGS. See Section 13.7.7.40, “SHOW WARNINGS Statement”.

Better to check MySQL Docs corresponding to your version.

Upvotes: 0

jared
jared

Reputation: 483

The warning is supposed to be there to let you handle what should happen if it does not exist. It's not an error.

Upvotes: 1

Related Questions