title: postgres - cannot drop database using psycopg2
body: <p>So I simply trying to drop and recreate my database using Python's psycopg2.  Here is my code:</p>
  
  <pre><code>    with psycopg2.connect(database="postgres", user="postgres", password="****") as conn:
      with conn.cursor() as cur:
          conn.autocommit = True   #  Explains why we do this - we cannot drop or create from within a DB transaction. http://initd.org/psycopg/docs/connection.html#connection.autocommit
          cur.execute("DROP DATABASE crowdsurfer;")
          cur.execute("CREATE DATABASE crowdsurfer;")
  </code></pre>
  
  <p>When I run this code, I get </p>
  
  <pre><code>PS C:\Users\Nick\Documents\GitHub\CrowdSurfer\CrowdSurfer&gt; python utils/sqlInit.py
  Traceback (most recent call last):
  
    File "utils/sqlInit.py", line 70, in &lt;module&gt;
      run()
  
    File "utils/sqlInit.py", line 21, in run
  recreate_empty_database()
  
    File "utils/sqlInit.py", line 40, in recreate_empty_database
      cur.execute("DROP DATABASE crowdsurfer;")
  
  psycopg2.OperationalError: database "crowdsurfer" is being accessed by other users
  DETAIL:  There is 1 other session using the database.
  </code></pre>
  
  <p>Alright, fair enough.  So I opened a connection to postgres and took a peek at the existing processed before and while my code was running.  Before my code started, we get this:</p>
  
  <pre><code>postgres=# select pid from pg_stat_activity
  </code></pre>
  
  <p>This command returns a single PID, PID 6052</p>
  
  <p>This process is me, so that's good.  Now here is what I get when query running processes while my python code is running:</p>
  
  <pre><code> postgres=# select * from pg_stat_activity;
   datid  |   datname   | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |       backend_start        |         xact_start         |        query_start         |
         state_change        | waiting | state  |                         query
  
    12029 | postgres    | 6052 |       10 | postgres | psql             | ::1         |                 |       49842 | 2014-03-11 23:14:34.049-06 | 2014-03-11 23:14:58.938-06 | 2014-03-11 23:14:58.938-06 |
  
  2014-03-11 23:14:58.938-06 | f       | active | select * from pg_stat_activity;
   142547 | crowdsurfer | 3952 |       10 | postgres |                  | 127.0.0.1   |                 |       49849 | 2014-03-11 23:14:57.489-06 |                            | 2014-03-11 23:14:57.491-06 |
  2014-03-11 23:14:57.491-06 | f       | idle   | SET default_transaction_isolation TO 'read committed'
  
    12029 | postgres    | 7908 |       10 | postgres |                  | ::1         |                 |       49851 | 2014-03-11 23:14:57.556-06 | 2014-03-11 23:14:57.559-06 | 2014-03-11 23:14:57.559-06 |
  2014-03-11 23:14:57.559-06 | f       | active | DROP DATABASE crowdsurfer;
  (3 rows)
  </code></pre>
  
  <p>The python code started 2 processes!  One connects to the postgres DB, which I did explicitly. The other connects to the DB I want to delete (crowdsurfer). Note that it is idle, and the query it ran was SET default_transaction_isolation TO 'read committed'  </p>
  
  <p>So it seems like setting conn.autocommit equal to true is creating a new process??? Any thoughts on what to do here to make drop this DB?</p>