Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 connect 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.
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")
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)
                Sort of weird that it would be needed, though. I wonder if your my.cnf file isn't configured correctly.  I'm guessing the port isn't configured right, and MySQLdb is smart enough to try to use the socket instead of TCP.  (Some discussion here where they talk about --protocol)
– Lucas Wiman
                Jan 16, 2013 at 6:39
                I haven't actually got a my.cnf file - but PHP seems to be able to figure out that localhost should become that. I like using .socks better, though :)
– Ry-
                Jan 16, 2013 at 6:40
                I am really wonder why connecting via the socket works but not localhost. I mean, both adminer and mysql command line work fine with the credentials…
– Sardathrion - against SE abuse
                Sep 4, 2019 at 10:01
                Been searching for days for an answer and your answer finally solved my problem here: stackoverflow.com/questions/60317075/…
– J.E.C.
                Feb 20, 2020 at 13:17
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'm having similar trouble but using sqlalchemy --> pymysql --> mysql                            Can connect fine using commandline( mysql -h localhost ).... so it does seem to implicate pymysql
– Steve G
                May 4, 2018 at 18:57
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'
Run ssh -fN -L 3307:mysql_host:3306 ssh_user@ssh_host in my terminal.
Then input your ssh password
conn = pymysql.connect(db='base', user='root', passwd='pwd', host='localhost')
This error occurs because database does not support link directly.
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.
                Welcome to SO! Before answering a question, always read the existing answers. This answer has already been provided. Instead of repeating the answer, vote up the existing answer. Some guidelines for writing good answers can be found here stackoverflow.com/help/how-to-answer
– LightBender
                Dec 28, 2017 at 14:57
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.