python-3.x - PyMySQL 代码 Windows 10 Python 3.5.0 中的连接错误？

Question

我正在尝试在 Windows 10 下将 PyMySQL 0.79 与 python 3.5.0 一起使用，但在运行一个名为“test.py”的简单程序时出现连接错误...

import pymysql
db = pymysql.connect( host = 'sqlserver.example.com', passwd 'SECRET12345', user = 'dbuser', db='myDatabase')
cursor = db.cursor()
sql = "INSERT INTO people (name, email, age) VALUES (%s, %s, %s)"
cursor.execute( sql, ( "Jay", "jay@example.com", '77') )
cursor.close()
db.commit()   #Makes sure the DB saves your changes!
db.close()

但是上面的代码给了我错误信息:

C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\Lib\site-packages\pymysql>test.py
Traceback (most recent call last):
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql\connections.py", line 890, in connect
    (self.host, self.port), self.connect_timeout)
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\socket.py", line 689, in create_connection
    for res in getaddrinfo(host, port, 0, SOCK_STREAM):
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\socket.py", line 728, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno 11001] getaddrinfo failed

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\Lib\site-packages\pymysql\test.py", line 2, in <module>
    db = pymysql.connect( host = 'sqlserver.example.com', passwd = 'SECRET12345', user = 'dbuser', db='myDatabase')
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql\connections.py", line 688, in __init__
    self.connect()
  File "C:\Users\Avtar\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pymysql\connections.py", line 937, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'sqlserver.example.com' ([Errno 11001] getaddrinfo failed)")

我使用命令“pip install pymysql”安装了 PyMySQL 并检查我输入的安装:

'pip list' 给我:

pip (9.0.1)
PyMySQL (0.7.9)
setuptools (18.2)
yolk (0.4.3)

'pip show pymysql' 给我:

Name: PyMySQL
Version: 0.7.9
Summary: Pure Python MySQL Driver
Home-page: https://github.com/PyMySQL/PyMySQL/
Author: INADA Naoki
Author-email: songofacandy@gmail.com
License: MIT
Location: c:\users\avtar\appdata\local\programs\python\python35-32\lib\site-packages
Requires:

我无法从中判断出我做错了什么，所以如果有人能帮我解决这个问题，我将不胜感激。提前致谢。

Answer 1

首先，主机类型:“sqlserver.example.com”，通常是 Microsoft SQL Server，而不是 MySQL。如果是这种情况(即您正在使用 SQL Server)，那么您应该使用更通用的库，而不是使用 pymysql 库；例如pyodbc .

基于另一个stackoverflow question ，对于Python2.7，该库的使用方法如下:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=sqlserver.example.com;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()

但是，如果您实际上使用的是 MySQL，那么您的配置不正确...

您的代码，请注意它在 passwd 和您的密码 之间没有“=”；它也没有端口号。

db = pymysql.connect( host = 'sqlserver.example.com', passwd 'SECRET12345', user = 'dbuser', db='myDatabase')

因此我建议:

db = pymysql.connect( host = 'sqlserver.example.com', port=3306, passwd='SECRET12345', user = 'dbuser', db='myDatabase')

如果您的端口不是 3306，并且您不确定您的mysql在哪个端口，那么要查找端口，请执行以下操作:

mysql> select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'PORT'; 
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| PORT          | 2127           |
+---------------+----------------+
1 row in set (0.00 sec)