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)Lastly, mysql.connector provides connection property "failover".
else:
print ("Finally - close connection")
cnx.close()
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!
