Home Page class 12 @ Python

Python Interface with MySQL

student mysql connectivity programs
Student Data Base With MySQL using mysql.connector

Connecting python with mysql database
to create a database
to create a table within a database
insert the data into table
to insert data into a table by taking input for the details
insert multiple records in the table
to display only one record from a table
to display all records from a table
to display selected columns from the table
searching a record by roll no
searching a record by name
delete by name
update all records
update all records

Student Details connectivity with MySQL using pymysql
Bank Details connectivity with MySQL using pymysql

class 12 Interface python with SQL 2

Program:5

* To insert a record in an existing table by table input for the value of a fields of the table.

Sol:

host:localhost
user:root
password:ct (user defined or may be blank)
database:student1
table name: stud

#5
#to insert data into a table by taking input for the details 
import mysql.connector
from mysql.connector import Error
con=None
try:
    con = mysql.connector.connect(host='localhost',user='root',
password='ct',database='student1')
    if(con.is_connected()==True):
        print("connected")
    db=con.cursor()
        
    
    r=input("Enter rollno ")
    n=input("Enter name ")
    m11=input("Enter marks for subject 1 ")
    m12=input("Enter marks for subject 2 ")
    m13=input("Enter marks for subject 3 ")
    m14=input("Enter marks for subject 4 ")
    m15=input("Enter marks for subject 5 ")
    db.execute("insert into stud (rollno,name,M1,M2,M3,M4,M5) values (%s,%s,%s,%s,%s,%s,%s)",(r,n,m11,m12,m13,m14,m15))
     
    #to save the data
    con.commit()
    print(db.rowcount, "record inserted.")
    
except Error as e:
        print(e)

finally:
        if con is not None and con.is_connected():
            con.close()
            print("Connection closed")

Program:6

* To insert a record in an existing table.
* Insertin multiple record at a time

Sol:

host:localhost
user:root
password:ct (user defined or may be blank)
database:student1
table name: stud

#6
#to insert data into a table
#insert multiple records in the table

import mysql.connector
from mysql.connector import Error
con=None
try:
    con = mysql.connector.connect(host='localhost',user='root',
password='ct',database='student1')
    if(con.is_connected()==True):
        print("connected")
    db=con.cursor()
        
    
    sql="insert into stud (rollno,name,M1,M2,M3,M4,M5) values (%s,%s,%s,%s,%s,%s,%s)"
    val=[("3","Anita Sharma","80","74","75","62","89"),
         ("4","Bhavya Verma","81","55","67","90","76"),
         ("5","Gauri Pathak","65","78","75","89","69"),
         ("6","Shruti Goyal","76","70","88","90","98"),
         ("7","Vijay Kumar","86","89","90","65","76")]
    
    db.executemany(sql,val)
   
     
    #to save the data
    con.commit()
    print(db.rowcount, "record inserted.")
    
except Error as e:
        print(e)

finally:
        if con is not None and con.is_connected():
            con.close()
            print("Connection closed")

Program:7

* To fetch data from a table
* To fetch only one record using fetchone()


Sol:

host:localhost
user:root
password:ct (user defined or may be blank)
database:student1
table name: stud

#7
#to display only one record from a table
#select command
import mysql.connector
from mysql.connector import Error
con=None
try:
    con = mysql.connector.connect(host='localhost',user='root',
password='ct',database='student1')
    if(con.is_connected()==True):
        print("connected")
    db=con.cursor()
    sql="select * from stud;"
    db.execute(sql)
    res = db.fetchone()
    for x in res:
        print(x)
except Error as e:
        print(e)
finally:
        if con is not None and con.is_connected():
            con.close()
            print("Connection closed")