Python Database Pooling with psycopg2

Question

Below is a database pooling example. I don't understand the following.

1. Why the getcursor function use "yield"?

2. What is the context manager?

   from psycopg2.pool import SimpleConnectionPool
   from contextlib import contextmanager
   
   dbConnection = "dbname='dbname' user='postgres' host='localhost' password='postgres'"
   
   # pool define with 10 live connections
   connectionpool = SimpleConnectionPool(1,10,dsn=dbConnection)
   
   @contextmanager
   def getcursor():
       con = connectionpool.getconn()
       try:
           yield con.cursor()
       finally:
           connectionpool.putconn(con)
   
   def main_work():
       try:
           # with here will take care of put connection when its done
           with getcursor() as cur:
               cur.execute("select * from \"TableName\"")
               result_set = cur.fetchall()
   
       except Exception as e:
           print "error in executing with exception: ", e**
   

Answer 1

Both of your questions are related. In python context managers are what we're using whenever you see a with statement. Classically, they're written like this.

class getcursor(object):
    def __enter__(self):
        con = connectionpool.getconn()
        return con

    def __exit__(self, *args):
        connectionpool.putconn(con)

Now when you use a context manager, it calls the __enter__ method on the with statement and the __exit__ method when it exits the context. Think of it like this.

cursor = getcursor()
with cursor as cur: # calls cursor.__enter__()
   cur.execute("select * from \"TableName\"")
   result_set = cur.fetchall()
# We're now exiting the context so it calls `cursor.__exit__()` 
# with some exception info if relevant
x = 1

The @contextmanager decorator is some sugar to make creating a context manager easier. Basically, it uses the yield statement to give the execution back to the caller. Everything up to and including the yield statement is the __enter__ method and everything after that is effectively the __exit__ statement.