python-tutorials-database-operations-using-python-mysql
Database Connection
In this section of the tutorial, we will discuss the steps to connect the python application to the database.
There are the following steps to connect a python application to our database.
- Import mysql.connector module
- Create the connection object.
- Create the cursor object
- Execute the query
- Creating the connection To create a connection between the MySQL database and the python application, the connect() method of mysql.connector module is used.
Pass the database details like HostName, username, and the database password in the method call. The method returns the connection object.
The syntax to use the connect() is given below.
Connection-Object= mysql.connector.connect(host = <host-name> , user = <username> , passwd = <password> )
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")
#printing the connection object
print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7fb142edd780>
Here, we must notice that we can specify the database name in the connect() method if we want to connect to a specific database.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")
#printing the connection object
print(myconn)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7ff64aa3d7b8>
Creating a cursor object
The cursor object can be defined as an abstraction specified in the Python DB-API 2.0. It facilitates us to have multiple separate working environments through the same connection to the database. We can create the cursor object by calling the 'cursor' function of the connection object. The cursor object is an important aspect of executing queries to the databases.
The syntax to create the cursor object is given below.
<my_cur> = conn.cursor()
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google", database = "mydb")
#printing the connection object
print(myconn)
#creating the cursor object
cur = myconn.cursor()
print(cur)
Output:
<mysql.connector.connection.MySQLConnection object at 0x7faa17a15748>
MySQLCursor: (Nothing executed yet)
Creating the table
In this section of the tutorial, we will create the new table Employee. We have to mention the database name while establishing the connection object.
We can create the new table by using the CREATE TABLE statement of SQL. In our database PythonDB, the table Employee will have the four columns, i.e., name, id, salary, and department_id initially.
The following query is used to create the new table Employee.
> create table Employee (name varchar(20) not null, id int primary key, salary float not null, Dept_Id int not null)
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Creating a table with name Employee having four columns i.e., name, id, salary, and department id
dbs = cur.execute("create table Employee(name varchar(20) not null, id int(20) not null primary key, salary float not null, Dept_id int not null)")
except:
myconn.rollback()
myconn.close()
Alter Table
Sometimes, we may forget to create some columns, or we may need to update the table schema. The alter statement used to alter the table schema if required. Here, we will add the column branch_name to the table Employee. The following SQL query is used for this purpose.
alter table Employee add branch_name varchar(20) not null
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#adding a column branch name to the table Employee
cur.execute("alter table Employee add branch_name varchar(20) not null")
except:
myconn.rollback()
myconn.close()
Creating new databases
In this section of the tutorial, we will create the new database PythonDB.
Getting the list of existing databases
We can get the list of all the databases by using the following MySQL query.
> show databases;
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")
#creating the cursor object
cur = myconn.cursor()
try:
dbs = cur.execute("show databases")
except:
myconn.rollback()
for x in cur:
print(x)
myconn.close()
Output:
('EmployeeDB',)
('Test',)
('TestDB',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mysql',)
('performance_schema',)
('testDB',)
Creating the new database
The new database can be created by using the following SQL query.
create database
Example
import mysql.connector
*Create the connection object *
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google")
#creating the cursor object
cur = myconn.cursor()
try:
#creating a new database
cur.execute("create database PythonDB2")
#getting the list of all the databases which will now include the new database PythonDB
dbs = cur.execute("show databases")
except:
myconn.rollback()
for x in cur:
print(x)
myconn.close()
Output:
('EmployeeDB',)
('PythonDB',)
('Test',)
('TestDB',)
('anshika',)
('information_schema',)
('javatpoint',)
('javatpoint1',)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)
Insert Operation
Adding a record to the table
The INSERT INTO statement is used to add a record to the table. In python, we can mention the format specifier (%s) in place of values.
We provide the actual values in the form of tuple in the execute() method of the cursor.
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"
#The row values are provided in the form of tuple
val = ("John", 110, 25000.00, 201, "Newyork")
try:
#inserting the values into the table
cur.execute(sql,val)
#commit the transaction
myconn.commit()
except:
myconn.rollback()
print(cur.rowcount,"record inserted!")
myconn.close()
Output:
1 record inserted!
Insert multiple rows
We can also insert multiple rows at once using the python script. The multiple rows are mentioned as the list of various tuples.
Each element of the list is treated as one particular row, whereas each element of the tuple is treated as one particular column value (attribute).
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),("Nick",104,90000.00,201,"Newyork")]
try:
#inserting the values into the table
cur.executemany(sql,val)
#commit the transaction
myconn.commit()
print(cur.rowcount,"records inserted!")
except:
myconn.rollback()
myconn.close()
Output:
3 records inserted!
Row ID
In SQL, a particular row is represented by an insertion id which is known as row id. We can get the last inserted row id by using the attribute lastrowid of the cursor object.
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (%s, %s, %s, %s, %s)"
val = ("Mike",105,28000,202,"Guyana")
try:
#inserting the values into the table
cur.execute(sql,val)
#commit the transaction
myconn.commit()
#getting rowid
print(cur.rowcount,"record inserted! id:",cur.lastrowid)
except:
myconn.rollback()
myconn.close()
Output:
1 record inserted! Id: 0
Read Operation
The SELECT statement is used to read the values from the databases. We can restrict the output of a select query by using various clause in SQL like where, limit, etc.
Python provides the fetchall() method returns the data stored inside the table in the form of rows. We can iterate the result to get the individual rows.
In this section of the tutorial, we will extract the data from the database by using the python script. We will also format the output to print it on the console.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select * from Employee")
#fetching the rows from the cursor object
result = cur.fetchall()
#printing the result
for x in result:
print(x);
except:
myconn.rollback()
myconn.close()
Output:
('John', 101, 25000.0, 201, 'Newyork')
('John', 102, 25000.0, 201, 'Newyork')
('David', 103, 25000.0, 202, 'Port of spain')
('Nick', 104, 90000.0, 201, 'Newyork')
('Mike', 105, 28000.0, 202, 'Guyana')
Reading specific columns
We can read the specific columns by mentioning their names instead of using star (*).
In the following example, we will read the name, id, and salary from the Employee table and print it on the console.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee")
#fetching the rows from the cursor object
result = cur.fetchall()
#printing the result
for x in result:
print(x);
except:
myconn.rollback()
myconn.close()
Output:
('John', 101, 25000.0)
('John', 102, 25000.0)
('David', 103, 25000.0)
('Nick', 104, 90000.0)
('Mike', 105, 28000.0)
The fetchone() method
The fetchone() method is used to fetch only one row from the table. The fetchone() method returns the next row of the result-set.
Consider the following example.
Example
import mysql.connector
**Create the connection object **
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee")
#fetching the first row from the cursor object
result = cur.fetchone()
#printing the result
print(result)
except:
myconn.rollback()
myconn.close()
Output:
('John', 101, 25000.0)
Formatting the result
We can format the result by iterating over the result produced by the fetchall() or fetchone() method of cursor object since the result exists as the tuple object which is not readable.
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee")
#fetching the rows from the cursor object
result = cur.fetchall()
print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()
myconn.close()
Output:
Name id Salary
John 101 25000
John 102 25000
David 103 25000
Nick 104 90000
Mike 105 28000
Using where clause
We can restrict the result produced by the select statement by using the where clause. This will extract only those columns which satisfy the where condition.
Consider the following example.
Example: printing the names that start with j
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee where name like 'J%'")
#fetching the rows from the cursor object
result = cur.fetchall()
print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()
myconn.close()
Output:
Name id Salary
John 101 25000
John 102 25000
Example: printing the names with id = 101, 102, and 103
import mysql.connector
Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee where id in (101,102,103)")
#fetching the rows from the cursor object
result = cur.fetchall()
print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()
myconn.close()
Output:
Name id Salary
John 101 25000
John 102 25000
David 103 2500
Ordering the result
The ORDER BY clause is used to order the result. Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee order by name")
#fetching the rows from the cursor object
result = cur.fetchall()
print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()
myconn.close()
Output:
Name id Salary
David 103 25000
John 101 25000
John 102 25000
Mike 105 28000
Nick 104 90000
Order by DESC
This orders the result in the decreasing order of a particular column.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Reading the Employee data
cur.execute("select name, id, salary from Employee order by name desc")
#fetching the rows from the cursor object
result = cur.fetchall()
#printing the result
print("Name id Salary");
for row in result:
print("%s %d %d"%(row[0],row[1],row[2]))
except:
myconn.rollback()
myconn.close()
Output:
Name id Salary
Nick 104 90000
Mike 105 28000
John 101 25000
John 102 25000
David 103 25000
Update Operation
The UPDATE-SET statement is used to update any column inside the table. The following SQL query is used to update a column.
> update Employee set name = 'alex' where id = 110
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#updating the name of the employee whose id is 110
cur.execute("update Employee set name = 'alex' where id = 110")
myconn.commit()
except:
myconn.rollback()
myconn.close()
Delete Operation
The DELETE FROM statement is used to delete a specific record from the table. Here, we must impose a condition using WHERE clause otherwise all the records from the table will be removed.
The following SQL query is used to delete the employee detail whose id is 110 from the table.
> delete from Employee where id = 110
Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#Deleting the employee details whose id is 110
cur.execute("delete from Employee where id = 110")
myconn.commit()
except:
myconn.rollback()
myconn.close()
Join Operation
We can combine the columns from two or more tables by using some common column among them by using the join statement.
We have only one table in our database, let's create one more table Departments with two columns department_id and department_name.
create table Departments (Dept_id int(20) primary key not null, Dept_Name varchar(20) not null);
As we have created a new table Departments as shown in the above image. However, we haven't yet inserted any value inside it.
Let's insert some Departments ids and departments names so that we can map this to our Employee table.
insert into Departments values (201, "CS");
insert into Departments values (202, "IT");
Let's look at the values inserted in each of the tables. Consider the following image.
Now, let's create a python script that joins the two tables on the common column, i.e., dept_id.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#joining the two tables on departments_id
cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments join Employee on Departments.Dept_id = Employee.Dept_id")
print("ID Name Salary Dept_Id Dept_Name")
for row in cur:
print("%d %s %d %d %s"%(row[0], row[1],row[2],row[3],row[4]))
except:
myconn.rollback()
myconn.close()
Output:
ID Name Salary Dept_Id Dept_Name
101 John 25000 201 CS
102 John 25000 201 CS
103 David 25000 202 IT
104 Nick 90000 201 CS
105 Mike 28000 202 IT
Right Join
Right join shows all the columns of the right-hand side table as we have two tables in the database PythonDB, i.e., Departments and Employee. We do not have any Employee in the table who is not working for any department (Employee for which department id is null). However, to understand the concept of right join let's create the one.
Execute the following query on the MySQL server.
insert into Employee(name, id, salary, branch_name) values ("Alex",108,29900,"Mumbai");
This will insert an employee Alex who doesn't work for any department (department id is null).
Now, we have an employee in the Employee table whose department id is not present in the Departments table. Let's perform the right join on the two tables now.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#joining the two tables on departments_id
result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments right join Employee on Departments.Dept_id = Employee.Dept_id")
print("ID Name Salary Dept_Id Dept_Name")
for row in cur:
print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4])
except:
myconn.rollback()
myconn.close()
Output:
ID Name Salary Dept_Id Dept_Name
101 John 25000.0 201 CS
102 John 25000.0 201 CS
103 David 25000.0 202 IT
104 Nick 90000.0 201 CS
105 Mike 28000.0 202 IT
108 Alex 29900.0 None None
Left Join
The left join covers all the data from the left-hand side table. It has just opposite effect to the right join. Consider the following example.
Example
import mysql.connector
#Create the connection object
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")
#creating the cursor object
cur = myconn.cursor()
try:
#joining the two tables on departments_id
result = cur.execute("select Employee.id, Employee.name, Employee.salary, Departments.Dept_id, Departments.Dept_Name from Departments left join Employee on Departments.Dept_id = Employee.Dept_id")
print("ID Name Salary Dept_Id Dept_Name")
for row in cur:
print(row[0]," ", row[1]," ",row[2]," ",row[3]," ",row[4])
except:
myconn.rollback()
myconn.close()
Output:
ID Name Salary Dept_Id Dept_Name
101 John 25000.0 201 CS
102 John 25000.0 201 CS
103 David 25000.0 202 IT
104 Nick 90000.0 201 CS
105 Mike 28000.0 202 IT
Top comments (0)