How to check a given MySQL server connects through Python?

Question

all, I'm trying to verify a given MySQL server's connectivity through Python, provided with host, username, password, and schema. I tried to use subprocess module, Ex.

subprocess.check_call(['mysql', '-u', 'root'])

but it will bring up mysql in the terminal, which is not what I want. I also tried mysqldb, but it does not support Python 3+.

Is there a way to test out a MySQL server's parameters through Python? At least I want to know if it connects. It would be better if I could know more detail about the failure of connection.

Thanks!

possible duplicate of How do I connect to a MySQL Database in Python? — Norbert
– Norbert, Commented Jul 17, 2015 at 22:06

Community · Accepted Answer · 2017-05-23 11:43:51Z

1

According to this question, you can check out PyMySQL for Python 3.3+

If you really want to use subprocess, you can add the '-e' flag to execute a command in mysql.

subprocess.check_call(['mysql', '-u', 'root', '-ppass', '-e', "show databases;"])

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jul 17, 2015 at 22:11

awmo

3391 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JACK ZHANG Over a year ago

I guess I will try PyMySQL.

Dalen · Accepted Answer · 2015-07-18 14:42:23Z

0



import pymysql

def MySQLCheck(*args, **kwargs):
    try:
        conn = pymysql.connect(*args, **kwargs)
        cursor = conn.cursor()
        cursor.execute("show databases;")
        r = cursor.fetchall()
        conn.close()
        return r
    except Exception as e: return e

Usage examples:

>>> print(MySQLCheck(host="localhost", user="root", password="pass"))
(2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")
>>> # Later, when I started the server:
(1045, "Access denied for user 'root'@'localhost' (Using password: YES)")
>>> # Later, when I started MySQL server with "--skip-grant-tables" option:
(('information_schema',), ('drupal',), ('forum',), ('mysql',), ('p

erformance_schema',), ('phpmyadmin',), ('plugin',), ('test',))

Please note that returned values of MySQLCheck() may be an exception or a tuple if all went fine and query got executed.

To install PyMySQL for Python3 on Ubuntu/Debian do:

$ sudo apt-get install python3-setuptools
$ sudo easy_install3 pip
$ sudo pip3.2 install PyMySQL

I hope this is an answer you needed.

edited Jul 18, 2015 at 14:42

answered Jul 17, 2015 at 22:10

Dalen

4,2461 gold badge20 silver badges36 bronze badges

4 Comments

JACK ZHANG Over a year ago

I'm not sure what your code does, but I tried it and it doesn't do much. What I really I want to know is that if the given parameters of a MySQL server is valid, i.e. if it connects. Thanks tho.

Dalen Over a year ago

That was an example. I'll produce some working code later today and post it. I am in a hurry at the moment.

Dalen Over a year ago

Did you try PyMSQL instead of MySQLdb? It should work.

Dalen Over a year ago

@JACK Yey! PyMySQL works on Python 3 with no problems. In fact it has a Python 3 version. I edited my answer completely and put up code I think you need. Good luck!

Pete LoGiudice · Accepted Answer · 2015-07-17 22:06:00Z

0

You can use something like this. A config file with your connection parameters and a try statement with error checking.

Create a file called config.py

config = {
  'user': 'username',
  'password': 'password',
  'host': 'host_name or ip',
  'database': 'database',
  'raise_on_warnings': True,
}

Using mysql.connenctor

try:
   cnx = mysql.connector.connect(**config)

   cursor = cnx.cursor()

        query = ("select statement")         

        cursor.execute(query)

   cursor.close()
   cnx.close()

except mysql.connector.Error as err:
   if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
   elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exists")
   else:
        print(err)
else:
   cnx.close()

answered Jul 17, 2015 at 22:06

Pete LoGiudice

7511 bronze badges

2 Comments

JACK ZHANG Over a year ago

Does it support both python 2.x and python 3.x?

Pete LoGiudice Over a year ago

See dev.mysql.com/doc/connector-python/en/…. Yes, both are supported. Syntax may be slightly different with 3.x.

Collectives™ on Stack Overflow

How to check a given MySQL server connects through Python?

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related