Data Insertion in Table
This “Insert Into” statement will be used for inserting data into table..
Single Value Insertion
import mysql.connector mycon = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur = mycon.cursor() cmd = "INSERT INTO students (name, address,roll_no) VALUES (%s, %s,%s)" value = ("Sonal", "Noida","34343434") mycur.execute(cmd, value) mycon.commit() print(mycur.rowcount,"record inserted")
Output
====== RESTART: C:/Users/ASUS/AppData/Local/Programs/Python/Python38-32/fgfd.py ===== 1 record inserted >>>
Program
import mysql.connector mycon=mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() cmd = "INSERT INTO students (name, address,mobile) VALUES (%s, %s,%s)" value =value= [ ('Rohan ', 'New Delhi',9876543212), ('Aditi', 'Lucknow',5643564444), ('Himanshu', 'Pune',7655656564), ('Vishal', 'Nainital',7654356445), ('Rajat', 'Mumbai',5644644456), ('Devansh', 'Kolkata',3456476545), ('Reema', 'Jaipur',653453455), ] mycur.executemany(cmd, value) mycon.commit() print(mycur.rowcount, "record inserted.")
Important : commit() function is used for making changes in databases.
Output
C:\Users\laptop>python demo_mysql_insert.py 7 record inserted.
You can also use a loop to insert multiple data in your database.
Value insertion using loop
import mysql.connector mycon = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur = mycon.cursor() cmd = "INSERT INTO students (name, address,roll_no) VALUES (%s, %s,%s)" loop=int(input("Set the Loop")) for x in range(loop): name=input("Name") add=input("Address") roll=input("Roll No") value = (name,add,roll) mycur.execute(cmd, value) mycon.commit() print(mycur.rowcount,"record inserted")
‘Select’ Statement
‘Select’ this keyword is used to extract data from databases with different conditions.
Program
import mysql.connector mycon=mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() mycur.execute("SELECT * FROM students") result = mycur.fetchall() for x in result: print(x)
Fetchall function is used extract all data from database
Output
C:\Users\laptop>python demo.py (1,'Rohan ', 'New Delhi',9876543212), (2,'Aditi', 'Lucknow',5643564444), (3,'Himanshu', 'Pune',7655656564), (4,'Vishal', 'Nainital',7654356445), (5,'Rajat', 'Mumbai',5644644456), (6,'Devansh', 'Kolkata',3456476545), (7,'Reema', 'Jaipur',653453455),
Selecting Column
We can select particular column for specific data fetch
import mysql.connector mycon= mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur = mycon.cursor() mycur.execute("SELECT roll_no, name FROM students") result = mycur.fetchall() for a in result: print(a)
Output
C:\Users\laptop>python demo.py ('987654', 'Rohan ') ('564356', 'Aditi') ('76556', 'Himanshu') ('765435', 'Vishal') ('564464', 'Rajat') ('345647', 'Devansh') ('65345', 'Reema')
Select with Filter
Select data where ‘address’ will be ‘pune’.
import mysql.connector mycon = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur = mycon.cursor() cmd = "SELECT * FROM students WHERE address ='pune'" mycur.execute(cmd) result = mycur.fetchall() for x in result: print(x)
Output
C:\Users\laptop>python demo.py 3,'Himanshu', 'Pune',7655656564),
Wildcard Characters
You can also use % to find the word from mid,last,start word/
import mysql.connector mycon = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur = mycon.cursor() mycur.execute("SELECT * FROM students WHERE name like 'Devansh'") result=mycur.fetchall() for x in result: print(x)
Output
C:\Users\laptop>python demo.py ('Devansh', 'Kolkata', '345647', 10)