#First, download and install the Python MySQL connector
using the following PIP command.
#python -m pip install mysql-connector-python
# Porgram 1 (Prog to show the connectivity from python and
MYSQL, then fetch the record and display it.)
import [Link] as my # import module for connecting python and mysql
db=[Link](host="localhost",user="root",password="root",database="school1")
if db.is_connected(): # to check the connectivity
print("success")
db_cursor=[Link]() # to open crusor
db_cursor.execute("select * from student") # to execute query from sql
data= db_cursor.fetchall() # to fetch all data from table
count=db_cursor.rowcount # to count no. of records
print("Total no. of rows:",count) # to display no of records
for row in data: #
print(row)
[Link]() # to close the connection
# Program 2 (Program to create database through python program in
MQ SQL)
import [Link] as my
db_connection = [Link](
host= "localhost",
user= "root",
passwd= "root"
# creating database_cursor to perform SQL operation
db_cursor = db_connection.cursor()
# executing cursor with execute method and pass SQL query
db_cursor.execute("CREATE DATABASE my_first_db")
# get list of all databases
db_cursor.execute("SHOW DATABASES")
#print all databases
for db in db_cursor:
print(db)
db_connection.close()
# Program 3 (Program to create table and display all the tables)
import [Link]
db_connection = [Link](host= "localhost",user= "root",passwd= "root",database="my_first_db")
db_cursor = db_connection.cursor()
#Here creating database table as student'
db_cursor.execute("CREATE TABLE student1 (id INT, name VARCHAR(255))")
#Here creating database table as employee with primary key
db_cursor.execute("CREATE TABLE employee1(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255),
salary INT(6))")
#Get database table
#Get database table'
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
print(table)
db_cursor = db_connection.cursor()
db_cursor.execute("ALTER TABLE student1 MODIFY id INT PRIMARY KEY")
db_connection.commit()
db_cursor.execute("describe student1")
db_connection.close()
# Program 4 (Program to Insert data and alter table)
import [Link] as my
db_connection = [Link](host= "localhost",user= "root",passwd=
"root",database="my_first_db2")
db_cursor = db_connection.cursor()
student_sql_query = "INSERT INTO student1(id,name) VALUES(01, 'John'),(16,'Raj'),(15,'Tom'),(17,'John')"
employee_sql_query = " INSERT INTO employee1 (id, name, salary) VALUES (01, 'John', 10000)"
#Execute cursor and pass query as well as student data
db_cursor.execute(student_sql_query)
#Execute cursor and pass query of employee and data of employee
db_cursor.execute(employee_sql_query)
db_connection.commit()
print(db_cursor.rowcount, "Record Inserted")
db_cursor.execute("select * from student1")
data= db_cursor.fetchall()
count=db_cursor.rowcount
print("Total no. of rows:",count)
for row in data:
print(row)
data1= db_cursor.fetchmany()
count=db_cursor.rowcount
print("Total no. of rows:",count)
for row in data1:
print(row)
data2= db_cursor.fetchone()
count=db_cursor.rowcount
print("Total no. of rows:",count)
print(data2)
#for row in data1:
# print(row)
db_cursor = db_connection.cursor()
db_cursor.execute("select * from employee1")
data= db_cursor.fetchall()
count=db_cursor.rowcount
print("Total no. of rows:",count)
for row in data:
print(row)
db_connection.close()
# Program 5 (Program to Update & Delete data from table)
import [Link] as my
db_connection = [Link](
host= "localhost",
user= "root",
passwd= " ",
database="my_first_db"
# creating database_cursor to perform SQL operation
db_cursor = db_connection.cursor()
obj = db_connection.cursor()
[Link]("UPDATE student1 SET id=18 WHERE name='Raj'")
db_connection.commit()
print("updated data is -")
[Link]("SELECT * from student1")
result= [Link]()
for x in result:
print(x)
obj = db_connection.cursor()
[Link]("DELETE FROM student1 WHERE id=3")
db_connection.commit()
print("updated data is -")
[Link]("SELECT * from student")
result= [Link]()
for x in result:
print(x)
db_connection.close()
# Program 6 (Complete program of SQL Connectivity with Python)
#First, download and install the Python MySQL connector
using the following PIP command.
#python -m pip install mysql-connector-python
import [Link] as my
db_connection = [Link](
host= "localhost",
user= "root",
passwd= " "
# creating database_cursor to perform SQL operation
db_cursor = db_connection.cursor()
# executing cursor with execute method and pass SQL query
db_cursor.execute("CREATE DATABASE my_first_db")
# get list of all databases
db_cursor.execute("SHOW DATABASES")
#print all databases
for db in db_cursor:
print(db)
db_connection = [Link](host= "localhost",user= "root",passwd=
"root",database="my_first_db2")
db_cursor = db_connection.cursor()
#Here creating database table as student'
db_cursor.execute("CREATE TABLE student1 (id INT, name VARCHAR(255))")
#Here creating database table as employee with primary key
db_cursor.execute("CREATE TABLE employee1(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255),
salary INT(6))")
#Get database table
#Get database table'
db_cursor.execute("SHOW TABLES")
for table in db_cursor:
print(table)
db_cursor = db_connection.cursor()
#Here we modify existing column id
#db_cursor = db_connection.cursor()
student_sql_query = "INSERT INTO student1(id,name) VALUES(01, 'John'),(16,'Raj'),(15,'Tom'),(17,'John')"
employee_sql_query = " INSERT INTO employee1 (id, name, salary) VALUES (01, 'John', 10000)"
#Execute cursor and pass query as well as student data
db_cursor.execute(student_sql_query)
#Execute cursor and pass query of employee and data of employee
db_cursor.execute(employee_sql_query)
db_connection.commit()
print(db_cursor.rowcount, "Record Inserted")
db_cursor.execute("ALTER TABLE student1 MODIFY id INT PRIMARY KEY")
db_cursor = db_connection.cursor()
db_cursor.execute("select * from student1")
data= db_cursor.fetchall()
count=db_cursor.rowcount
print("Total no. of rows:",count)
for row in data:
print(row)
db_cursor = db_connection.cursor()
db_cursor.execute("select * from employee1")
data= db_cursor.fetchall()
count=db_cursor.rowcount
print("Total no. of rows:",count)
for row in data:
print(row)
obj = db_connection.cursor()
[Link]("UPDATE student1 SET id=18 WHERE name='Raj'")
db_connection.commit()
print("updated data is -")
[Link]("SELECT * from student1")
result= [Link]()
for x in result:
print(x)
obj = db_connection.cursor()
[Link]("DELETE FROM student1 WHERE id=3")
db_connection.commit()
print("updated data is -")
[Link]("SELECT * from student")
result= [Link]()
for x in result:
print(x)
db_connection.close()