postgres - 不能使用psycopg2删除数据库 [英] postgres - cannot drop database using psycopg2

问题描述

所以我只是想使用Python的psycopg2删除并重新创建我的数据库。这里是我的代码：

So I simply trying to drop and recreate my database using Python's psycopg2. Here is my 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;")

当我运行此代码时，我会得到

When I run this code, I get

PS C:\Users\Nick\Documents\GitHub\CrowdSurfer\CrowdSurfer> python utils/sqlInit.py
Traceback (most recent call last):

  File "utils/sqlInit.py", line 70, in <module>
    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.

好吧，公平。所以我打开了一个连接到postgres，并在我的代码运行之前，看一看现有的处理。在我的代码开始之前，我们得到：

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:

postgres=# select pid from pg_stat_activity

此命令返回一个PID，PID 6052

This command returns a single PID, PID 6052

这个过程是我，好。现在这里是我得到当查询运行进程时我的python代码运行：

This process is me, so that's good. Now here is what I get when query running processes while my python code is running:

 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)

python代码开始2个进程！一个连接到postgres DB，我明确地。其他连接到我要删除的数据库（crowdsurfer）。注意它是空闲的，它运行的查询是SET default_transaction_isolation TO'read committed'

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'

所以看起来设置conn.autocommit等于true是创建一个新进程???关于在这里做什么做任何想法，删除这个DB？

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?

推荐答案

其中一个导入类有一个装饰器打开连接。这是一个标准的Django装饰器事务。原子（我实际上错误地应用到类，而不是一个方法）。显然它是在导入过程中执行，打开一个到postgres数据库的连接。

Here is what happened. One of the imported classes had a decorator that was opening the connection. This is a standard Django decorator transaction.atomic (I actually incorrectly applied it to a class as opposed to a method). Apparently it is executed during the import process, opening a connection to the postgres DB.

这篇关于postgres - 不能使用psycopg2删除数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！