Python-MySQL Connectivity
******************************************
Python 3.6 and MySQL Server 5.1 Connectivity
*******************************************
STEP 1 : First let us find the directory where Python is installed.
******************************************************
>>> import sys
>>> sys.path
['', 'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\Lib\\idlelib',
'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\python38.zip',
'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\DLLs',
'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\lib',
'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32',
'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\lib\\site-packages']
>>>
The output shows that Python Interpreter is installed in the folder located at path (location) :
C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32
*************************************************************************
STEP 2 : Let us open command prompt and proceed to the Scripts Sub Folder in Python36-32 Directory.
*************************************************************************
Microsoft Windows [Version 10.0.19043.1826]
(c) Microsoft Corporation. All rights reserved.
C:\Users\Hari>cd appdata
C:\Users\Hari\AppData>cd local
C:\Users\Hari\AppData\Local>cd programs
C:\Users\Hari\AppData\Local\Programs>cd python
C:\Users\Hari\AppData\Local\Programs\Python>cd python36-32
C:\Users\Hari\AppData\Local\Programs\Python\Python36-32>cd scripts
*************************************************************
STEP 3 : Let us install mysql-connector package inside the Scripts Folder.
**************************************************************
C:\Users\Hari\AppData\Local\Programs\Python\Python36-32\Scripts>pip install mysql-connector
Collecting mysql-connector
Downloading mysql-connector-2.2.9.tar.gz (11.9 MB)
|████████████████████████████████| 11.9 MB 3.2 MB/s
Using legacy setup.py install for mysql-connector, since package 'wheel' is not installed.
Installing collected packages: mysql-connector
Running setup.py install for mysql-connector ... done
Successfully installed mysql-connector-2.2.9
WARNING: You are using pip version 20.1.1; however, version 22.2 is available.
You should consider upgrading via the 'c:\users\hari\appdata\local\programs\python\python38-32\python.exe -m pip install --upgrade pip' command.
*******************************************************
STEP 4 : Let us upgrade pip tool ( This step is optional)
********************************************************
C:\Users\Hari\AppData\Local\Programs\Python\Python36-32\Scripts>cd..
C:\Users\Hari\AppData\Local\Programs\Python\Python36-32>python -m pip install --upgrade pip
Collecting pip
Downloading pip-22.2-py3-none-any.whl (2.0 MB)
|████████████████████████████████| 2.0 MB 2.2 MB/s
Installing collected packages: pip
Attempting uninstall: pip
Found existing installation: pip 20.1.1
Uninstalling pip-20.1.1:
Successfully uninstalled pip-20.1.1
Successfully installed pip-22.2
C:\Users\Hari\AppData\Local\Programs\Python\Python36-32>
************************************************************************
STEP 5 : Let us open Python IDLE and try to import connector. If we are not getting any error then it has established connectivity successfully.
**************************************************************************
>>> import mysql.connector
************************************
Some Python Programs on Connectivity
***********************************
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="root") # database="school" change password as per your need
if mycon.is_connected():
print("sucessfully connected to mysql database")
......................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root")
mycursor=mydb.cursor()
mycursor.execute("SHOW DATABASES")
#value are printed into TUPLE form BCOZ each element seprate with ,
for x in mycursor:
print(x)
.....................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="School")
mycursor=mydb.cursor()
mycursor.execute("Select * from Student where rollno=110")
myresult=mycursor.fetchall()
for x in myresult:
print(x)
...................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="School")
mycursor=mydb.cursor()
sql=("INSERT INTO student(rollno,name,age,city,address)values(%s,%s,%s,%s,%s)")
val=(110,'Pankaj',22,'Lucknow','Nishatganj')
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount, "Record Inserted Successfully")
...............................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="School")
mycursor=mydb.cursor()
sql=("INSERT INTO student(rollno,name,age,city,address)values(%s,%s,%s,%s,%s)")
val=(110,'Pankaj',22,'Lucknow','Nishatganj')
mycursor.execute(sql,val)
mydb.commit()
mycursor.execute("select * from student")
for i in mycursor:
print(i)
#print(mycursor.rowcount, "Record Inserted Successfully")
...............................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="School")
mycursor=mydb.cursor()
rollno=int(input("Enter RollNo"))
name=input("Enter name")
age=int(input("Enter Age"))
city=input("Enter City")
address=input("Enter Address")
sql=("INSERT INTO student(rollno,name,age,city,address)values(%s,%s,%s,%s,%s)")
val=(rollno,name,age,city,address)
mycursor.execute(sql,val)
mydb.commit()
mycursor.execute("select * from student")
for i in mycursor:
print(i)
........................
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="root",database="school")
if mycon.is_connected()==False:
print("Error connecting to MySql database")
cursor=mycon.cursor()
cursor.execute("select * from student")
data=cursor.fetchone()
count=cursor.rowcount
print(data)
print("Total number of rows retrived so far from resultset:",count)
data=cursor.fetchone()
count=cursor.rowcount
print(data)
print("Total number of rows retrived so far from resultset:",count)
data=cursor.fetchmany(2)
count=cursor.rowcount
for row in data:
print(row)
print("Total number of rows retrived so far from resultset:",count)
.............................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="school")
mycursor=mydb.cursor()
mycursor.execute("DESC STUDENT")
for i in mycursor:
print(i)
.......................................
import mysql.connector as sqltor
mycon=sqltor.connect(host="localhost",user="root",passwd="root",database="school")
mycursor=mycon.cursor()
nm=input("enter name of the student whose record is to be deleted")
try:
mycursor.execute("delete from student where name='nm'")
print(mycursor.rowcount,"record(s) deleted")
mycon.commit()
except:
mycon.rollback()
mycon.close()
..........................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root")
mycursor=mydb.cursor()
mycursor.execute("CREATE DATABASE School")
...........................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="school")
mycursor=mydb.cursor()
mycursor.execute("CREATE TABLE student (Roll INT(4) Primary Key,Name VARCHAR(20),AGE INT(2),CITY CHAR(10))")
..........................................
import mysql.connector as sq
mydb=sq.connect(host="localhost",user="root",passwd="root",database="school")
mycursor=mydb.cursor()
mycursor.execute("Alter Table Student add Address char(20)")
