Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:pwd@localhost: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

like image 639
morpheous Avatar asked Jun 01 '10 14:06

morpheous


People also ask

Does SQLAlchemy use SQL?

SQLAlchemy is a SQL tool built with Python that provides developers with an abundance of powerful features for designing and managing high-performance databases. We'll briefly explore how to use SQLAlchemy and then dive deeper into how to execute raw SQL statements from within the comfort of the Python domain language.

How do I run SQL query from SQLAlchemy in Python?

Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.

What does SQLAlchemy query return?

It returns an instance based on the given primary key identifier providing direct access to the identity map of the owning Session.


2 Answers

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.

like image 95
Denis Otkidach Avatar answered Sep 28 '22 03:09

Denis Otkidach


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.

like image 28
Menno Smits Avatar answered Sep 28 '22 05:09

Menno Smits