How to connect to a database on mySQLworkbench using jupyter notebook

Technical Topics
1

I’ve tried so many alternative syntaxes and can’t seem to connect mySQLworkbench to jupyter notebook.

Here what error i keep getting back

--------------------------------------------------------------------------
ConnectionRefusedError                    Traceback (most recent call last)
File ~/.local/lib/python3.11/site-packages/pymysql/connections.py:661, in Connection.connect(self, sock)
    660 try:
--> 661     sock = socket.create_connection(
    662         (self.host, self.port), self.connect_timeout, **kwargs
    663     )
    664     break

File /opt/conda/envs/anaconda-panel-2023.05-py310/lib/python3.11/socket.py:851, in create_connection(address, timeout, source_address, all_errors)
    850 if not all_errors:
--> 851     raise exceptions[0]
    852 raise ExceptionGroup("create_connection failed", exceptions)

File /opt/conda/envs/anaconda-panel-2023.05-py310/lib/python3.11/socket.py:836, in create_connection(address, timeout, source_address, all_errors)
    835     sock.bind(source_address)
--> 836 sock.connect(sa)
    837 # Break explicitly a reference cycle

ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

OperationalError                          Traceback (most recent call last)
Cell In[7], line 5
      1 import pymysql
      3 db_name = "project_2"
----> 5 conn=pymysql.connect(db=db_name,
      6                      host=db_host,
      7                      port=int(3306),
      8                      user='root',
      9                      passwd=db_password)
     12 if conn:
     13     print("Successfully connected")

File ~/.local/lib/python3.11/site-packages/pymysql/connections.py:365, in Connection.__init__(self, user, password, host, database, unix_socket, port, charset, collation, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_key_password, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)
    363     self._sock = None
    364 else:
--> 365     self.connect()

File ~/.local/lib/python3.11/site-packages/pymysql/connections.py:723, in Connection.connect(self, sock)
    721     if DEBUG:
    722         print(exc.traceback)
--> 723     raise exc
    725 # If e is neither DatabaseError or IOError, It's a bug.
    726 # But raising AssertionError hides original error.
    727 # So just reraise it.
    728 raise

OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")
2

That error basically means your Jupyter notebook can’t reach the MySQL server at all. MySQL Workbench isn’t something you connect to directly, it’s just a client, so your notebook needs to connect to the actual MySQL service using the same host, port, and credentials. If you’re getting “connection refused,” it usually means the MySQL server isn’t running, or you’re using the wrong port or host. A common issue is that Workbench might be using a different port like 3307 while your code is trying 3306, or MySQL is bound to a different interface.

Try using the exact same connection details from Workbench and also switch localhost to 127.0.0.1 since that forces a TCP connection instead of a socket. If MySQL is running inside Docker or another machine, then localhost won’t work at all and you’ll need the correct IP and exposed port. This isn’t a database corruption issue, just a connectivity problem, but if your MySQL service had previous crashes and tables got affected, then tools like Stellar Repair for MySQL can help recover data, otherwise just focus on fixing the connection settings.