cursor() raise errors.OperationalError("MySQL Connection not available.") OperationalError: MySQL Connection not available

Question

import requests
import time
import csv
import ast
import sys
import mysql.connector

config = {
'user': 'root',
'password': 'password',
'host': '127.0.0.1',
'port': '3306',
'database': 'dbname',
'raise_on_warnings': True,}

cnx = mysql.connector.connect(config)    
cursor = cnx.cursor()

Running gives:

Traceback (most recent call last):
  File "/home/ubuntu/scrapers/xrp2.py", line 17, in <module>
    cursor = cnx.cursor()
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 1383, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
OperationalError: MySQL Connection not available.

Does anyone know how to fix this? Other forums have had similar errors and fixed the problem by not having too many cursors open, but this is the first call to cursor(), so I'm not sure why it's unavailable. Do I need to close MySQL from the Ubuntu terminal?

My config file works fine connecting via Sequel Pro's SSH.

SOLVED: Put the configuration into the .connect(statement) instead of as a dictionary.

import requests
import mysql.connector

cnx = mysql.connector.connect(user ='root', password= 'p', host = '127.0.0.1',port='3306', database='coindb')

cursor = cnx.cursor()

Answer 1

This error will happen if your connection is already closed. In a Try-Except-Else block, it turns out Else is always executed if there is no error caught by the Except.

Therefore, this code was closing my connection immediately:

def mysql_get_mydb():
    '''Takes no args, and returns a connection to MYDB via MYSQL.'''

    creds = fixed_data.MYSQL_ENDPOINTS

    try:
        cnx = connector.connect(user='MYDB',
                              password='open_sesame',
                              host=creds['prod']['MYDB'][0],
                                port=3306,
                              database='MYDB')
    except connector.Error as err:
        if err.errno == connector.errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DV_ERROR:
            print("Database does not exist")
        else:
            print(err)
    # the else will happen if there was no error!
    else:
        cnx.close()

    return cnx

When I tried doing z = mysql_get_mydb() and y = z.cursor() an error is raised by y = z.cursor(). This is the exact error you've listed. You can also test this by opening a connection, closing it, then trying to define a cursor on it. Hopefully, this comment helps someone. The fix here is that the last else should contain return cnx (and the cnx.close() should be removed)

Answer 2

A more elegant solution than yours:

import requests
import time
import csv
import ast
import sys
import mysql.connector

config = {
'user': 'root',
'password': 'password',
'host': '127.0.0.1',
'port': '3306',
'database': 'dbname',
'raise_on_warnings': True,}

cnx = mysql.connector.connect(**config)    
cursor = cnx.cursor()