search

widdowquinn / pyani

Application and Python module for average nucleotide identity analyses of microbes.
http://widdowquinn.github.io/pyani/
MIT License
192 stars 55 forks source link

SQLalchemy (or maybe SQLite3) errors for a missing database are unclear #289

Closed baileythegreen closed 2 years ago

baileythegreen commented 3 years ago

Summary:

If a database file is missing, or incorrectly specified, the error that is thrown is worse than just being uninformative, it's actively misleading.

Description:

When a database doesn't exist, the error returned doesn't say this; it says something about not being able to find a particular table within it.

Reproducible Steps:

Run a pyani command that updates or accesses the database, but specify a database file that does not exist.

Current Output:

(miniconda3) Arcadia:~/Software/fastANI-worktree$ pyani plot anim_test 1 --dbpath <missing database file> --formats png --method seaborn -l anim_test_plot.log
Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: runs

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/bin/pyani", line 11, in <module>
    load_entry_point('pyani', 'console_scripts', 'pyani')()
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/pyani_script.py", line 105, in run_main
    returnval = args.func(args)
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/subcommands/subcmd_plot.py", line 93, in subcmd_plot
    write_run_heatmaps(run_id, session, outfmts, args)
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/subcommands/subcmd_plot.py", line 113, in write_run_heatmaps
    session.query(pyani_orm.Run).filter(pyani_orm.Run.run_id == args.run_id).first()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3240, in first
    ret = list(self[0:1])
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
    return list(res)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3342, in __iter__
    return self._execute_and_instances(context)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3367, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: runs
[SQL: SELECT runs.run_id AS runs_run_id, runs.method AS runs_method, runs.cmdline AS runs_cmdline, runs.date AS runs_date, runs.status AS runs_status, runs.name AS runs_name, runs.df_identity AS runs_df_identity, runs.df_coverage AS runs_df_coverage, runs.df_alnlength AS runs_df_alnlength, runs.df_simerrors AS runs_df_simerrors, runs.df_hadamard AS runs_df_hadamard 
FROM runs 
WHERE runs.run_id = ?
 LIMIT ? OFFSET ?]
[parameters: ('1', 1, 0)]
(Background on this error at: http://sqlalche.me/e/e3q8)

Expected Output:

An error that says 'no database file found' would be good, with the name of the database file its looking for.

pyani Version:

pyani version: 0.3.0-alpha

installed dependencies

If you are running a version of pyani v0.3 or later, then please run the command pyani listdeps at the command line, and enter the output below.

Python Version:

3.8.3

Operating System:

High Sierra

widdowquinn commented 3 years ago

I'd prefer a more moderate tone for issue headings.

widdowquinn commented 3 years ago

As a general principle for the fix - it would make sense to me if pyani checked for existence of the database file when parsing the command-line arguments, and raised an appropriate error (logger.error() so it's written to the log and to STDERR) if it is not found.

baileythegreen commented 2 years ago

Another example where I forgot to make the database file, first:

! pyani anim -i scratch/small_test/ -o scratch/issue_342 --dbpath scratch/issue_342-2.db -v --debug -l issue_342.log --maxmatch
[INFO] [pyani.scripts.pyani_script]: Processed arguments: Namespace(citation=False, classes=None, dbpath=PosixPath('scratch/issue_342-2.db'), debug=True, disable_tqdm=False, filter_exe=PosixPath('delta-filter'), func=<function subcmd_anim at 0x1177fb5e0>, indir=PosixPath('scratch/small_test'), jobprefix='PYANI', labels=None, logfile=PosixPath('issue_342.log'), maxmatch=True, name=None, noextend=False, nofilter=False, nucmer_exe=PosixPath('nucmer'), outdir=PosixPath('scratch/issue_342'), recovery=False, scheduler='multiprocessing', sgeargs=None, sgegroupsize=10000, verbose=True, version=False, workers=None)
[INFO] [pyani.scripts.pyani_script]: command-line: /Users/baileythegreen/Software/miniconda3/bin/pyani anim -i scratch/small_test/ -o scratch/issue_342 --dbpath scratch/issue_342-2.db -v --debug -l issue_342.log --maxmatch
[INFO] [pyani.scripts.pyani_script]: pyani version: 0.3.0-alpha
[INFO] [pyani.scripts.pyani_script]: CITATION INFO
[INFO] [pyani.scripts.pyani_script]: If you use pyani in your work, please cite the following publication:
[INFO] [pyani.scripts.pyani_script]:    Pritchard, L., Glover, R. H., Humphris, S., Elphinstone, J. G.,
[INFO] [pyani.scripts.pyani_script]:    & Toth, I.K. (2016) 'Genomics and taxonomy in diagnostics for
[INFO] [pyani.scripts.pyani_script]:    food security: soft-rotting enterobacterial plant pathogens.'
[INFO] [pyani.scripts.pyani_script]:    Analytical Methods, 8(1), 12–24. http://doi.org/10.1039/C5AY02550H
[INFO] [pyani.scripts.pyani_script]: DEPENDENCIES
[INFO] [pyani.scripts.pyani_script]: The authors of pyani gratefully acknowledge its dependence on
[INFO] [pyani.scripts.pyani_script]: the following bioinformatics software:
[INFO] [pyani.scripts.pyani_script]:    MUMmer3: S. Kurtz, A. Phillippy, A.L. Delcher, M. Smoot, M. Shumway,
[INFO] [pyani.scripts.pyani_script]:    C. Antonescu, and S.L. Salzberg (2004), 'Versatile and open software
[INFO] [pyani.scripts.pyani_script]:    for comparing large genomes' Genome Biology 5:R12
[INFO] [pyani.scripts.pyani_script]:    BLAST+: Camacho C., Coulouris G., Avagyan V., Ma N., Papadopoulos J.,
[INFO] [pyani.scripts.pyani_script]:    Bealer K., & Madden T.L. (2008) 'BLAST+: architecture and applications.'
[INFO] [pyani.scripts.pyani_script]:    BMC Bioinformatics 10:421.
[INFO] [pyani.scripts.pyani_script]:    BLAST: Altschul, S.F., Madden, T.L., Schäffer, A.A., Zhang, J.,
[INFO] [pyani.scripts.pyani_script]:    Zhang, Z., Miller, W. & Lipman, D.J. (1997) 'Gapped BLAST and PSI-BLAST:
[INFO] [pyani.scripts.pyani_script]:    a new generation of protein database search programs.' Nucleic Acids Res.
[INFO] [pyani.scripts.pyani_script]:    25:3389-3402
[INFO] [pyani.scripts.pyani_script]:    Biopython: Cock PA, Antao T, Chang JT, Chapman BA, Cox CJ, Dalke A,
[INFO] [pyani.scripts.pyani_script]:    Friedberg I, Hamelryck T, Kauff F, Wilczynski B and de Hoon MJL
[INFO] [pyani.scripts.pyani_script]:    (2009) Biopython: freely available Python tools for computational
[INFO] [pyani.scripts.pyani_script]:    molecular biology and bioinformatics. Bioinformatics, 25, 1422-1423
[INFO] [pyani.scripts.subcommands.subcmd_anim]: Running ANIm analysis
[INFO] [pyani.scripts.subcommands.subcmd_anim]: MUMMer nucmer version: Darwin_3.1 (/Users/baileythegreen/Software/miniconda3/bin/nucmer)
[INFO] [pyani.scripts.subcommands.subcmd_anim]: Analysis name: ANIm_2021-10-01T11:35:44.266878
Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: runs

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/pyani/pyani/pyani_orm.py", line 481, in add_run
    session.commit()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1027, in commit
    self.transaction.commit()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 494, in commit
    self._prepare_impl()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 473, in _prepare_impl
    self.session.flush()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2470, in flush
    self._flush(objects)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2608, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2568, in _flush
    flush_context.execute()
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1136, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: runs
[SQL: INSERT INTO runs (method, cmdline, date, status, name, df_identity, df_coverage, df_alnlength, df_simerrors, df_hadamard) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('ANIm', '/Users/baileythegreen/Software/miniconda3/bin/pyani anim -i scratch/small_test/ -o scratch/issue_342 --dbpath scratch/issue_342-2.db -v --debug -l issue_342.log --maxmatch', '2021-10-01 11:35:44.266878', 'started', 'ANIm_2021-10-01T11:35:44.266878', None, None, None, None, None)]
(Background on this error at: http://sqlalche.me/e/e3q8)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/subcommands/subcmd_anim.py", line 190, in subcmd_anim
    run = add_run(
  File "/Users/baileythegreen/Software/pyani/pyani/pyani_orm.py", line 483, in add_run
    raise PyaniORMException(f"Could not add run {run} to the database")
pyani.pyani_orm.PyaniORMException: Could not add run Run None: ANIm_2021-10-01T11:35:44.266878 (2021-10-01 11:35:44.266878) to the database

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/bin/pyani", line 11, in <module>
    load_entry_point('pyani', 'console_scripts', 'pyani')()
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/pyani_script.py", line 126, in run_main
    returnval = args.func(args)
  File "/Users/baileythegreen/Software/pyani/pyani/scripts/subcommands/subcmd_anim.py", line 200, in subcmd_anim
    "Could not add run %s to the database (exiting)", run, exc_info=True
UnboundLocalError: local variable 'run' referenced before assignment