Geek asks and answers

Check for missing optional tables

I am using SQLAlchemy + Pyramid to work with my database. However, there are several optional tables that are not always expected in the database. Therefore, when requesting them, I try to catch such cases withNoSuchTableError

try:
    x = session.query(ABC.name.label('sig_name'),func.count('*').label('count_')).join(DEF).join(MNO).filter(MNO.relevance >= relevance_threshold).group_by(DEF.signature).order_by(desc('count_')).all()[:val]
except NoSuchTableError:
    x = [-1,]

But when I execute this statement, I get a ProgramError

ProgrammingError: (ProgrammingError) (1146, "Table 'db.mno' doesn't exist")

Why does SQLAlchemy raise a more general ProgrammingError instead of a more specific NoSuchTableError? And if this is really the expected behavior, how can I make sure that the application displays the correct information depending on the presence / absence of tables?

EDIT1

Since this is part of my webapp, the DB model is in models.py(under my webapp pyramid). I have a parameter in my .ini file that asks the user to choose whether additional tables are available or not. But not trusting the user, I want to be able to check for myself (in the views) whether the table exists or not. The controversial table is something like (c models.py)

class MNO(Base):
    __tablename__="mno"
    id=Column(Integer,primary_key=True,autoincrement=True)
    sid=Column(Integer)
    cid=Column(mysql.MSInteger(unsigned=True))
    affectability=Column(Integer)
    cvss_base=Column(Float)
    relevance=Column(Float)
    __table_args__=(ForeignKeyConstraint(['sid','cid',],['def.sid','def.cid',]),UniqueConstraint('sid','cid'),)

How and where should a check be done so that a variable can be set (preferably during application setup) that tells me if tables are present or not?

Note: in this case, I would have to try if ... else and not "apologize"

+3
source share
1 answer

sqlalchemy, NoSuchTableError , "SQLAlchemy [] , ". , .

- " ":

try:
    table = Table(table_name, MetaData(engine)) 
except NoSuchTableError:
    pass

, :

Edit:

, has_table:

if engine.dialect.has_table(connection, table_name):
    #do your crazy query

Inspector, ?

, - :

from sqlalchemy import create_engine
from sqlalchemy.engine import reflection
#whatever code you already have
engine = create_engine('...')
insp = reflection.Inspector.from_engine(engine)
table_name = 'foo'
table_names = insp.get_table_names()
if table_name in table_names:
    x = session.query(ABC.name.label('sig_name'),func.count('*').label('count_')).join(DEF).join(MNO).filter(MNO.relevance >= relevance_threshold).group_by(DEF.signature).order_by(desc('count_')).all()[:val]
+6

More articles:


All Articles