Python: How to connect to a MySQL db

Question

I am trying to write a script that connects to a server, then connects to a MySQL db (which I currently can do via Navicat - so I know my username and password for the MySQL connection are correct).

Here is what I’ve written so far:

import socket
from ssh2.session import Session
import mysql.connector
host = 'servername.logserverlog.net'
user = 'username'

sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((host, 22))

session = Session()
session.handshake(sock)
session.userauth_publickey_fromfile(user, r'C:\Users\user\Docs\ssh-key')

cnx = mysql.connector.connect(user='username', password='$gHj1aFaVFRfhl*C', database='analyst_db')

The error I am getting reads:

File “C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site- packages\mysql\connector\connection.py”, line 176, in _auth_switch_request raise errors.get_exception(packet) mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user ‘username’@‘localhost’ (using password: YES)

Given that I have already confirmed my user and password are valid, I have also tried editing the password string to a raw string (to see if somehow the Python string wasn’t being received by the MySQL db correctly) and received the same error.

So, I’m not sure why the error keeps coming up.

Your user name have to acess for mysql database, so you have to provide access the username and host, please check this post that will hep https://stackoverflow.com/questions/6445917/connect-failed-access-denied-for-user-rootlocalhost-using-password-yes — utks009, Aug 16 '18 at 06:23
I added both host='localhost' and host='127.0.0.1' parameters, and it returned the same error. When you say "Your user name have to access for mysql database", that is what I meant by I can already connect to the database with those creds using Navicat. Is this what you mean? — samueljames3, Aug 16 '18 at 06:29
Is the mysql db is on your localhost or any other server, if is on other server then specify the host param in your `mysql.connector.connect(host='server.url')`, or try to connect it using cli or other tool if connection is successful or not — utks009, Aug 16 '18 at 06:51
@utks009 (by the way, is your username referencing chapter 20?). At any rate, the connection is taking place on another server. The python connection to the server is being established separately in the "session = Session() session.handshake(sock) session.userauth_publickey_fromfile(user, r'C:\Users\user\Docs\ssh-key'). When I run this by itself, I get a connection without an error. And the error I am getting is from the MySQL connection being denied from the database I am trying to connect to (not from the server). — samueljames3, Aug 16 '18 at 07:10
I believe the "server.url" you are suggesting above is being connected to in the host='servername.logserverlog.net' line in my code above. thanks! — samueljames3, Aug 16 '18 at 07:18

score 1 · Accepted Answer · answered Aug 17 '18 at 23:07

I ended up learning that just because I created an SSH connection, I still needed to set up an SSH Tunnel with port forwarding, so the script knows where to communicate. I made the assumption that the connection itself would tell the script where to look (when in reality the port forwarding tells it where to look and listen).

So I was skipping a step. The final working script uses SSHTunnelForwarder from the sshtunnel library.

import mysql.connector
from datetime import date, datetime, timedelta
from sshtunnel import SSHTunnelForwarder

ssh_host    = 'servername.net'
ssh_port    = 22
ssh_user    = 'serveruser'
ssh_key     = "C:\\Users\\user\\ssh\\public key"
ssh_remote_port = 3306

with SSHTunnelForwarder(
    (ssh_host, ssh_port),
    ssh_username=ssh_user,
    ssh_private_key=ssh_key,
    remote_bind_address=('127.0.0.1', ssh_remote_port)
) as server:
cnx = mysql.connector.connect(user='username', password='password', database='analyst_db, host='localhost', port=server.local_bind_port)
cur = cnx.cursor()

Python: How to connect to a MySQL db

1 Answers1