How to check for table's existance without a separate query

[Zsolt Ero]

Hi,

I'm looking for a way to check for a table's existance. I know I can do it in 2 queries, once for checking the table and one for running the query, but I'd prefer to do it in one query with a try-except block.

What is the best way to do this? I'm using psycopg2 and I see that the raised error is "psycopg2.errors.UndefinedTable". However I cannot catch for this error, I can only catch for "sqlalchemy.exc.ProgrammingError".

How can I catch specifically for psycopg2.errors.UndefinedTable inside sqlalchemy.exc.ProgrammingError?

Or is there a better way to check for a table's existance? Since it'll be in an API I'd prefer to keep the query count low.

[Zsolt Ero]

My best idea so far is

if 'psycopg2.errors.UndefinedTable' in str(err)

Just to note, I'm looking especially for psycopg2.errors.UndefinedTable, I'm not using any other DB.

[Simon King]

Rather than looking at the string representation, you could inspect
the "orig" attribute of the SQLAlchemy exception:

https://docs.sqlalchemy.org/en/13/core/exceptions.html#sqlalchemy.exc.DBAPIError

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3d103596-199a-43bc-aa96-da30e892a33d%40googlegroups.com.

[Zsolt Ero]

Thanks. So is the following code correct for psycopg2 specific scenario?

try:
res = conn.execute(stmt)
except sa.exc.ProgrammingError as err:
if isinstance(err.orig, psycopg2.errors.UndefinedTable):
print('Table does not exist')
else:
raise err

[Jonathan Vanasco]

Personally, I would handle the check like this:

ERRORS_UNDEFINED_TABLE = (psycopg2.errors.UndefinedTable, )

try:
    res = conn.execute(stmt)
except sa.exc.ProgrammingError as err:

    if isinstance(err.orig, ERRORS_UNDEFINED_TABLE):
        print('Table does not exist')
    raise

This would allow you to update the `ERRORS_UNDEFINED_TABLE` tuple in a central place, and allow you to more easily catch this situation in other databases if needed.  I've used this technique in a few projects that are built for postgres, but support mysql and use sqlite for some tests.

[James Fennell]

>  However I cannot catch for this error, I can only catch for "sqlalchemy.exc.ProgrammingError".

Why is that?

James

--

SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f936b2c1-bdcf-4372-8943-00a31615d70a%40googlegroups.com.