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)

Learn Python

MySql

Django

Data Insertion in Table