# CRUD Operation in MySQL using Python

Run both command in jupyter to install pymysql and mysqlclient

<https://dev.mysql.com/downloads/mysql/>

```python
#Installing MySQL on Python :
!pip install pymysql
!pip install mysqlclient
```

#### 1) Database Connection :

```python
import mysql.connector
# Establishing connection with the SQL  
  
dataBase = mysql.connector.connect( 
  host ="localhost", 
  user ="root", 
  password ="sahil123",
  auth_plugin='mysql_native_password',
  port=3306
) 
# Cursor to the database 
cursor = dataBase.cursor() 
  
cursor.execute("CREATE DATABASE Student") 
print("Student Data base is created") 
```

Student Data base is created

#### 2) Creating Database Table :

```python
import mysql.connector   
  
# connecting to the database  
dataBase = mysql.connector.connect( 
                     host = "localhost", 
                     user = "root", 
                     passwd = "sahil123", 
                     database = "Student" ) 

# preparing a cursor object  
cursorObject = dataBase.cursor()  
  
# creating table   
studentRecord = """CREATE TABLE NEWSTUDENT (  
                   NAME  VARCHAR(20) NOT NULL,  
                   ROLL INT NOT NULL
                   )"""
  
# table created 
cursorObject.execute(studentRecord)  
```

&#x20;

#### 3) INSERT Operation :

```python
sql = "INSERT INTO newstudent (NAME, ROLL) VALUES (%s, %s)"
val = ("Ram", "85") 
  
cursorObject.execute(sql, val) 
dataBase.commit() 
  
print(cursorObject.rowcount, "details inserted")
```

1 details inserted&#x20;

#### 4) INSERT Multiple Operations:

```python
sql = "INSERT INTO newstudent (NAME, ROLL) VALUES (%s, %s)"
val = [("Akash", "98"), 
       ("Neel", "23"), 
       ("Rohan", "43"), 
       ("Amit", "87"), 
       ("Anil", "45"),  
       ("Megha", "55"),  
       ("Sita", "95")] 
  
cursorObject.executemany(sql, val) 
dataBase.commit() 
  
print(cursorObject.rowcount, "details inserted") 
```

7 details inserted

#### 5) SELECT QUERY:

```python
print("Displaying NAME and ROLL columns from the STUDENT table:") 
  
# selecting query 
query = "SELECT NAME, ROLL FROM NEWSTUDENT"
cursorObject.execute(query) 
  
myresult = cursorObject.fetchall() 
  
for x in myresult: 
    print(x) 
```

&#x20;Displaying NAME and ROLL columns from the STUDENT table:&#x20;

('Ram', 85)&#x20;

('Ram', 85)&#x20;

('Akash', 98)&#x20;

('Neel', 23)&#x20;

('Rohan', 43)&#x20;

('Amit', 87)&#x20;

('Anil', 45)

('Megha', 55)&#x20;

('Sita', 95)&#x20;

#### 6) Update Operation :

```python
# drop clause 
statement ="UPDATE NEWSTUDENT SET ROLL = 23 WHERE Name ='Rohan'"
  
cursorObject.execute(statement) 
dataBase.commit() 


```

&#x20;

Example -2:

```python
# drop clause 
statement ="UPDATE NEWSTUDENT SET NAME = 'ROSHAN' WHERE Name ='Rohan'"
  
cursorObject.execute(statement) 
dataBase.commit() 


```

#### 7) DELETE OPERATION :

```python
query = "DELETE FROM NEWSTUDENT WHERE ROLL = 55"
cursorObject.execute(query) 
  
dataBase.commit() 

```

#### 8) DROP TABLE:

```python
statement = "Drop Table newstudent"
  
cursorObject.execute(statement)
  
dataBase.commit() 
dataBase.close()
```