Friday, June 10, 2016

Python App connecting to MySQL with mysql.connector + Failover



The following is the very basic Python App connecting to MySQL Database. (01-connect.py)
It connects to the MySQL Database (127.0.0.1:3306 with root and empty password). 
import mysql.connector
import sys

if len(sys.argv) > 1 :
        _pass = sys.argv[1]
else :
        _pass = ''
cnx = mysql.connector.connect(user='root', password=_pass, host='127.0.0.1', port=3306, database='mysql')

cnx.close()

The alternative using Python Dictionary parameter -

This can be useful way to connect to MySQL Database with named parameters. (02-connect.py)
import mysql.connector
import sys

_pass = ''
if len(sys.argv) > 1 :
        _pass = sys.argv[1]

config = {
        'user': 'root',
        'password': _pass,
        'host': '127.0.0.1',
        'database': 'mysql',
        'raise_on_warnings': True,
}

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

Error Handling with MySQL  (03-connectTry.py)
The following example shows the connection to the default db ("test") with 127.0.0.1:3306 using root empty password user.    The error handling processing using try/except/else together with mysql.connector.errorcode.  By providing first argument with invalid password, the python app will get  exception - "errorcode.ER_ACCESS_DENIED_ERROR".  Whereas by giving 2nd argument with a non-exists database, the python app will generate exception "errorcode.ER_BAD_DB_ERROR".

import mysql.connector
from mysql.connector import errorcode
import sys

_pass=''
_db='test'
if len(sys.argv) > 1 :
        _pass = sys.argv[1]
if len(sys.argv) > 2 :
        _db = sys.argv[2]

try:
  cnx = mysql.connector.connect(user='root', host='127.0.0.1', port=3306,
                                password=_pass,
                                database=_db)

  print("connect success")
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 exist")
  else:
       print(err)
else:
  print ("Finally - close connection")
  cnx.close()
Lastly,  mysql.connector provides connection property "failover".
Please find more details on 
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

The following sample (04-failover.py) provides a sample to connect to databases (3306 and 3316).  If 3306 is stopped, the connection goes to the 3316. 

import mysql.connector
from mysql.connector import errorcode

DB_NAME = 'mysql'

operation = 'SELECT @@hostname, @@port'

config = {
        'raise_on_warnings': True,
        'failover' : [{
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3306,
                'database': 'mysql',
                }, {
                'user': 'root',
                'password': '',
                'host': '127.0.0.1',
                'port': 3316,
                'database': 'mysql',
                }]
}
cnx = mysql.connector.connect(**config)

cursor = cnx.cursor()

try:
  for result in cursor.execute(operation, multi=True):
     if result.with_rows:
        print("rows producted by statement '{}':".format(result.statement))
        row = cursor.fetchone()
        while row:
           print(row)
           row = cursor.fetchone()
     else:
        print("Number of rows affeted by statement '{}':{}".format(result.statement, result.rowcount))
except mysql.connector.Error as err:
        print(err.msg)

Enjoy!


No comments:

Post a Comment