CRUD Operations in Python on MySQL

1. install MySQL on Ubuntu

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Verify the installation:
sudo mysql
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
mysql> FLUSH PRIVILEGES;
mysql> exit
$ mysql -u root -p to test your login

2. ubuntu@ubunu2004:~$ pip3 install mysql-connector-python

create db.py to create db, table and records

import mysql.connector #Importing Connector package   
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password")#established connection   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create database dbpython")#Execute SQL Query to create a database    
mysqldb.close()#Connection Close  

#Create a table into dbpython database  
import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password",database="dbpython")#established connection between your database   
mycursor=mysqldb.cursor()#cursor() method create a cursor object  
mycursor.execute("create table student(roll INT,name VARCHAR(255), marks INT)")#Execute SQL Query to create a table into your database  
mysqldb.close()#Connection Close  

import mysql.connector  
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password",database="dbpython")#established connection between your database  
mycursor=mysqldb.cursor()#cursor() method create a cursor object    
try:  
   #Execute SQL Query to insert record  
   mycursor.execute("insert into student values(1,'Sarfaraj',80),(2,'Kumar',89),(3,'Sohan',90)")  
   mysqldb.commit() # Commit is used for your changes in the database  
   print('Record inserted successfully...')   
except:  
   # rollback used for if any error   
   mysqldb.rollback()  
mysqldb.close()#Connection Close  

ubuntu@ubunu2004:~$ python3 db.py
Record inserted successfully…
you can also check in MySQL:
mysql> show databases;
mysql> use dbpython;
mysql> show tables;
+——————–+
| Tables_in_dbpython |
+——————–+
| student |
+——————–+
1 row in set (0.00 sec)
mysql> select * from student;
+——+———-+——-+
| roll | name | marks |
+——+———-+——-+
| 1 | Sarfaraj | 80 |
| 2 | Kumar | 89 |
| 3 | Sohan | 90 |
+——+———-+——-+
3 rows in set (0.00 sec)

3. create update_record.py

import mysql.connector
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password",database="dbpython")#established connection between your database
mycursor=mysqldb.cursor()#cursor() method create a cursor object
try:
   mycursor.execute("UPDATE student SET name='Ramu', marks=100 WHERE roll=1")#Execute SQL Query to update record
   mysqldb.commit() # Commit is used for your changes in the database
   print('Record updated successfully...')
except:
   # rollback used for if any error
   mysqldb.rollback()
mysqldb.close()#Connection Close

4. create delete_record.py

import mysql.connector
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password",database="dbpython")#established connection between your database
mycursor=mysqldb.cursor()#cursor() method create a cursor object
try:
   mycursor.execute("DELETE FROM student WHERE roll=3")#Execute SQL Query to detete a record
   mysqldb.commit() # Commit is used for your changes in the database
   print('Record deteted successfully...')
except:
   # rollback used for if any error
   mysqldb.rollback()
mysqldb.close()#Connection Close

5. test the code with display_db.py

import mysql.connector
mysqldb=mysql.connector.connect(host="localhost",user="root",password="password",database="dbpython")#established connection between your database
mycursor=mysqldb.cursor()#cursor() method create a cursor object
try:
   mycursor.execute("select * from student")#Execute SQL Query to select all record
   result=mycursor.fetchall() #fetches all the rows in a result set
   for i in result:
      roll=i[0]
      name=i[1]
      marks=i[2]
      print(roll,name,marks)
except:
   print('Error:Unable to fetch data.')
mysqldb.close()#Connection Close

ubuntu@ubunu2004:~$ python3 display_db.py
1 Sarfaraj 80
2 Kumar 89
3 Sohan 90
ubuntu@ubunu2004:~$ python3 update.py
Record updated successfully…
ubuntu@ubunu2004:~$ python3 display_db.py
1 Ramu 100
2 Kumar 89
3 Sohan 90
ubuntu@ubunu2004:~$ python3 delete.py
Record deteted successfully…
ubuntu@ubunu2004:~$ python3 display_db.py
1 Ramu 100
2 Kumar 89

NOTE:

1. if you cannot connect to MySQL from remote, you need update the binding port:
   sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
   from:
   bind-address = 127.0.0.1
   change to:
   bind-address = 0.0.0.0
   then restart MySQL
   sudo systemctl restart mysql.service
2. if you got error msg:
   mysql.connector.errors.DatabaseError: 1130: Host ‘192.168.0.28’ is not allowed to connect to this MySQL server
   root is NOT allowed login from remote, you can create a new user and grant PRIVILEGES:
   mysql> CREATE USER ‘monty’@’%’ IDENTIFIED BY ‘somIUpass#98’;
   mysql> GRANT ALL PRIVILEGES ON . TO ‘monty’@’%’ WITH GRANT OPTION;
   then you can query from remote with this ID:
   C:\Users\zhuby\python_code>python display_db.py
   1 Ramu 100
   2 Kumar 89