Can't connect to mysql db withh python - bad handshake

Question

I am trying to connect to a mysql database (hosted on media temple) with my python script (ran locally) but I am receiving an error when I run it.

The error is:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 179, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Bad handshake

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/Charlie/Documents/python/myscript/mysql_insert.py", line 8, in <module>
    port="3306"
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/__init__.py", line 172, in connect
    return CMySQLConnection(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 78, in __init__
    self.connect(**kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/abstracts.py", line 735, in connect
    self._open_connection()
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/mysql/connector/connection_cext.py", line 182, in _open_connection
    sqlstate=exc.sqlstate)
mysql.connector.errors.OperationalError: 1043 (08S01): Bad handshake

Here is the code from the script

import mysql.connector

mydb = mysql.connector.connect(
  host="external-db.s157688.gridserver.com",
  user="myusername",
  passwd="mypassword",
  database="mydatabase",
  port="3306"
)

mycursor = mydb.cursor()

sql = "INSERT INTO test (post_id, title) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

I tried to google it but did not find any solutions, could anybody help out?

Answer 1

make sure you've installed mysql-connector and not mysql-connector-python, to make this sure just run the following commands: pip3 uninstall mysql-connector-python pip3 install mysql-connector