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?](http://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python) — Norbert, Jul 17 '15 at 22:06

score 1 · Answer 1 · edited May 23 '17 at 11:43

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 '17 at 11:43

Community

1
1

answered Jul 17 '15 at 22:11

awmo

329
1
2

I guess I will try PyMySQL. – JACK ZHANG Jul 17 '15 at 22:37

score 0 · Answer 2 · answered Jul 17 '15 at 22:06

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()

See http://dev.mysql.com/doc/connector-python/en/connector-python-versions.html. Yes, both are supported. Syntax may be slightly different with 3.x. — Pete LoGiudice, Jul 18 '15 at 06:28

Dalen · Accepted Answer · 2015-07-18T14:42:23.277

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 '15 at 14:42

answered Jul 17 '15 at 22:10

Dalen

4,128
1
17
35

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. – JACK ZHANG Jul 17 '15 at 22:38
That was an example. I'll produce some working code later today and post it. I am in a hurry at the moment. – Dalen Jul 18 '15 at 10:17
Did you try PyMSQL instead of MySQLdb? It should work. – Dalen Jul 18 '15 at 10:18
@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! – Dalen Jul 18 '15 at 14:51

How to check a given MySQL server connects through Python?

3 Answers3