Python with MySQL Connectivity: Database & Table [Examples]

• What is MySQL?

• Install MySQL Connector Library for Python

• Test the MySQL Database connection with Python

• Creating Database in MySQL using Python

• Create a Table in MySQL with Python

• Create a Table with Primary Key

• ALTER table in MySQL with Python

• Delete Operation with MySQL in Python

2.

3.   Exporting a Dataframe’s Data as a Table in MySQL Database. Open command prompt Click Run as administrator.

import pandas as pd import sqlalchemy as sq con=sq.create_engine("mysql+mysqlconnector://root:root@localhost/school") #school is database name df=pd.read_sql("teachers",con) #Teachers table name print(df)

• Test the MySQL Database connection with Python

mycursor.execute("SHOW DATABASES")
#print all databases
for db in mycursor:
          print(db)

·         What is MySQL?

MySQL is an Open-Source database and one of the best type of RDBMS (Relational Database Management System). Co-founder of MySQLdb is Michael Widenius’s, and also MySQL name derives from the daughter of Michael.

·         Install MySQL Connector Library for Python

Required Library

1.   1. MySQL connector package

C:\>pip install sqlalchemy

The connection format string is :

Mysql+mysqlconnecotr://username:password@host:port/Database_name

• Syntax to access MySQL with Python:

  import mysql.connector

        db_connection = mysql.connector.connect(

        host="hostname",

        user="username",

        passwd="password")

  Example:

  import mysql.connector

  mydb = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="root")

  print(mydb)

  Output:

  <mysql.connector.connection.MySQLConnection object at 0x000002338A4C6B00>

   

  ·         Creating Database in MySQL using Python

   

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",

                                          user="root",

                                    passwd="root")

  mycursor=mydb.cursor()

  mycursor.execute("CREATE DATABASE TEACHER")

  # show databases:

  Output

  
  
  

  ·         Create a Table in MySQL with Python

   

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",

                                                  user="root",

                                                    passwd="root",

                                                    database='teacher')

  mycursor=mydb.cursor()

  mycursor.execute("Create Table Teachers(Tno int,Tname varchar(10),DOj date)")

  print("Successfully Create table.")

      # show databases:

  mycursor.execute("SHOW TABLES")

  #print all databases

  for tb in mycursor:

            print(tb)           

  Output

  

  
  
  

  ·         Create a Table with Primary Key

   

  Let’s create an Students table with three different columns. We will add a primary key in Scode column with AUTO_INCREMENT constraint as shown in the below Python project with database connectivity.

  SQL Syntax:

  CREATE TABLE Students(Scode INT AUTO_INCREMENT PRIMARY KEY, Sname VARCHAR(10), DOB Date)

  Example:

  import mysql.connector

  mydb = mysql.connector.connect(host="localhost",user="root",

    passwd="root", database="teacher"  )

  mycursor = mydb.cursor()

  #Here creating database table as employee with primary key

  mycursor.execute("CREATE TABLE Students(Scode INT AUTO_INCREMENT PRIMARY KEY, Sname VARCHAR(10), DOB Date)")

  #Get database table

  mycursor.execute("SHOW TABLES")

  for table in mycursor:

            print(table)

  ·         Add Record into table with Python

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",\

                           user="root",passwd="root", database='teacher')

  mycursor=mydb.cursor()

  tcode=111

  sql="Select* from teachers"

  mycursor.execute(sql)

  #Auto Generate Teacher Code

  for x in mycursor:

      tcode=x[0]

  tcode=tcode+1

  print("Teacher code :",tcode)

  tname=input("Enter Teacher name ")

  doj=input("Enter teacher date of joining (yyyy-mm-dd)")

  info=(tcode,tname,doj)  # converts into tuple

  sql="insert into teachers values(%s,%s,%s)"

  mycursor.execute(sql,info)

  mydb.commit()

  print(mycursor.rowcount," Record Inseted")  

  Output:

  
  
  
  

  ·         Show Table record with Python

   

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",\

                               user="root",passwd="root", database='teacher')

  mycursor=mydb.cursor()

  print("show all records")

  mycursor.execute("select * from teachers")

  print("Code\tName\tDate of Joining")

  print("*"*50)

  for x in mycursor:

      print(x[0],"\t",x[1],"\t",x[2])

  print("*"*50)

  Output:

  

  ·         ALTER table in MySQL with Python

   

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",\

                           user="root",

                          passwd="root",

                          database='teacher')

  mycursor=mydb.cursor()

  tno=input("Enter teacher code :")

  sql="Select * from teachers where tno=%s"

  mycursor.execute(sql,(tno,))

  for x in mycursor:

      print("Teacher Code : ",x[0])

      print("Teacher Name : ",x[1])

      print("Date of joining : ",x[2])

      print("*"*50)

  ch=input("Do u want to update record press y/n -> ").upper()

  if(ch=='Y'):

      tname=input("Enter new Teacher name ")

      doj=input("Enter new teacher date of joining (yyyy-mm-dd)")

      info=(tname,doj,tno,)

      sql="Update teachers set tname=%s,doj=%s where tno=%s"

      mycursor.execute(sql,info)

      mydb.commit()

      print(mycursor.rowcount," Record updated")

  Output:

  
  
  
  

  ·         Delete table in MySQL with Python

   

  import mysql.connector

  mydb=mysql.connector.connect(host="localhost",\

                           user="root",

                          passwd="root",

                          database='teacher')

  mycursor=mydb.cursor()

  tno=input("Enter teacher code :")

  sql="Select * from teachers where tno=%s"

  mycursor.execute(sql,(tno,))

  for x in mycursor:

      print("Teacher Code : ",x[0])

      print("Teacher Name : ",x[1])

      print("Date of joining : ",x[2])

  print("*"*50)

  ch=input("Do u want to delete record press y/n -> ").upper()

  if(ch=='Y'): 

      sql="delete from teachers where tno=%s"

      mycursor.execute(sql,(tno,))

      mydb.commit()

      print(mycursor.rowcount," Record deleted")

  Output: