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‘ ")
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.