pandas 有没有办法测试 SQLAlchemy 连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32929318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to test an SQLAlchemy Connection?
提问by mgig
I'm using SQLAlchemy to connect to write a pandas DataFrame to a MySQL database. Early on in my code I create an SQLAlchemy engine:
我正在使用 SQLAlchemy 连接将 Pandas DataFrame 写入 MySQL 数据库。在我的代码早期，我创建了一个 SQLAlchemy 引擎：
engine = create_my_sqlalchemy_connection()
I execute some queries, do some calculations, and then try to use that same engine to write to the database a little later:
我执行一些查询，进行一些计算，然后稍后尝试使用相同的引擎写入数据库：
df.to_sql('my_table', engine, if_exists='append', index=False)
Sometimes this works, and sometimes the connection is lost by the time the code is ready to write to the DB, and there is an error.
有时这有效，有时在代码准备写入数据库时连接丢失，并且出现错误。
I could do a try, except and create a new connection if needed:
我可以尝试一下，如果需要，除了创建一个新连接：
try:
df.to_sql('my_table', engine, if_exists='append', index=False)
except:
engine = create_my_sqlalchemy_connection()
df.to_sql('my_table', engine, if_exists='append', index=False)
However, I thought I'd reach out and see if anyone knows of a better way (e.g. if there is some SQLAlchemy method that I am unaware of for testing to see if the connection still exists).
但是，我想我会伸出手，看看是否有人知道更好的方法（例如，是否有一些我不知道的 SQLAlchemy 方法用于测试以查看连接是否仍然存在）。
回答by user787267
You can have SQLAlchemy check for the liveness of the connection with the parameter pool_pre_ping: https://docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine.params.pool_pre_ping
您可以使用参数让 SQLAlchemy 检查连接的活性pool_pre_ping：https: //docs.sqlalchemy.org/en/13/core/engines.html#sqlalchemy.create_engine.params.pool_pre_ping
if True will enable the connection pool “pre-ping” feature that tests connections for liveness upon each checkout.
if True 将启用连接池“pre-ping”功能，该功能在每次结帐时测试连接的活跃度。
Simply enable it by using when you create your engine.
只需在创建引擎时使用即可启用它。
回答by Dan
It's worth trying the Connection.closedproperty.
值得尝试Connection.closed属性。
if engine.closed:
engine = create_my_sqlalchemy_connection()
df.to_sql('my_table', engine, if_exists='append', index=False)
else:
df.to_sql('my_table', engine, if_exists='append', index=False)
回答by grofte
If you have problems with time outs when writing a Pandas Dataframe to a SQL server your Dataframe is probably quite large or there are many constraints that the database has to check when you insert.
To get around this you need to set the chunksize argument in the Pandas command:DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
如果您在将 Pandas Dataframe 写入 SQL 服务器时遇到超时问题，您的 Dataframe 可能非常大，或者在您插入时数据库必须检查许多约束。
要解决这个问题，您需要在 Pandas 命令中设置 chunksize 参数：DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
chunksize: int, optional
Rows will be written in batches of this size at a time. By default, all rows will be written at once.
chunksize: int, optional
Rows 将一次按此大小批量写入。默认情况下，将一次写入所有行。
I don't know how many rows you have but 10000 is probably a fine value. The problem with this is that if your write fails now you will have inserted some rows but not all =( and you won't know which.
我不知道你有多少行，但 10000 可能是一个很好的值。这样做的问题是，如果您现在写入失败，您将插入一些行，但不是所有 =(，您将不知道是哪一行。
回答by knowone
This might be useful to you since it seems like your connection is timing out. This is for anyone like me who found it useful.
这可能对您有用，因为您的连接似乎超时。这适用于像我这样发现它有用的人。
From the SQLAlchemy documentation when working with MySQL:
从使用 MySQL 时的 SQLAlchemy 文档：
MySQL features an automatic connection close behavior, for connections that have been idle for a fixed period of time, defaulting to eight hours. To circumvent having this issue, use the create_engine.pool_recycle option which ensures that a connection will be discarded and replaced with a new one if it has been present in the pool for a fixed number of seconds:
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
MySQL 具有自动连接关闭行为，用于固定时间段空闲的连接，默认为 8 小时。要避免出现此问题，请使用 create_engine.pool_recycle 选项，以确保如果连接在池中存在固定的秒数，则连接将被丢弃并替换为新连接：
engine = create_engine('mysql+mysqldb://...', pool_recycle=3600)
