Connect error in PyMySQL code Windows 10 Python 3.5.0?

Question

I'm trying to use PyMySQL 0.79 with python 3.5.0 under Windows 10 but get a connect error when running a simple program I called '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", "[email protected]", '77') )
cursor.close()
db.commit()   #Makes sure the DB saves your changes!
db.close()

But the above code gives me the error message:

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)")

I installed PyMySQL using the command 'pip install pymysql' and to check the installation I typed:

'pip list' which gives me:

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

'pip show pymysql' which gives me:

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

I cannot tell from this what I am doing wrong, so would really appreciate if anyone can help me sort this. Thanks in advance.

Answer 1

First and foremost, hosts of type: "sqlserver.example.com", is usually Microsoft SQL Server , and not MySQL. If that's the case (ie you are using SQL Server), then instead of using the pymysql library, you should use a more generic library; such as pyodbc.

Based on a another stackoverflow question, for Python2.7, the way to use that library is as follows:

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

However, if infact you are using MySQL, then your configuration is incorrect...

Your code, notice it does not have "=" between passwd and your password; nor does it have the port number.

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

As such I would suggest:

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

If your port is not 3306, and you are not sure which port your mysql is in, then to find the port, do the following:

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)