CODEWITHSUNDEEP
pythonmysqlsql-servermysql-python
dd90p
dd90p

Reputation: 513

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)

Upvotes: 1

Views: 3519

Answers (2)

Fujiao Liu
Fujiao Liu

Reputation: 2253

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

 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)
 # add insert staff
 # insert_sql = "inset ..."
 # cursor.execute(sql)
 cursor.close()
 db.close()

if you want check table if exists anyway, check this python - how to check if table exists?

Upvotes: 1

New Bie
New Bie

Reputation: 68

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

Upvotes: 1

Related Questions