This post is written with respect to python 3 only.

Let’s get direct into this;

You can get the code on GitHub or download ZIP

Prerequisites:

• MySQL sever and client
• Python3.x installed

First install pymysql package using pip or pip3 in your python, so open Command prompt and type the following command:

pip3 install pymysql

Now check if it is installed successfully or not

pip3 freeze | grep 'pymysql'

Now open your MySQL client/CLI and work as follows:

mysql> USE test;
Database changed

Now now create a sample named "STUDENT" table in it.

mysql> CREATE TABLE STUDENT(rollno int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.16 sec)

Now check the table description

mysql> DESC student;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| rollno | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Now we are ready to work with our this database now open any of your python console and follow the commands.

Check whether the connection is working or not:

import pymysql
try:
   con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
   print("Connection Successful :-)")
except:
        print("Connection Unsuccessful :-(")

If the output is “Connection Successful :-)” then everything is working fine and we can proceed.

Insert values in our database:

import pymysql
con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
cursor=con.cursor()
sql='INSERT INTO `student`(`name`) VALUES(%s)'
cursor.execute(sql,('Azhar Khan'))
con.commit()
con.close()

You can check the whether is it worked or not from MySQL CLI

mysql> SELECT * FROM student;
+--------+------------+
| rollno  | name      |
+--------+------------+
|       1 | Azhar Khan|
+--------+------------+
1 row in set (0.00 sec)

Retrieving values from database:

import pymysql
con=pymysql.connect(host='localhost',port=3306,user='root',passwd='root',db='test')
cursor=con.cursor()
sql="SELECT * FROM `student`"
cursor.execute(sql)
results=cursor.fetchall()
for i in results:
  print(i)

I hope that the post was useful, lazy coders can get code from GitHub: