Debugging (displaying) SQL command sent to the db by SQLAlchemy

I have an ORM class called Person, which wraps around a person table:

After setting up the connection to the db etc, I run the statement:

people = session.query(Person).all()

The person table does not contain any data (as yet), so when I print the variable people, I get an empty list.

I renamed the table referred to in my ORM class People, to people_foo (which does not exist).

I then run the script again. I was surprised that no exception was thrown when attempting to access a table that does not exist.

I therefore have the following 2 questions:

1. How may I setup SQLAlchemy so that it propagates db errors back to the script?
2. How may I view (i.e. print) the SQL that is being sent to the db engine?

If it helps, I am using PostgreSQL.

[Edit]

I am writing a package. In my __main__.py script, I have the following code (shortened here):

### __main__.py
import common # imports logging and defines logging setup funcs etc

logger = logging.getLogger(__name__)


def main():    
    parser = OptionParser(usage="%prog [options] <commands>",
                          version="%prog 1.0")

    commands = OptionGroup(parser, "commands")

    parser.add_option(
        "-l",
        "--logfile",
        dest="logfile",
        metavar="FILE",
        help="log to FILE. if not set, no logging will be done"
    )

    parser.add_option(
        "--level",
        dest="loglevel",
        metavar="LOG LEVEL",
        help="Debug level. if not set, level will default to low"
    )

    # Set defaults if not specified
    if not options.loglevel:
        loglevel = 1
    else:
        loglevel = options.loglevel

    if not options.logfile:
        logfilename = 'datafeed.log'
    else:
        logfilename = options.logfile

    common.setup_logger(False, logfilename, loglevel) 

       # and so on ...



        #### dbfuncs.py


import logging

    # not sure how to 'bind' to the logger in __main__.py
    logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

    engine = create_engine('postgres://postgres:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b8c8cfdcf8d4d7dbd9d4d0d7cbcc">[email protected]</a>:port/dbname', echo=True)

[Edit2]

Common module sets the logger up correctly, and I can use the logger in my other modules that import common.

However in dbfuncs module, I am getting the following error/warning:

No handlers could be found for logger “sqlalchemy.engine.base.Engine

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In addition to echo parameter of create_engine() there is a more flexible way: configuring logging to echo engine statements:

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

See Configuring Logging section of documentation for more information.

Method 2

You can see the SQL statements being sent to the DB by passing echo=True when the engine instance is created (usually using the create_engine() or engine_from_config() call in your code).

For example:

engine = sqlalchemy.create_engine('postgres://foo/bar', echo=True)

By default, logged statements go to stdout.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0