MySQLdb 最佳实践
我目前正在开发一个 Python 脚本，它可以使用从 MySQL 数据库中提取的一些数据来执行一些操作。为了访问这些数据，我使用模块 MySQLdb。
该模块遵循PEP 249（Python DB API）中规定的准则，并涉及创建一个连接对象和一个后续的游标对象，该对象用于迭代信息。
目前，在我的项目中，每当我需要执行 MySQL 读/写块时，我都会创建一个连接对象，然后在完成后关闭它。但是，我可以轻松地传递连接对象，以避免这些重复的打开/关闭。
我的问题是：考虑到安全性、资源管理等，是否开放；读/写；关闭;对下一次读/写重复； 方法比 open; 更好读/写；为下一个读/写传递连接； 方法？
编辑：更多背景信息。这个特定的 Python 脚本是高度多线程的。复杂的进程/线程环境是否会影响哪种方法更合适？
I'm currently developing a Python script that does a few things with some data pulled from a MySQL database. To access this data, I'm using the module MySQLdb.
This module follows the guidelines laid out in PEP 249, the Python DB API, and involves creating a connection object, and a subsequent cursor object, which is used to iterate through the information.
Currently, In my project, I create a connection object any time I need to do a block of MySQL reading/writing, then close it when I'm done. However, I could easily pass the connection object around in order to avoid these repeated open/closes.
My question is: Taking into account security, resource management, etc., is the open; read/write; close; repeat for the next read/write; methodology better than the open; read/write; pass connection on for the next read/write; approach?
EDIT: Some more context. This specific Python script is heavily multithreaded. Does a complex process/thread environment affect which method is more apropos?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用数据库连接的多线程应用程序可能应该使用某种连接池。在此方案中，您有预定数量的连接，所有连接都在某种队列中进行管理。当线程需要运行事务时，它会从池中获取连接。如果池当前空闲，则将为该线程创建一个新连接；如果非常忙，它会让线程等待，直到另一个线程释放连接。当线程完成连接时，它将连接返回到池，池将其移交给另一个线程，或者如果没有线程等待连接，则将其关闭。
这个逻辑可能有几十种实现，但我强烈推荐 SQLAlchemy，它不仅仅是连接管理（尽管您不必使用它做更多的事情）
a multithreaded application that uses database connections should probably use some sort of connection pool. in this scheme, you have a predifined number of connections, all managed in some sort of queue. When a thread needs to run a transaction, it gets a connection from the pool. If the pool is currently idle, then a new connection will be created for the thread; If it's very busy, it will make the thread wait until another thread frees a connection. when the thread is done with a connection, it returns the connection to the pool, which hands it off to another thread, or if there are no threads waiting for a connection, it closes it.
There are probably dozens of implementations of this logic, but I can highly recommend SQLAlchemy, and for more than just it's connection management (although you don't have to use it for much more than that)
我的想法是，最好只在需要时才保持连接对象打开。有时不断实例化新连接可能很麻烦，但一般来说这是更好的做法。
the way I think about it, it's best to only keep a connection object open while you need it. It can be cumbersome at times to keep instantiating new connections, but generally speaking that is the better practice.
作为最佳实践之一，我建议使用 OurSQL 而不是 MySQLdb。
如果我是你，我会在程序执行期间始终保持连接打开，仅在程序结束时关闭它。不好的一点是你的连接池中少了一个连接，好的一点是它可以为你节省大量的样板文件。
As one of the best practices I would recommend using OurSQL over MySQLdb.
If I were you I'd keep a connection open at all times during the execution of your program only closing it when the program ends. The bad part about it is that your connection pool has one less connection in it, the good part is it saves you a lot of boilerplate.