Load Database table into pandas DataFrame

Reading data from a MySQL database:

mysql.connector:

• It is a package(module).
• Python library is not providing any module to connect with mysql database.
• connector module contains programs by which we connect with mysql database from python application.

connect():

• Pre-defined functionality mysql.connector module.
• Prototype is;
  • connect(user, password, host, database)
• On success, returns connection object with database, on failure returns None object

cursor(): Dynamic method of Connection object. Returns cursor object when we call. Cursor object providing execute() method. execute() method can be used to execute any sql query in the database.

import mysql.connector
class DB:
    con=None
    def main():
        try :
            DB.con = mysql.connector.connect(user='root', password='root', host='127.0.0.1',
                                    database='student')
            print("Connected...")
            cur = DB.con.cursor()

            query = "select * from account"            
            cur.execute(query)

            table = cur.fetchall()
            print("Records are :")
            for record in table :
                print(record)

        except Exception as e:
            print("Exception :",e)

        finally:
            if DB.con != None:
                DB.con.close()
                print("Connection closed..")
        return
DB.main()

Output:

101		amar	8000
102		annie	15000 
103		hairn	9000

Import Database table into DataFrame Object:

import mysql.connector as sql
import pandas as pd

db = sql.connect(host='localhost', database='student', user='root', password='root')
print("Connected")

cur = db.cursor()
cur.execute('select *from account')
table = cur.fetchall()
frame = pd.DataFrame(table)
print(frame)

Output:

Connected
     	0      	1     		2
0  	101   	Amar  		8000
1  	102  	annie  		9000