How to check if table exist? I would like to check if table exist then drop table. If not exist, create table.

My codes at this moment:

import MySQLdb, csv, sys
db = MySQLdb.connect("host","username","password","databasename" )
# prepare a cursor object using cursor() method
c = db.cursor()
# Drop table if it already exist using execute() method.
sql_drop = "DROP TABLE IF EXISTS Sentence"
c.execute(sql_drop)
# Create table as per requirement
sql = """CREATE TABLE Sentence(
     Id INT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(Id),
     Sentence TEXT(65535) NOT NULL)"""
c.execute(sql)
csv_data=csv.reader(file("textfile.txt"))
count = 0
for row in csv_data:
    count = count + 1
    #print count, row
    c.execute("INSERT INTO (Sentence) VALUES (%s)", row)
# disconnect from server
db.close()

Any suggest?

You could check ths old daniweb thread in PHP, here more verbal explanations out of DaniWeb:
http://www.electrictoolbox.com/check-if-mysql-table-exists/

I have not unfortunately personal experience of using them yet.

EDIT: actually there seems even to be Python discussions about topic here, like http://www.daniweb.com/software-development/python/threads/103597/python-mysql-for-checking-table-exist-or-not

Using try... except looks most sensible for me, ie trying to drop the table and just ignore if it does not exist before.

With Python's builtin module sqlite3 you have a CREATE TABLE IF NOT EXISTS command ...

# explore Python module sqlite3
# create a database file
# query language in upper case (optional)

import sqlite3
import os

# create/connect to a permanent database file
file_name = "stock_portfolio1.db3"
con = sqlite3.connect(file_name)

# establish the cursor
cur = con.cursor()

# if it doesn't exist yet, create the table named stocks
cur.execute('''CREATE TABLE IF NOT EXISTS stocks
    (date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)''')

# insert several lines at once using a
# list of (date, trans, symbol, qty, price) tuples
try:
    stocks = [
    ('2011-05-07', 'buy', 'AZN', 500, 55.05),
    ('2011-05-07', 'buy', 'CAT', 200, 110.34),
    ('2011-05-07', 'sell', 'IBM', 100, 168.89),
    ('2011-05-07', 'buy', 'GE', 2000, 20.01)
    ]
    cur.executemany("""INSERT INTO stocks
    values (?, ?, ?, ?, ?)""", stocks)
except:
    print("data exists?")
    pass

'''
# commit current data to the db file
# if it exist it would append possibly matching data again
# this scheme seems to create an unusable db file!
# if it is not committed it goes back to empty!!!
if not os.path.exists(file_name):
    con.commit()
'''
# commit current data to the db file
con.commit()

# test the database, in sorted order by symbol
cur.execute('SELECT * FROM stocks ORDER BY symbol')
# fetch all data rows
for row in cur.fetchall():
    print(row)

'''
(u'2011-05-07', u'buy', u'AZN', 500.0, 55.05)
(u'2011-05-07', u'buy', u'CAT', 200.0, 110.34)
(u'2011-05-07', u'buy', u'GE', 2000.0, 20.01)
(u'2011-05-07', u'sell', u'IBM', 100.0, 168.89)
'''

# done
con.close()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.