CODEWITHSUNDEEP
pythonsqlexists
tadm123
tadm123

Reputation: 8787

How to check if a table exists in Python?

I'm currently going over a course in Web design. What I want to do is to check if a table named portafolio exists in my database, if not I want to create one. I'm using Python (flask) and sqlite3 to manage my database. So far I the some of the logic in SQL to create a table if it doesn't exist:

# db is my database variable
db.execute('''create table if not exists portafolio(id INTEGER PRIMARY KEY AUTOINCREMENT, 
                                              stock TEXT, 
                                              shares INTEGER, 
                                              price FLOAT(2), 
                                              date TEXT
                                             ''');

But instead of using SQL commands I'd like to know how would I do the exact same checking in Python instead, since it would look a lot cleaner.

Any help would be appreciated.

Upvotes: 2

Views: 9545

Answers (2)

mrKindo
mrKindo

Reputation: 53

If you are looking for a neat job with Database operations, I advice you learn more about ORM(Object Relation Model).

I use Flask with SQLAlchemy. You can use python classes to manage SQL operations like this:

class Portafolio(db.Model):
   id = db.Column(db.Integer, primary_key=True)
   stock = db.Column(db.String(255), unique=True)
   shares = db.Column(db.Integer, unique=True)

It does all the database checks and migration easily.

Upvotes: 1

alecxe
alecxe

Reputation: 473863

Not sure about which way is cleaner but you can issue a simple select and handle the exception:

try:
    cursor.execute("SELECT 1 FROM portafolio LIMIT 1;")
    exists = True
except sqlite3.OperationalError as e:
    message = e.args[0]
    if message.startswith("no such table"):
        print("Table 'portafolio' does not exist")
        exists = False
    else:
        raise

Note that here we have to check what kind of OperationalError it is and, we have to have this "not pretty" message substring in a string check because there is currently no way to get the actual error code.

Or, a more SQLite specific approach:

table_name = "portafolio"
cursor.execute("""
    SELECT name 
    FROM sqlite_master 
    WHERE type='table' AND name=?;
""", (table_name, ))

exists = bool(cursor.fetchone())

Upvotes: 6

Related Questions