Python 3!! How to Use PYMYSQL Module

Hello every one in this article we are going to see how to connect mysql with python. We we try some program related like create database and table, insert data, retrieve data, Update data and Drop the table.

Let’s Us Begin

Table of content :-

• Basics of MySQL.
• How to establish a connection with MySQL and create a database.
• How to create a table and insert values.
• How to retrieve the data and update the data.
• How to drop the table.

Basic of MySQL :-

• MySQL is one of the popular database management systems.
• It is Open-source relational database management.
• It stores data in a table. Tables further store data in rows and columns.
• It is a freely available database system.
• It is a Structured Query Language.
• It can run on Multiple Platforms such as Linux, Windows and UNIX.
• It is a Highly Scalable Database System.

How to establish connection with MySQL and create database :-

As python is a rich programming language there is an inbuilt package for MySQL is called pymysql. At first we will import the package then create the connection object using connect() method whose basic parameters are host ,userpassword and database name

Python

import pymysql

con = pymysql.connect(host="localhost",user="root",passwd="")

After creating the connection we will create the cursor object using con.cursor() .Basically cursor work between application and database to send and retrieve the data.

Python

cur = con.cursor()

Then we have taken the string named Query in which we have stored the query which we want to execute. We will execute the Query using cur.execute() method and always make a habit to commit after execution of query.

Python

query="create database blog_demo_db"

cur.execute(query)

con.commit()

After completion of program make sure to close the connection and cursor using close() method

Program :-

Python

import pymysql

​

try:

    con = pymysql.connect(host="localhost",user="root",passwd="")

    cur = con.cursor()

    query="create database blog_demo_db"

    cur.execute(query)

    query1="show databases"

    cur.execute(query1)

    for x in cur:

        print(x)

​

except:

    print("Query Problem")

​

finally:

    if cur:

        cur.close()

    if con:

        con.close()

How to create table and insert values :-

Python

import pymysql

​

try:

    con = pymysql.connect(host="localhost",user="root",passwd="", database="blog_demo_db")

    cur = con.cursor()

    query="create table student(enrollment int,name varchar(20),branch varchar(20))"

    cur.execute(query)

    records=[(1,'jeff','CE'),(2,'John','CE'),(3,'carry','CE')]

    query1="insert into student values(%s,%s,%s)"

    cur.executemany(query1,records)

    con.commit()

except:

    print("Query Problem")

finally:

    if cur:

        cur.close()

    if con:

        con.close()

How to retrieve the data and update the data :-

Python

import pymysql

​

try:

    con = pymysql.connect(host="localhost",user="root",passwd="", database="blog_demo_db")

    cur = con.cursor()

    print("Before Update")

    query="select * from student"

    cur.execute(query)

    for x in cur:

        print(x)

    #For Updating

    query="update student set name='john' where enrollment=3"

    cur.execute(query)

    print("\nUpdating...")

    print("\nAfter Update")

    query="select * from student"

    cur.execute(query)

    for x in cur:

        print(x)

    con.commit()

except:

    print("Query Problem")

​

finally:

    if cur:

        cur.close()

    if con:

        con.close()

How to drop the table.

Python

import pymysql

​

try:

    con = pymysql.connect(host="localhost",user="root",passwd="", database="blog_demo_db")

    cur = con.cursor()

    query="drop table student"

    cur.execute(query)

    con.commit()

    print("Table Deleted")

except:

    print("Query Problem")

finally:

    if cur:

        cur.close()

    if con:

        con.close()

Output :-