Python and MySQL

This tutorial will help you to learn how to use MySQL with Python.

Introduction:

Python MySQL Connector is used to enable python programs to access MySQL databases using API.

In Python, you need to install a Python MySQL Connector to interact with MySQL database. You can get it with PIP:

> pip install mysql-connector-python

Open command prompt and run the above command to install Python MySQL Connector.

Test your connector is successfully installed or not with below program.

testconnector.py

import mysql.connector

Run the above code if you see errors means connector driver were not properly installed.

General Steps:

1. Connect to the MySQL server with existing DB or newly created DB.
2. Execute a SQL query or fetch results.
3. Close the connection to the MySQL server.

Establish Connection:

To establish connection between Python and MySQL Server we use mysql.connector.connect() method.

estconnection.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost", user="root", passwd="admin")
  if conn:
    print("Connection established.")
except Error as e:
  print("Connection not established.",e)

Note: In the above program, the passwd parameter value is the password of MySQL Server.

Creating Database:

Creating a cursor object is used to execute SQL queries.

createDB.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin")
  if conn.is_connected():
    print("Connection established.")
    mycursor = conn.cursor()
    mycursor.execute("create database if not exists college")
    mycursor.execute("show databases")
    for db in mycursor:
      print(db)
except Error as e:
  print("Connection not established.",e)

The following program is the example for establish connection with existing database.

selectDB.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  if conn.is_connected():
    print("Connection established.")
except Error as e:
  print("Connection not established.",e)

Executing Queries:

execute() - This method accepts a MySQL query as a parameter and executes the given query.

Example: The following example shows how to create student table.

createTable.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  mycursor = conn.cursor()
  mycursor.execute("create table if not exists student(rollno varchar(11), name varchar(30))")
  mycursor.execute("show tables")
  print("Tables in college database")
  for table in mycursor:
    print(table)
  print("Student table description")
  mycursor.execute("desc student")
  for r in mycursor:
    print(r)
except Error as e:
  print("Connection not established.")
finally:
  if(conn.is_connected()):
    mycursor.close()
    conn.close()

Tables in college database
('student',)
Student table description
('rollno', 'varchar(11)', 'YES', '', None, '')
('name', 'varchar(30)', 'YES', '', None, '')

Example: The following example shows how to insert record into student table.

insertRecord.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  if conn.is_connected():
    mycursor = conn.cursor()
    mycursor.execute("insert into student values('501','nani')")
    conn.commit()
    print(mycursor.rowcount,"record inserted.")
except Error as e:
  print("Database Error.Try again later.",e)
finally:
  if conn.is_connected():
    mycursor.close()
    conn.close()

1 record inserted.

executemany() - This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters.

Example: The following example shows how to insert multiple records into student table.

insertMultipleRecords.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  if conn.is_connected():
    mycursor = conn.cursor()
    sql="insert into student(rollno, name) values(%s,%s)"
    val=[('502','siva parvathi'),('503','manasa')]
    mycursor.executemany(sql,val)
    conn.commit()
    print(mycursor.rowcount,"records inserted.")
except Error as e:
  print("Connection not established.",e)
finally:
  if conn.is_connected():
    mycursor.close()
    conn.close()

2 records inserted.

Fetch Records:

fetchone() - This method fetches the next row in the result of a query and returns it as a tuple.

The following example shows how to extract one record from student table.

fetchOneRecord.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  mycursor = conn.cursor(buffered=True)
  mycursor.execute("select * from student")
  #Retrive one record
  rec = mycursor.fetchone()
  print(rec)
except Error as e:
  print(e)
finally:
  if conn.is_connected():
    mycursor.close()
    conn.close()

('501', 'nani')

fetchmany() - This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.

The following example shows how to extract many records from student table.

fetchManyRecords.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  mycursor = conn.cursor(buffered=True)
  mycursor.execute("select * from student")
  #Retrieve 2 records
  recs = mycursor.fetchmany(size=2)
  print(recs)
except Error as e:
  print(e)
finally:
  if conn.is_connected():
    mycursor.close()
    conn.close()

[('501', 'nani'), ('502', 'siva parvathi')]

fetchall() - This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones).

The following example shows how to extract all records from student table.

fetchAllRecords.py

import mysql.connector
from mysql.connector import Error
try:
  conn = mysql.connector.connect(host="localhost",user="root",password="admin",database="college")
  mycursor = conn.cursor(buffered=True)
  mycursor.execute("select * from student")
  #Retrieve all records
  recs = mycursor.fetchall()
  print(recs)
except Error as e:
  print(e)
finally:
  if conn.is_connected():
    mycursor.close()
    conn.close()

[('501', 'nani'), ('502', 'siva parvathi'), ('503', 'manasa')]