# 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()
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://gyansetu-python.gitbook.io/python-programming/installing-mysql-on-python.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.