This python script shows you how easy it is to work with a MySQL database. It requires a stock install of MySQL with python v3 installed.
This information is largely from this excellent site.
source: https://www.geeksforgeeks.org/python-mysql/
Setup
This will update your system, install pip and get the python module for MySQL.
1
2
3
sudo apt-get update && sudo apt-get update -y
sudo apt install python3-pip
pip3 install mysql-connector-python
Create a user in MySQL
1
2
3
4
5
sudo mysql
mysql> CREATE USER 'some_user'@'localhost' IDENTIFIED BY '123456';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'some_user'@'localhost';
mysql> quit
This allows this user to have an “all access pass” to all databases. In production, database specific user permissions should be granted.
Create database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# importing required libraries
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="some_user",
passwd ="123456"
)
# preparing a cursor object
cursorObject = dataBase.cursor()
# creating database
cursorObject.execute("CREATE DATABASE some_name")
# Disconnecting from the server
dataBase.close()
Python script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="some_user",
passwd ="123456",
database = "some_name"
)
# --------------------------------------------------
# preparing a cursor object
cursorObject = dataBase.cursor()
# --------------------------------------------------
# creating table
studentRecord = """CREATE TABLE STUDENT (
NAME VARCHAR(20) NOT NULL,
BRANCH VARCHAR(50),
ROLL INT NOT NULL,
SECTION VARCHAR(5),
AGE INT
)"""
cursorObject.execute(studentRecord)
# --------------------------------------------------
# insert a single record
sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("Ram", "CSE", "85", "B", "19")
cursorObject.execute(sql, val)
dataBase.commit()
# --------------------------------------------------
# insert several records
sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("Ram", "CSE", "85", "B", "19")
cursorObject.execute(sql, val)
dataBase.commit()
# --------------------------------------------------
# print a list of records
query = "SELECT NAME, ROLL FROM STUDENT"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
# --------------------------------------------------
# disconnecting from server
dataBase.close()