morpheous morpheous - 1 year ago 65
Python Question

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 ff 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 as the db


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

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

help="log to FILE. if not set, no logging will be done"

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
loglevel = options.loglevel

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

common.setup_logger(False, logfilename, loglevel)

# and so on ...


import logging

# not sure how to 'bind' to the logger in

engine = create_engine('postgres://postgres:pwd@localhost:port/dbname', echo=True)


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

Answer Source

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

import logging

See Configuring Logging section of documentation for more information.