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

« Prev 1 2 3 4 5 6 7 Next »

Python MySQL Interface

Student MySQL connectivity programs

Program:1

To test connection with MYSQL using mysql.connector
Sol:

host:localhost
user:root
password:ct (user defined or may be blank)

#1 
#Connecting python with mysql database
#to check the connection
import mysql.connector
from mysql.connector import Error
con=None
try:
    con = mysql.connector.connect(host='localhost',user='root',password='ct')
    if(con.is_connected()==True):
        print("connected to MYSQL Database")

except Error as e:
        print(e)

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

Program:2

* To test connection with MYSQL using mysql.connector
* To create a database

Sol:

host:localhost
user:root
password:ct (user defined or may be blank)

#2
#to create a database
import mysql.connector
from mysql.connector import Error
con=None
try:
    con = mysql.connector.connect(host='localhost',user='root',password='ct')
    if(con.is_connected()==True):
        print("connected to MYSQL Database")
        db=con.cursor()
        q1="create database student1;"
        db.execute(q1)
        print("student1 database created")
        con.close()
except Error as e:
        print(e)

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

Program:3

* To create a table in an existing database

Sol:

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

#3
#to create a table within a database
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")
    mycursor=con.cursor()
    q1="create table stud ( rollno int, name char(15), M1 float, M2 float, M3 float, 
M4 float, M5 float, total float, per float);"
    mycursor.execute(q1)
    print("stud table created")
except Error as e:
        print(e)

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

Program:4

* To insert a record in an existing table

Sol:

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

#4
#to create a table within a database
#and  insert the data
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='1'
    n='Aman Sharma'
    m11='80'
    m12='70'
    m13='75'
    m14='66'
    m15='88'
    
    #running
    #1st method
    #sql="insert into stud (rollno,name,M1,M2,M3,M4,M5) values (%s,%s,%s,%s,%s,%s,%s)"
    #val=("1","Aman Sharma","80","70","75","66","88")
    #db.execute(sql,val)
    
    #2nd method
    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("Record Saved: 1 Record Inserted")
except Error as e:
        print(e)

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