如何启用MySQL客户端自动重新连接MySQLdb?

iyr7buue  于 2023-03-17  发布在  Mysql
关注(0)|答案(9)|浏览(129)

我遇到了PHP的方式做的把戏:

my_bool reconnect = 1;
mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

但MySQLdb(python-mysql)却没有成功。
有谁能给予点提示吗?谢谢。

uurv41yg

uurv41yg1#

我通过创建一个封装cursor.execute()方法的函数来解决这个问题,因为cursor.execute()方法抛出了MySQLdb.OperationalError异常,上面的另一个例子暗示了抛出这个异常的是conn.cursor()方法。

import MySQLdb

class DB:
  conn = None

  def connect(self):
    self.conn = MySQLdb.connect()

  def query(self, sql):
    try:
      cursor = self.conn.cursor()
      cursor.execute(sql)
    except (AttributeError, MySQLdb.OperationalError):
      self.connect()
      cursor = self.conn.cursor()
      cursor.execute(sql)
    return cursor

db = DB()
sql = "SELECT * FROM foo"
cur = db.query(sql)
# wait a long time for the Mysql connection to timeout
cur = db.query(sql)
# still works
oug3syen

oug3syen2#

我对提出的解决方案有疑问,因为它没有捕获异常。我不知道为什么。
我已经用我认为更简洁的ping(True)语句解决了这个问题:

import MySQLdb
con=MySQLdb.Connect()
con.ping(True)
cur=con.cursor()

从这里开始:http://www.neotitans.com/resources/python/mysql-python-connection-error-2006.html

vi4fp9gy

vi4fp9gy3#

如果你使用的是ubuntuLinux,python-mysql包中添加了一个补丁,可以设置相同的MYSQL_OPT_RECONNECT选项(参见here),但我还没有尝试过。
不幸的是,由于与autoconnect和transations冲突(如here所述),该补丁后来被删除。
该页面的评论说:1.2.2-7在2008年6月19日发布的Intrepid中发布
Python-mysqldb(1.2.2-7)不稳定;紧急程度=低
[ Sandro Tosi ] * debian/control -在描述中列出项目行以2个空格开始,以避免在网页上重新格式化(关闭:编号480341)
[贝恩德·蔡梅茨] * debian/patches/02_reconnect.dpatch:- 脱落贴片:Storm中的注解解释了该问题:

# Here is another sad story about bad transactional behavior. MySQL
    # offers a feature to automatically reconnect dropped connections.
    # What sounds like a dream, is actually a nightmare for anyone who
    # is dealing with transactions. When a reconnection happens, the
    # currently running transaction is transparently rolled back, and
    # everything that was being done is lost, without notice. Not only
    # that, but the connection may be put back in AUTOCOMMIT mode, even
    # when that's not the default MySQLdb behavior. The MySQL developers
    # quickly understood that this is a terrible idea, and removed the
    # behavior in MySQL 5.0.3. Unfortunately, Debian and Ubuntu still
    # have a patch right now which *reenables* that behavior by default
    # even past version 5.0.3.
qlvxas9a

qlvxas9a4#

我需要一个类似Garret的解决方案,但是针对cursor.execute(),因为我想让MySQLdb为我处理所有的转义任务。 Package 器模块最后看起来像这样(用法如下):

#!/usr/bin/env python

import MySQLdb

class DisconnectSafeCursor(object):
    db = None
    cursor = None

    def __init__(self, db, cursor):
        self.db = db
        self.cursor = cursor

    def close(self):
        self.cursor.close()

    def execute(self, *args, **kwargs):
        try:
            return self.cursor.execute(*args, **kwargs)
        except MySQLdb.OperationalError:
            self.db.reconnect()
            self.cursor = self.db.cursor()
            return self.cursor.execute(*args, **kwargs)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

class DisconnectSafeConnection(object):
    connect_args = None
    connect_kwargs = None
    conn = None

    def __init__(self, *args, **kwargs):
        self.connect_args = args
        self.connect_kwargs = kwargs
        self.reconnect()

    def reconnect(self):
        self.conn = MySQLdb.connect(*self.connect_args, **self.connect_kwargs)

    def cursor(self, *args, **kwargs):
        cur = self.conn.cursor(*args, **kwargs)
        return DisconnectSafeCursor(self, cur)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

disconnectSafeConnect = DisconnectSafeConnection

使用它很简单,只有初始连接不同。根据MySQLdb的需要用 Package 器方法扩展类。

import mydb

db = mydb.disconnectSafeConnect()
# ... use as a regular MySQLdb.connections.Connection object

cursor = db.cursor()

# no more "2006: MySQL server has gone away" exceptions now
cursor.execute("SELECT * FROM foo WHERE bar=%s", ("baz",))
tzdcorbm

tzdcorbm5#

你可以把连接的提交和关闭分开......这并不可爱,但它确实做到了。

class SqlManager(object):
 """
 Class that handle the database operation
 """
 def __init__(self,server, database, username, pswd):

      self.server = server
      self.dataBase = database
      self.userID = username
      self.password = pswd

def Close_Transation(self):
      """
      Commit the SQL Query
      """
      try:
        self.conn.commit()
      except Sql.Error, e:
        print "-- reading SQL Error %d: %s" % (e.args[0], e.args[1])

 def Close_db(self):
    try:
        self.conn.close()
    except Sql.Error, e:
        print "-- reading SQL Error %d: %s" % (e.args[0], e.args[1])

 def __del__(self):
    print "close connection with database.."
    self.conn.close()
thtygnil

thtygnil6#

我在MySQL和Python上也遇到过类似的问题,对我有效的解决方案是将MySQL升级到5. 0. 27(在Fedora Core 6上;您的系统可以在不同版本下正常工作)。
我尝试了很多其他的事情,包括修补Python库,但是升级数据库要容易得多,而且(我认为)是一个更好的决定。

f3temu5u

f3temu5u7#

除了Liviu Chircu解决方案......将以下方法添加到DisconnectSafeCursor:

def __getattr__(self, name):
    return getattr(self.cursor, name)

并且像“lastrowid”这样的原始光标属性将继续工作。

xzabzqsa

xzabzqsa8#

我的方法基于Liviu Chircu解决方案。
我已经添加了对太多重试的控制。如果查询与表结构中的字段不匹配,Liviu的原始答案很容易得到“太多连接”*OperationalError

MySQLdb.OperationalError: (1054, "Unknown column 'xxxxxxx' in 'field list'")

这个错误不是连接错误。在我的版本中,我也删除了类变量,因为我认为它们是不必要的。

import MySQLdb

class MySQLdbReconnectableCursor(object):
    def __init__(self, db, cursor):
        self.db = db
        self.cursor = cursor

    def __getattr__(self, name):
        return getattr(self.cursor, name)

    def __iter__(self, *args, **kwargs):
        return self.cursor.__iter__(*args, **kwargs)

    def __next__(self, *args, **kwargs):
        return self.cursor.__next__(*args, **kwargs)

    def close(self):
        self.cursor.close()

    def execute(self, *args, **kwargs):
        try:
            result = self.cursor.execute(*args, **kwargs)
            self.db.refresh_retries()
            return result
        except MySQLdb.OperationalError:
            self.db.reconnect(reraise=True)
            self.cursor = self.db.cursor()
            return self.cursor.execute(*args, **kwargs)

    def fetchone(self):
        return self.cursor.fetchone()

    def fetchall(self):
        return self.cursor.fetchall()

class MySQLdbReconnectable(object):
    def __init__(self, *args, **kwargs):
        self.conn = None
        self.retries = kwargs.get("max_retries", 3)
        self.connect_args = args
        self.connect_kwargs = kwargs
        self.refresh_retries()
        self.reconnect()

    def refresh_retries(self):
        self.__retries = self.retries

    def reconnect(self, reraise=False):
        if self.__retries:
            self.__retries -= 1
            self.conn = MySQLdb.connect(
                *self.connect_args, **self.connect_kwargs
            )
        else:
            if reraise:
                raise
            else:
                raise IOError("Can not retry anymore!")

    def cursor(self, *args, **kwargs):
        cur = self.conn.cursor(*args, **kwargs)
        return MySQLdbReconnectableCursor(self, cur)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()
d7v8vwbk

d7v8vwbk9#

你也可以用代码来解决掉线的问题。
其中一种方法如下:

import MySQLdb

class DB:
    conn = None

    def connect(self):
        self.conn = MySQLdb.connect()

    def cursor(self):
        try:
            return self.conn.cursor()
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            return self.conn.cursor()

db = DB()
cur = db.cursor()
# wait a long time for the Mysql connection to timeout
cur = db.cursor()
# still works

相关问题