Integrate SQL with Python by importing the MySQL module

Programming steps (Reference):

1. Operating system: windows 64 bit
2. Python: 64 bit
3. Mysql:  64 bit           # open parallely cmd prompt, python window &  mysql prompt

4. open cmd prompt
    pip install mysql-connector   Enter
    pip install mysqlclient              Enter

5. python interactive mode
   >> import mysql.connector     Enter
 Ans: >>


6.   open mysql cmd prompt
     Enter password: root
     select current_user;

7. open python script mode
     import mysql.connector
     mydb=mysql.connector.connect(host="localhost",user="root",passwd="root")
     print(mydb)

8.  open mysql cmd prompt
      show databases;
      drop database school;   ( if existing)
      show databases;

9. open python script mode   # creating school database logic
    import mysql.connector
    mydb=mysql.connector.connect(host="localhost",user="root",passwd="root")

    mycursor=mydb.cursor();
    mycursor.execute("create database school ")

10.  open mysql cmd prompt     # printing database mysql python
       use school;
      show databases; 

11. open python script mode      # printing database through python
    import mysql.connector
    mydb=mysql.connector.connect(host="localhost",user="root".passwd="root")

    mycursor=mydb.cursor();
    mycursor.execute("show databases")
    for x in mycursor:
           print(x)

 13.open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("CREATE TABLE student1920(Roll int(2) Primary key, Name char(20), class int(2), DOB Date, Gender Char(1), City Char(10), Marks int(3))")

14.  open mysql cmd prompt
    use school;
    desc student1920;

15. open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("show tables")
for x in mycursor:
           print(x)

16. open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
sql="insert into student1920(Roll,Name,class,DOB,Gender,City,Marks) values (%s,%s,%s,%s,%s,%s,%s) "
val=(01,"Nanda",10,1995/06/06,"M","Agra",551)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount,"1 Record inserted")

17.  open python script mode   #Repeat all the steps remaining rows change the data
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
sql="insert into student1920(Roll,Name,class,DOB,Gender,City,Marks) values (%s,%s,%s,%s,%s,%s,%s) "
val=(02,"Saurabh",12,1993/05/07,"M","Mumbai",462)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount,"1 Record inserted")

.
.
18.open mysql cmd prompt
  select * from student1920;

19.open python script mode
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("select * from student")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)



# questions
1. SELECT COUNT(*), CITY FROM STUDENT1920 GROUP BY CITY HAVING COUNT(*)>1;

#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute(" SELECT COUNT(*), CITY FROM STUDENT1920 GROUP BY CITY HAVING COUNT(*)>1")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)

2. SLECT MAX(DOB), MIN(DOB) FROM STUDENT1920;

#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("SLECT MAX(DOB), MIN(DOB) FROM STUDENT1920;")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)


3. SELECT NAME,GENDER FROM STUDENT1920 WHERE CITY=''DELHI";
#open python script mode

import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root".passwd="root",database="school")

mycursor=mydb.cursor();
mycursor.execute("SELECT NAME,GENDER FROM STUDENT1920 WHERE CITY=''DELHI"")
myresult=mycursor.fetchall()
for x in myresult:
     print(x)




                                                                 ***End***