PyMySQL can't connect to MySQL on localhost

I'm trying to connect to MySQL on localhost using PyMySQL:

import pymysql
conn = pymysql.connect(db='base', user='root', passwd='pwd', host='localhost')

but (both on Python 2.7 and Python 3.2) I get the error:

socket.error: [Errno 111] Connection refused

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (111)")

I'm sure mysqld is running because I can conne开发者_Python百科ct using mysql command or phpMyAdmin. Moreover, I can connect using MySQLdb on Python 2 with nearly the same code:

import MySQLdb
conn = MySQLdb.connect(db='base', user='root', passwd='pwd', host='localhost')

It seems that the problem is on PyMySQL side rather than MySQL but I have no idea how to solve it.

Two guesses:

1. Run mysqladmin variables | grep socket to get where the socket is located, and try setting up a connection like so:

   pymysql.connect(db='base', user='root', passwd='pwd', unix_socket="/tmp/mysql.sock")
   

2. Run mysqladmin variables | grep port and verify that the port is 3306. If not, you can set the port manually like so:

   pymysql.connect(db='base', user='root', passwd='pwd', host='localhost', port=XXXX)
   

Seems like changing localhost to 127.0.0.1 fixes the error, at least in my configuration. If it doesn't, I would look for errors in tcp sockets connection and, of course, post it as a bug in pymysql bugtrack.

I solved the issue by replacing localhost with 127.0.0.1 and changing the password to my MYSQL database password as shown below;

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = 'XXXXXXXXX',
    db = 'mysql'
)

I met the same question and my solution is as follows:

1. Run ssh -fN -L 3307:mysql_host:3306 ssh_user@ssh_host in my terminal.
2. Then input your ssh password
3. conn = pymysql.connect(db='base', user='root', passwd='pwd', host='localhost')

This error occurs because database does not support link directly.

I asked why socket worked but not TCP and the answer was that bind-address in /etc/my.cnf was not set correctly. This could be your problem too since the socket methods works just fine but the TCP one does not.

Those who are strugging to connect localhost MySQL from dockerised flask-sqlalchemy or using pymysql, pls look into this thread, very usefull How to connect locally hosted MySQL database with the docker container

This worked for me:

import pymysql

db = pymysql.connect(host="localhost",port=8889,user="root",passwd="root")
cursor=db.cursor()
cursor.execute("SHOW DATABASES")
results=cursor.fetchall()
for result in results:
    print (result)

if you want to find the port # go to mysql in terminal, and type:

SHOW VARIABLES WHERE Variable_name = 'hostname';
SHOW VARIABLES WHERE Variable_name = 'port';

I had this same problem on AWS - and turns out that my security group was blocking the connection. I temporarily opened up all connections and voila! It connected!

Do you have any type of FW or host-based FW that could be blocking the connection? I thought it was my code and all was fine. Also check the port you are connecting on.

If you are using Docker, you might need to use host.docker.internal instead of localhost.

I managed to solve my issue by using the port without any quotation like so:

port = 3306,

You need to add the port to the connection as well. Try this and it works fine.

pymysql(Module Name).connect(host="localhost", user="root", passwd="root", port=8889, db="db_name")