In this Python database connection tutorial, we will learn to create a connection between Python and MySQL. We will also learn all CRUD operations that is, create, retrieve, update and delete.

Why Database Connection?

In this world, all major applications store data and present it in a useful manner. For the example banking system, email system, reservation system and many more applications which store your information and present it whenever required. Here, we will use Python to create application and MySQL to store data.

Software Requirement

For Python Mysql connection, you should have pre-installed MySQL and MySQL connector along with the Python in your PC. You can download MySQL and MySQL connector from this link https://www.mysql.com/downloads/

You can test mysql connector installation by writing import mysql.connector on python shell.

Connecting MYSQL

In Python, we need to follow four steps to connect with MySQL.

1. Create a Connection
2. Create Cursor
3. Query with the Database
4. Close the Connection

Let’s Start with The Example

First, we will look at the Python example to create a database and the table, after that we will discuss the steps.

Example to Create Database from Python

If you have knowledge of the MySQL, you can create a database in the MySQL and skip this step. Or you can execute these below codes to create the database and tables in Python.

Python code to create the database

import mysql.connector
con=mysql.connector.connect(host="localhost", user="root", passwd="root")
cur=con.cursor()
cur.execute("create database coder")
con.close()

Python code to create the table

import mysql.connector
con=mysql.connector.connect(host="localhost", user="root", passwd="root", database="coder")
cur=con.cursor()
cur.execute("create table Book( BookName varchar(30), Author varchar(30) )")
con.close()

Let’s Discuss the steps.

First, we need to import mysql.connector module in our program to connect with the MySQL.

1. Create a Connection – mysql.connector.connect() function establish the connection between Python and Database than return a connection object.

con=mysql.connector.connect(
	host="localhost",
	user="root",
	passwd="root",
	database="coder"
)

• host – Server or Hostname (localhost, if the database is on the same server or computer)
• user – Username of the database.
• passwd – Password of the database.
• database – Name of the database to be connected.

2. Create Cursor – The cursor() method creates a cursor object. Cursor object is responsible for executes SQL query in the database.

cur=con.cursor()

3. Query with Database – The execute() function, executes the SQL query in the database.

cur.execute("create database coder")

4. Close the Connection – At the end, we need to close the connection using the close() function.

con.close()

Example to Insert(store) the Records

To store records in a table, INSERT query(SQL) is used.

import mysql.connector
con = mysql.connector.connect(host="localhost",user="root",passwd="root", database="coder")
cur = con.cursor()

#accept user input to store
bookname = input("Enter Book Name")
author = input("Enter Author")

#execute insert query to store the data
cur.execute("insert into Book values('{}','{}')".format(bookname,author))
print("Record Inserted")
con.commit()    #commit means save the record
con.close()

Connection must be commited to edit in a table using commit() function.

Insert(store) Multiple Data

We can insert multiple records at a time using executemany() function, it accepts two-parameters, query and data.

import mysql.connector
con = mysql.connector.connect(host="localhost",user="root",passwd="root", database="coder")
cur = con.cursor()
#creeate list of book as tuple
data=[("Head First Python", "Paul Barry"),
      ("Learn Python the HARD WAY", "Zed A. Shaw"),
      ("Python Crash Course", "Eric Matthews"),
      ("A Byte of Python", "C. H. Swaroop")
      ]

#query string, %s is format for string
query="insert into Book values(%s,%s)"

cur.executemany(query,data)
print("Record Inserted")
con.commit()    #commit means save the record
con.close()

Example to Show the Records (SELECT).

Now, we will write a program in Python to access data of the database and print them.

import mysql.connector
con = mysql.connector.connect(host="localhost",user="root",passwd="root", database="coder")
cur = con.cursor()

cur.execute("select * from Book")
print(cur.fetchall())
con.close()

#fetchall() method, fetch all the data from cursor object

Show Data in Tabular Format

import mysql.connector
con = mysql.connector.connect(host="localhost",user="root",passwd="root", database="coder")
cur = con.cursor()

cur.execute("select * from Book")
data = cur.fetchall()   #fetch and store in a variable

print("Book\t\tAuthor")
print("--------------------------")

for row in data:
    for cell in row:
        print(cell,end='\t\t')
    print()

con.close()

In the above example, we are using fetchall() function to fetch all the records from the database. To fetch a single record, we can use fetchone() method and for fetch many some records we can use fetchmany(size) method.

Python MySQL Update Record

We can modify the existing records, In the below example, we will change the Author of a Book.

import mysql.connector
con = mysql.connector.connect(host="localhost", user="root", passwd="root", database="coder")
cur = con.cursor()

#accepting record to be modify
bookname=input("Enter the Book Name")
author=input("New Author to be Modify")

#update query, to modify data
cur.execute("update Book set author='{}' where bookname='{}'".format(author , bookname))
print("Record Updated")

con.commit()  
con.close()

Python MySQL Delete Records

We can remove records from the table using DELETE query.

import mysql.connector
con = mysql.connector.connect(host="localhost", user="root", passwd="root", database="coder")
cur = con.cursor()

#accepting record to be remove
bookname=input("Enter the Book Name")

#delete query, to remove records
cur.execute("delete from Book where bookname='{}'".format(bookname))
print("Record Removed")
con.commit()  
con.close()

You can learn in detail about format() function in String chapter.