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?

Edited 4 Years Ago by thanadaray

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.

Edited 4 Years Ago by pyTony

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()
This article has been dead for over six months. Start a new discussion instead.