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:
- 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
- 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
