MySQL with Python Connectivity Notes
import mysql.connector as m
db = m.connect(host=”localhost”,user=”root”,passwd=”1234″) # Open database connection
cursor = db.cursor() # prepare a cursor object using cursor() method
cursor.execute(“show databases”) # execute SQL query using execute() method.
data = cursor.fetchone() # Fetch a single row using fetchone() method.
print (“Database version : %s ” % data)
db.close() # disconnect from server
Creating Database Table- Once a database connection is established, we are ready to create tables or records into the database tables using execute method of the created cursor.
Example
To create Database table EMPLOYEE −
import mysql.connector as m
db = m.connect(host=”localhost”,user=”testuser”,passwd=”test123″,database=”TESTDB”)
cursor = db.cursor()
cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) # Drop table if it already exist using execute() method.
sql = “””CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )””” cursor.execute(sql)
db.close()
INSERT Operation-to create records into a database table.
Example– INSERT statement to create a record into EMPLOYEE table −
import mysql.connector as m
db = m.connect(“localhost”,”testuser”,”test123″,”TESTDB” )
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = “””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (‘Mac’, ‘Mohan’, 20, ‘M’, 2000)”””
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
OR
import mysql.connector as m
db = m.connect(“localhost”,”testuser”,”test123″,”TESTDB” )
cursor = db.cursor()
sql = “INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES (‘%s’, ‘%s’, ‘%d’, ‘%c’, ‘%d’ )” % \ (‘Mac’, ‘Mohan’, 20, ‘M’, 2000) try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close() ——————————————————–
To fetch some useful information from the database,
can use either fetchone() method to fetch single record or
fetchall() method to fetch multiple values from a database table.
fetchone() − It fetches the next row of a query result set.
A result set is an object that is returned when a cursor object is used to query a table.
fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.
Example
Q:-All the records from EMPLOYEE table having salary more than 1000 −
sol
import mysql.connector as m
db = m.connect(“localhost”,”testuser”,”test123″,”TESTDB” )
cursor = db.cursor()
sql = “SELECT * FROM EMPLOYEE WHERE INCOME > 1000 ”
try:
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
print(“fname=%s,lname=%s,age=%d,sex=%s,income=%d”%(fname, lname, age, sex, income )
except:
print (“Error: unable to fetch data”)
db.close()
OUTPUT − fname=Mac, lname=Mohan, age=20, sex=M, income=2000
Update Operation-to update one or more records, which are already available in the database.
Example–
Q:-To updates all the records having SEX as ‘M’. and increase AGE of all the males by one year.
sol
import mysql.connector as m
db = m.connect(“localhost”,”testuser”,”test123″,”TESTDB” )
cursor = db.cursor()
sql = “UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ‘%c'” % (‘M’)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()
DELETE Operation-To delete some records from your database.
Example–
Q: To delete all the records from EMPLOYEE where AGE is more than 20 −
import mysql.connector as m
db = m.connect(“localhost”,”testuser”,”test123″,”TESTDB” )
cursor = db.cursor()
sql = “DELETE FROM EMPLOYEE WHERE AGE > ‘%d'” % (20)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()