LESSON 14
(INTERFACE PYTHON WITH MYSQL)
PYTHON SQL CONNECTIVITY
1 - First of all install pip in script path
ex-: pip install my sql-connector (Internet connection is needed)
(it will be install automatically)
2) Now check it is installed or not in python
Ex-
>> import [Link]
3) for working on sql we must know some information like host name,
database name,password and user.
( for getting host and user type select current_user; on sql)
CREATING CONNECTION WITH SQL:
1) Go on script mode of python and type
python sql connectivity
CREATING CONNECTION WITH SQL:
1) Go on script mode of python and type
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345")
print(ob)
output-:
<[Link] connection object at
0x0000007cBBA900f0>
(connection done)
WORKING ON SQL FROM PYTHON:
1) CREATING DATABASE FROM PYTHON
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345")
mycursor=[Link]()
[Link](“create database little”)
2) SHOW DATABASES LIST FROM SQL ON PYTHON
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345")
mycursor=[Link]()
[Link](“show databases”)
for i in mycursor:
print(i)
3) CREATING TABLE :-
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345",
database=”LFCS”)
mycursor=[Link]()
[Link](“create table student (AdNo integer(3) primary key, NAME
char(6)),AGE integer,FEE integer”)
4) ALTER TABLE
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345
",database=”rr1”)
mycursor=[Link]()
[Link](“alter table student add( address char(10))”)
5) DISPLAY TABLE STRUCTURE:
[Link](“desc student”)
for i in mycursor:
print(i)
6) INSERT COMMAND IN PYTHON:
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345
",database="rr1")
mycursor=[Link]()
sql="insert into student(name,fee,address)values(%s,%s,%s)"
val=(“mohan”,1000,”lfc”)
[Link](sql,val)
[Link]()
print([Link],"element inserted")
7) How to use select query
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345",data
base="rr1")
mycursor=[Link]()
[Link]("select * from student")
myresult=[Link]()
for i in myresult:
print(i)
fetchone()- This function is used to display only one row at one time
fetchall()-: it is used to display all row at one time.
Fetchmany()-: This function is used to fetch more than one record from a
resultset.
rowcount-: It is a property that count number of row retrieved in query.
Commit()-: It is a function that accept changes made in a query on python.
8)UPDATE COMMAND
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345",d
atabase="rr1")
mycursor=[Link]()
[Link]("update student set roll=101 where roll=100")
[Link]()
9)DELETING RECORD
import [Link]
ob=[Link](host="localhost",user="root",passwd="12345",d
atabase="rr1")
mycursor=[Link]()
[Link]("delete from student where roll=100")
[Link]()