MySQL Python/Connector is an interface for connecting to a MySQL database server from Python. It implements the Python Database API and is built on top of MySQL.

For installing MySQL-connector-python first you have to open your terminal and execute the following code

pip install mysql-connector-python

After installation, you have to import that package into your code and define your database name and other entities using connect().

You can establish a connection using the connect() constructor. This accepts the username, password, host and, name of the database you need to connect with (optional) and, returns an object of the MySQLConnection class.

The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='db_name')mycursor=mydb.cursor()

mycursor.execute() is used to execute the given SQL query

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='db_name')mycursor=mydb.cursor()
mycursor.execute('create database employedb')
import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()
mycursor.execute('create table employee(id int not null auto_increment,name varchar(20),designation varchar(20),primary key(id))')

For inserting we can insert a single element or we can insert many elements using insert executemany() give the values as a list of tuples and we have to use mydb.commit() after every data entry in the database

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()query = 'insert into employee(id,name,designation) values( %s, %s, %s)'value = (1, 'tom', 'admin')values = [(2,'sam','devops'),(3,'ram','sales'),(4,'jon','marketing')]mycursor.execute(query,value)mycursor.executemany(query,values)

For viewing our table we can directly iterate from the cursor or we can use the fetch keyword. There are three methods to use it

  1. fetchone() → returns the first row
  2. fetchall() → returns all the column
  3. fetchmany(size=) → returns rows according to the given size
import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()
mycursor.execute(select * from employee)
data1 = mycursor.fetchone()
print(data1)
(1, 'tom', 'admin')
data2 = mycursor.fetchmany(size = 3)
print(data2)
[(1, 'tom', 'admin'), (2, 'sam', 'devops'), (3, 'ram', 'sales')]
data3 = mycursor.fetchall()
print(data3)
[(1, 'tom', 'admin'), (2, 'sam', 'devops'), (3, 'ram', 'sales'), (4, 'jon', 'marketing')]

Let us perform join operations using the python and mysql

Before going to joins let us take two tables where we are gonna perform all the joints

Table: employee
Table: sales

inner join

Returns records that have matching values in both tables

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()mycursor.execute('select sales.total_sales,sales.city from employee inner join sales on employee.id = sales.id')
data = mycursor.fetchall()
print(data)
[(3000, 'ny'), (2000, 'nj'), (2000, 'ny'), (2500, 'bos'), (3500, 'ny')]

Returns all records from the left table, and the matched records from the right table

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()mycursor.execute('select sales.total_sales,sales.city,employee.name from employee left join sales on employee.id = sales.id')
data = mycursor.fetchall()
print(data)
[(3000, 'ny', 'tom'), (2000, 'nj', 'sam'), (2000, 'ny', 'rob'), (2500, 'bos', 'ram'), (3500, 'ny', 'jon')]

Returns all records from the right table, and the matched records from the left table

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()mycursor.execute('select sales.total_sales,sales.city,employee.name from employee right join sales on employee.id = sales.id')
data = mycursor.fetchall()
print(data)
[(3000, 'ny', 'tom'), (2000, 'nj', 'sam'), (2000, 'ny', 'rob'), (2500, 'bos', 'ram'), (3500, 'ny', 'jon'), (4500, 'bos', None)]

A self-join is a regular join, but the table is joined with itself.

import mysql.connectormydb=mysql.connector.connect(host='localhost',user='root',password='password',database='employedb')mycursor=mydb.cursor()
mycursor.execute('select e1.id,e1.name,e1.teamleadId,e2.name as leadname from employee e1 join employee e2 on e1.teamleadId = e2.id ')
data = mycursor.fetchall()
print(data)
[(1, 'tom', 3, 'rob'), (2, 'sam', 2, 'sam'), (3, 'rob', 3, 'rob'), (5, 'jon', 3, 'rob')]

Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store