MySQL/Python Examples

NOTE: Port 3306 (or your defined alternative) must be open between your client computer and the target MySQL server.

Your username must have access from the client computer

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password', host='hostname', database='database_name')

cnx.close()

MySQL Enterprise Monitor Example

This example connects to the MEM database and retrieves a list of monitored hosts.

  • It assumes a login-path called 'mem' has been configured.

#!/usr/bin/python3.6

import mysql.connector

import myloginpath


conf = myloginpath.parse('mem')


try:

mem = mysql.connector.connect(**conf,database='mem__inventory',auth_plugin='mysql_native_password')

cursor = mem.cursor()


query1 = """SELECT hostname from host"""


cursor.execute(query1)


result = cursor.fetchall()

for row in result:


split = row[0].split(".")

host = split[0]

print (host)


except mysql.connector.Error as err:

print("Something went wrong: {}".format(err))


else:

cursor.close()

mem.close()

See also:

  • cursor.fetchall()

  • cursor.fetchmany(size)

  • cursor.fetchone()

Uptime Example

uptime.py

import mysql.connector


config = {

'user': 'username',

'password': 'password',

'host': 'hostname',

'database': 'information_schema'

}


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

cursor = cnx.cursor()


query = ("select convert(VARIABLE_VALUE/60/60/24,decimal(7,2)) as Uptime "

"from information_schema.GLOBAL_STATUS "

"where VARIABLE_NAME='Uptime' ")


cursor.execute(query)


for (Uptime) in cursor:

print("{}".format(Uptime))


cursor.close()


cnx.close()

To run it...

python uptime.py

(Decimal('71.15'),)