How to connect Database with Python

Introduction

Database connectivity is one of the most important aspects of any programming language. Since we have to deal with huge bulk of data ,we cannot store it in files or folders. So we need a database to manage the data.

CONNECTION ESTABLISHMENTS

For connecting frontend of python code with the backend of mysql database, we require a python library named mysql-connector-python.

**This library is not inbuild we have to install it.**

For installing this python library we have to use a command in cmd terminal:

				
					pip install mysql-connector-python

PROCEDURE

After installing the library we can import it by

				
					import mysql.connector

After installing the library we can import it by :

				
					con = mysql.connector.connect(user=”root” ,
password=” ”; , host=”localhost“)

The above command will connect python code with the backend of the mysql database.We can set the password and hostname of the database while installing the database.

Creating database

After setting up the connection by con class ,we create an object of the class. Let say name it mycursor.

				
					mycursor = con.cursor()

For creating the database

				
					mycursor.execute(“create database sunpharma”)
mycursor.execute(“use sunpharma “)

The above command will create a database named sunpharma.

FOR CREATING TABLE

For creating the table :-

				
					mycursor.execute(“create table sun ( id int 
primary key,
name varchar(30),
salary int,
city varchar(30))”)
print(“created successfully”)

The above command will create a table named sun.

Primary key is the key which gives unique identity to the column.

INSERTING THE DATA

For inserting data into the created sun table:

				
					mycursor.execute(“insert into sun values
(2,’atul’,10000,’chandigarh’)”)
con.commit()
print(“created successfully”)

For deleting any row of the table

				
					mycursor.execute(“delete from sun where 
city = ‘pune‘ &quot;)
con.commit()
print(“deleted successfully”)

For inserting ,deleting and updating we have to use commit( ) function with our class .

Without that it will just execute but will not show on the database.

UPDATING AND DELETING

For updating the data of any row of table:-

				
					mycursor.execute(“update sun  set 
name = ‘ankit’ where name = ‘ajay‘ “)
con.commit()
print(“updated successfully”)

MySQL Connection Management in Python

Before proceeding with the next command in your Python script, it’s essential to comment out the previous command using `#`.

After completing operations with MySQL in your IDE frontend, remember to close the MySQL cursor using:

				
					mycursor.close()

This step ensures that database connectivity is not restricted to the current page only. Always ensure that your MySQL database remains open at the backend for continuous functionality.