最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】pymysql中connect异常时有哪些错误类型

Python crifan 6871浏览 0评论

折腾:

【已解决】Python中如何操作mysql

期间,参考别人代码:

<code>def connect(self):
    try:
        self.connection = pymysql.connect(**self.config, cursorclass=pymysql.cursors.DictCursor)
        print("self.connection=%s", self.connection)
        return True
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("The credentials you provided are not correct.")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("The database you provided does not exist.")
        else:
            print("Something went wrong: ", err)
        return False
</code>

现在需要:

写对于pymysql.connect,有哪些error,然后去写处理代码,提示用户

pymysql connect error type

python – How to get the MySQL type of error with PyMySQL? – Stack Overflow

pymysql.Error Python Example

“      except pymysql.ProgrammingError:

            raise nose.SkipTest(

                “Create a group of connection parameters under the heading “

                “[pandas] in your system’s mysql default file, “

                “typically located at ~/.my.cnf or /etc/.my.cnf. “)

        except pymysql.Error:

        try:

            self.__conn = pymysql.connect( host = host, port = port, user = user, password = password, charset = charset )

        except pymysql.Error as e:

            print( ‘MySQL?????%d?%s’ % (e.args[ 0 ], e.args[ 1 ]) )

ProgrammingError

DataError

IntegrityError

NotSupportedError

OperationalError

好像是:

PEP 249 — Python Database API Specification v2.0 | Python.org

中的:

https://www.python.org/dev/peps/pep-0249/#exceptions

StandardError

|__Warning

|__Error

   |__InterfaceError

   |__DatabaseError

      |__DataError

      |__OperationalError

      |__IntegrityError

      |__InternalError

      |__ProgrammingError

      |__NotSupportedError

所以就是:

pymsql中的connect后,得到的是Connection,其遵循PEP 0249:

其中有解释有哪些可能的error

PyCharm中动态代码提示中也可以看到:

所以此处为了简单化,只处理普通的Error即可

然后此处故意没有开mysql的server去测试看看会有什么error

<code>➜  ~ /usr/local/mysql/support-files/mysql.server status
 SUCCESS! MySQL running (61419)
➜  ~ /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL
... SUCCESS!
➜  ~ /usr/local/mysql/support-files/mysql.server status
 ERROR! MySQL is running but PID file could not be found
➜  ~ /usr/local/mysql/support-files/mysql.server status
 ERROR! MySQL is not running
</code>

停了mysql server后,去connect,果然是出Connection refused的错:

<code>Connect mysql with config= {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'crifan_mysql', 'database': 'AutohomeResultdb', 'charset': 'utf8'}  error= (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 61] Connection refused)")
</code>

然后:

<code>➜  ~ /usr/local/mysql/support-files/mysql.server start
Starting MySQL
. SUCCESS!
➜  ~ /usr/local/mysql/support-files/mysql.server status
 SUCCESS! MySQL running (93946)
</code>

故意让密码错误试试

<code>Connect mysql with config= {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'xxx', 'database': 'AutohomeResultdb', 'charset': 'utf8'}  error= (1045, "Access denied for user 'root'@'localhost' (using password: YES)")
</code>

然后故意数据库的table搞错了试试

<code>Connect mysql with config= {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'crifan_mysql', 'database': 'xxxx', 'charset': 'utf8'}  error= (1049, "Unknown database 'xxxx'")
</code>

【总结】

至此,算是基本上明确了pymysql中connect时,如果出现异常,也是Python的PEP 249中的异常,对应类型有:

<code>StandardError
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
</code>

而此处只是简单处理的话,直接只针对pymysql.Error去处理即可:

<code>
import pymysql
import pymysql.cursors


class MysqlDb:
    config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': 'crifan_mysql',
        'database': 'AutohomeResultdb',
        'charset': "utf8"
    }

    connection = None
    isConnected = False

    def __init__(self):
        """init mysql"""
        if not self.isConnected:
            self.isConnected = self.connect()
            print("Connect mysql return", self.isConnected)

    def connect(self):
        try:
            self.connection = pymysql.connect(**self.config, cursorclass=pymysql.cursors.DictCursor)
            print("connect mysql ok, self.connection=", self.connection)
            return True
        except pymysql.Error as err:
            print("Connect mysql with config=", self.config, " error=", err)
            return False

def test():
    """test mysql"""
    mysqlObj = MysqlDb()
    print("mysqlObj=", mysqlObj)

if __name__ == '__main__':
    test()
</code>

然后:

  • 当mysql服务器停止,错误是:2003, “Can’t connect to MySQL server on ‘127.0.0.1’ ([Errno 61] Connection refused)”

  • 当密码出错,错误是:1045, “Access denied for user ‘root’@’localhost’ (using password: YES)”

  • 当数据库名字搞错,出错是:1049, “Unknown database ‘xxxx'”

就基本上足够普通用户清楚到底是哪方面的错误,基本够用了。

转载请注明:在路上 » 【已解决】pymysql中connect异常时有哪些错误类型

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
89 queries in 0.171 seconds, using 22.15MB memory