dd90p dd90p - 20 days ago 8
MySQL Question

How to use python statement to check the sql table is exist or not.?

I would like to know how to write the python statement to check whether the sql table is exist or not.

if the table is exist , then I will insert data to the table, otherwise, I will create the table.

The table name is "resulttable",

db = MySQLdb.connect("localhost","root","123","test")
cursor = db.cursor()

sql="""CREATE TABLE resulttable ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, writerName CHAR(20) NOT NULL, matchWords LONGTEXT, matchMagazine LONGTEXT, matchNews LONGTEXT )"""
cursor.execute(sql)
db.close()


how to do the checking part??

I add this statement, but I got an error:

sql="""CREATE TABLE IF NOT EXISTS resulttable ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, writerName CHAR(20) NOT NULL, matchWords LONGTEXT, matchMagazine LONGTEXT, matchNews LONGTEXT )"""
cursor.execute(sql)
db.close()


the error is:
Warning: Table 'resulttable' already exists
cursor.execute(sql)

Answer

Use the "TABLES" information schema view. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

SELECT * FROM information_schema.tables
WHERE table_name = 'YOUR TABLE'

You can apply this view to your code by doing something like the following:

def checkTableExists(dbcon, tablename):
    dbcur = dbcon.cursor()
    dbcur.execute("""
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_name = '{0}'
        """.format(tablename.replace('\'', '\'\'')))
    if dbcur.fetchone()[0] == 1:
        dbcur.close()
        return True

    dbcur.close()
    return False