Chpater -15(Mysql Interface with Python)
In order to connect to a database from within Python, we need a library named mysql connector.
Steps to create database connectivity
Step1: Start Python
Step2: Import the required packages
Step3:Open a connection to a database
Step4: Create a cursor instance
Step5: Execute a query
Step6: Extract data from result set
Step7: Clean up the environment
Database connectivity:- It refers to connection and communication between an application and
a database system.
Connection object:- A database connection object controls the connection to the database. It
represents a unique session with a database connected from within a script/program.
fetchall() method:- it will return all the rows from the result set in form of tuple containing the
records.
fetchone() method:- it will return only one row from the result set in form of tuple containing
the records.
fetchmany(n) method:- it will return only <n> rows from the result set in form of tuple
containing the records.
Result set:- It refers to logical set of records that are fetched from the database by executing a
query and made available to the application program.
Database Cursor:- It is a special control structure that facilitates the row by row processing of
records in the result set.
cursor.rowcount:- Returns how many rows have been retrieved through fetch method from the
cursor. It gives count of records in the result set.
import mysql.connector
z= mysql.connector.connect(user="root", password="user", host="localhost",database="mysql")
if z.is_connected():
print("suceesfully connected")
c=z.cursor()
#to fetch all the values
d=c.fetchall()
for row in d:
print(row)
count=c.rowcount
print("count=",count)
#to fetch two records
d=c.fetchmany(2)
for row in d:
print(row)
count=c.rowcount
print("count=",count)
#to fetch one record
d=c.fetchone()
for row in d:
print(row)
count=c.rowcount
print("count=",count)
# students having marks >=70 will be shown
st="select * from stm where marks>=%s" %(70)
c.execute(st)
d=c.fetchall()
for row in d:
print(row)
# students having marks >=70 and name=abc will be shown
st="select * from stm where marks>=%s and name='%s' "%(70, 'dfg')
c.execute(st)
d=c.fetchall()
for row in d:
print(row)
# update marks in table(from 90 to 99)
q="update stm set marks='99' where name='pop'"
d=c.execute(q)
z.commit()
print(c.rowcount,"records affected")
# students having marks >90 and name dfg will be shown
st="select * from stm where marks>{} and name='{}'".format(90,'dfg')
d=c.execute(st)
d=c.fetchall()
for row in d:
print(row)
#update marks in table(from 77 to 99)
st="update stm set marks= {} where marks={}".format(77,99)
d=c.execute(st)
z.commit()
print(c.rowcount,"records affected")
#insert one row in table
st="insert into stm(name, rollno, marks)values('{}',{},{})".format("nisha",17,94)
d=c.execute(st)
z.commit()
print(c.rowcount,"records affected")
# create table in Python
c.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
#insert single row in python
sql = "INSERT INTO customers2 (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
c.execute(sql, val)
print(c.rowcount,"records affected")
z.commit()
#insert multiple rows in Python
sql = "INSERT INTO customers2 (name, address) VALUES (%s, %s)"
val = [
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
]
c.executemany(sql, val)
z.commit()
