How to check if a table exists using python?

Question

I want to check whether the table exists or not before inserting the data. This is what i have tried:

def checkTables(tablename):
    stmt = "SHOW TABLES LIKE %s"%tablename          
    cursor.execute(stmt)
    result = cursor.fetchone()          
    return result

But it gives me error saying:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ctg_payload3' at line 1

Is your syntax right for the parameter? See e.g. http://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python depending on which db you are using, e.g. maybe `cursor.execute("SHOW TABLES LIKE ?", (tablename))` — doctorlove, Mar 28 '17 at 11:45

chile · Accepted Answer · 2017-03-28T11:51:55.787

0

Maybe it is not the best way.

As for my opinion, I will show tables;, then search the result.

And, I you cannot execute show tables like tablename;, no syntax like that.

edit 1

If you must do it in sql, use

 show table status like 'table_name';

' is needed for this sql.

edited Mar 28 '17 at 11:51

answered Mar 28 '17 at 11:41

chile

141
1
12

You're welcome. And don't forget mark the answer when you solve the problem. – chile Mar 30 '17 at 02:10

kourouma_coder · Answer 2 · 2017-03-28T11:52:25.120

0

Try this query string :SHOW TABLES WHERE Tables_in_mydb LIKE '%tablename%' or

this one

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'my_database_name'
AND table_name LIKE '%tablename%'

Good luck

edited Mar 28 '17 at 11:52

answered Mar 28 '17 at 11:45

kourouma_coder

1,078
2
13
24

score 0 · Answer 3 · answered Mar 28 '17 at 12:15

0

Try this.

def checkTables(tablename):
    stmt = "SHOW TABLES LIKE '%s' "% ('%'+str(tablename)+'%')
    cursor.execute(stmt)
    result = cursor.fetchone()          
    return result

answered Mar 28 '17 at 12:15

Abhishake Gupta

2,939
1
25
34

How to check if a table exists using python?

3 Answers3