How to check if a table exists in Python?

Question

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.

score 6 · Accepted Answer · edited May 23 '17 at 12:00

6

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())

edited May 23 '17 at 12:00

Community

1
1

answered Jan 17 '17 at 02:07

alecxe

462,703
120
1,088
1,195

2

nit for the exception solution. `OperationalError` could be raised if the reason is *not* because the table exists. For example, the query might have a syntax issue, it still qualifies as an `OperationalError`. You might need to dive a bit deeper in to a more specific exception to figure out *why*, exactly. – idjaw Jan 17 '17 at 02:10
1

@idjaw yeah, good point, was thinking about this, I'll see if I can get the actual error code. thanks. – alecxe Jan 17 '17 at 02:10
just a quick question is `cursor` the same as the `db` variable on the OP? or is it a some different function (and if it is, do I need to import a module that comes with it)? – tadm123 Jan 17 '17 at 02:15
1

@alecxe :( According to [this](http://stackoverflow.com/a/38847510/1832539), the error codes that would have been helpful are not exposed. The best and not ideal bet, would be to look for some match like "does not exist" in the string. – idjaw Jan 17 '17 at 02:15
@alecxe ha. My comment came right at your edit. Great. :) – idjaw Jan 17 '17 at 02:16
@tadm123 it looks like `db` is a cursor instance in your case. I initialize my `cursor` as `cursor = db.cursor()` where `db = sqlite3.connect(':memory:')` in my case.. – alecxe Jan 17 '17 at 02:17

score 1 · Answer 2 · answered Jan 17 '17 at 02:16

1

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.

answered Jan 17 '17 at 02:16

mrKindo

53
11

Yeah I know a bit about it and it's a lot easier but the online course that I'm going through tells you to do it in SQL, very tedious.. – tadm123 Jan 17 '17 at 02:19

How to check if a table exists in Python?

2 Answers2