pandas删除数据库 python_python – 与Pandas中的SQLAlchemy清理数据库连接

背景：

当使用sqlalchemy和pandas read_sql_query(query,con)方法时,它将创建一个具有可连接到self.connectable.execute(query)的属性的SQLDatabase对象.并且SQLDatabase.connectable初始化为con,只要it is an instance of sqlalchemy.engine.Connectable(即Engine and Connection).

案例I：将Engine对象作为con传递时

就像你问题中的示例代码一样：

from sqlalchemy import create_engine

import pandas as pd

engine = create_engine('...')

df = pd.read_sql_query(query, con=engine)

在内部,pandas只使用result = engine.execute(query),which means：

Where above, the execute() method acquires a new Connection on its own, executes the statement with that object, and returns the ResultProxy. In this case, the ResultProxy contains a special flag known as close_with_result, which indicates that when its underlying DBAPI cursor is closed, the Connection object itself is also closed, which again returns the DBAPI connection to the connection pool, releasing transactional resources.

在这种情况下,您不必担心Connection本身会自动关闭,但它会保留引擎的连接池.

因此,您可以使用以下命令禁用池：

engine = create_engine('...', poolclass=NullPool)

或者使用engine.dispose()完全处理发动机.

但是在Engine Disposal doc (the last paragraph)之后,这两个是另类,你不必同时使用它们.所以在这种情况下,对于read_sql_query和清理的简单一次性使用,我认为这应该足够了：

# Clean up entirely after every query.

engine = create_engine('...')

df = pd.read_sql_query(query, con=engine)

engine.dispose()

案例二：将Connection对象作为con传递：

connection = engine.connect()

print(connection.closed) # False

df = pd.read_sql_query(query, con=connection)

print(connection.closed) # False again

# do_something_else(connection)

connection.close()

print(connection.closed) # True

engine.dispose()

You should do this whenever you want greater control over attributes of the connection, when it gets closed, etc. For example, a very import example of this is a Transaction, which lets you decide when to commit your changes to the database. (07005)

但是对于pandas,我们在read_sql_query中没有控制权,连接的唯一用处是它允许你在明确关闭它之前做更多有用的事情.

所以一般来说：

我想我想使用以下模式,这使我能够更好地控制连接并留下未来的可扩展性：

engine = create_engine('...')

# Context manager makes sure the `Connection` is closed safely and implicitly

with engine.connect() as conn:

df = pd.read_sql_query(query, conn)

print(conn.in_transaction()) # False

# do_something_with(conn)

trans = conn.begin()

print(conn.in_transaction()) # True

# do_whatever_with(trans)

print(conn.closed) # False

print('Is Connection with-OUT closed?', conn.closed) # True

engine.dispose()

但是对于简单的用例,例如您的示例代码,我认为清理DB IO资源的方法同样简洁且简单.