A very common task for a sysadmin is automation scripts, sometimes these scripts require external data such as a MySQL database, in this article I will explain how easy it is to access it from Python.
The test table is very simple:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| role | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
The script will insert two users, query the table, delete one of the users, and query again:
vi mysql.py
#!/usr/local/bin/python3.8
try:
import pymysql
except ImportError:
print('Please install dependency: pip install pymysql')
sys.exit(1)
db = pymysql.connect(unix_socket="/tmp/mysql.sock", user="root", passwd="XXXXXX", db="testpython")
cursor = db.cursor()
sql = "INSERT INTO testtable (username, role) VALUES (%s, %s)"
cursor.execute(sql, ('kr0m', 'sysadmin'))
sql = "INSERT INTO testtable (username, role) VALUES (%s, %s)"
cursor.execute(sql, ('kr0m2', 'sysadmin'))
cursor.execute("SELECT username, role FROM testtable;")
for row in cursor.fetchall():
print('------------------------------------------')
username = row[0]
print('Username: %s' % username)
role = row[1]
print('Role: %s' % role)
print('==========================================')
print('-- Deleting kr0m user')
sql = "DELETE FROM testtable WHERE username='kr0m'"
cursor.execute(sql)
cursor.execute("SELECT username, role FROM testtable;")
for row in cursor.fetchall():
print('------------------------------------------')
username = row[0]
print('Username: %s' % username)
role = row[1]
print('Role: %s' % role)
The output on the screen is as follows:
------------------------------------------
Username: kr0m
Role: sysadmin
------------------------------------------
Username: kr0m2
Role: sysadmin
==========================================
-- Deleting kr0m user
------------------------------------------
Username: kr0m2
Role: sysadmin