Python Database Connectivity with MySQL

So again another most demanded topic this week is Python's Database Connection

I am going to cover everything in a peeling way. I will tell you exactly how to do it without any crap.

DBMS

To store data we need a database. A database represents a collection of data. We can also perform some operations on data. for example modifying the existing data, deleting the unwanted data, or retrieving the data from the database. To perform such operations a database comes with the software. This is called a database management system(DBMS).

DBMS = database + software to manage the data

Examples for DBMS are MYSQL, ORACLE, SYBASE, SQL SERVER, etc.

Types of Databases Used with Python

So here I will certainly talk about Python and Databases for a better understanding of how it works with Python.

To work with any database we should have the database installed in our computer system. And to connect to that database we need a driver or connector program that connects the database with Python program.

So now let's use MYSQL very commonly used database. In order to install it open the mysql.com website and download the installer.

https://dev.mysql.com/downloads/windows/installer/

Just click next - next and install it after this how to verify that MYSQL is installed properly.

Verifying MySQL in the Windows Operating System

So for this just go to the Start -> your computer and you will find the MYSQL option. but you have to do one more thing after this is like

Installing MYSQLdb Module

Why this? because To communicate with MYSQL database from Python we need a program that becomes an interface to the MySQL database server. So MYSQLdb is an interface between the MySQL server and Python programs. To install it you have to do this way.

https://pypi.python.org/pypi/mysqlclient

So after this, you are ready to use the MySQL database with Python certainly. Whatever  I have mentioned is for the core-python developer.

So let me give you some examples of simple programs that you can directly execute on your systems.

#Program : 

Retrieve and Display all rows from a any table.

import MySQLdb

# connect to Mysql database

conn = MYSQLdb.connect(host = 'localhost' , database = 'Anything' , user = 'root' , password ='Pass123')

# prepare a cursor object using cursor() method

cursor = conn.cursor()

# execute the query

cursor.execute("select * from table")

# fetch only one row

row = cursor.fetchone()

#if the rows exists

while row is not None:

        print(row)

         row = cursor.fetchone()

# close the connection

cursor.close()

conn.close()

Output: In My case, it is like below

Connected to MySQL database

(1001 ,  'Abhinav', '10000')

(1002 ,  'Pritam' , '10000')

(1003 ,  'Jayanta' , '10000')

(1004 ,  'Arindam' , '10000')

So we can also use the fetchall() method that retrieved all the rows from my table like 

rows = cursor.fetchall()

This is a simple database connection program for Python Developer.