🚀 DevOps & SRE Certification Program 📅 Starting: 1st of Every Month 🤝 +91 8409492687 🔍 Contact@DevOpsSchool.com

Upgrade & Secure Your Future with DevOps, SRE, DevSecOps, MLOps!

We spend hours on Instagram and YouTube and waste money on coffee and fast food, but won’t spend 30 minutes a day learning skills to boost our careers.
Master in DevOps, SRE, DevSecOps & MLOps!

Learn from Guru Rajesh Kumar and double your salary in just one year.


Get Started Now!

Uncategorised

Python Tutorials: Database operations using python – mysql

July 24, 2022 Leave a Comment

Step 1 – Install MYSQL Server

  • https://www.devopsschool.com/blog/how-to-download-and-install-mysql-in-macos-10/
  • https://www.devopsschool.com/tutorial/mysql/how-to-install-mysql-on-centos-7.html

Step 2 – Install python mysql connector using PIP3

$ sudo pip3 install mysql-connector-python

Step 3 – Using Python – Check a mysql Connection

Step 4 – Using Python – Create a database

Step 5 – Using Python – Create a Table

Step 6 – Using Python – Insert Row Ops

Step 7 – Using Python – Read Row Ops

Step 8 – Using Python – Update Row Ops

Step 9 – Using Python – Delete Row Ops

import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
# Python Program to Check database connection in MySQL
try:
connection = mysql.connector.connect(host='localhost',
user='root',
password='MyNewPass')
if connection.is_connected():
db_Info = connection.get_server_info()
print("Connected to MySQL Server version ", db_Info)
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")
view raw check.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
# Python Program to Check database connection in MySQL
try:
connection = mysql.connector.connect(host='localhost',
user='root',
password='MyNewPass')
if connection.is_connected():
my_database = connection.cursor()
my_database.execute("CREATE DATABASE devopsschool1")
except Error as e:
print("Error while connecting to MySQL", e)
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")
view raw createdb.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
try:
connection = mysql.connector.connect(host='localhost',
database='devopsschool1',
user='root',
password='MyNewPass')
mySql_Create_Table_Query = """CREATE TABLE Laptop (
Id int(11) NOT NULL,
Name varchar(250) NOT NULL,
Price float NOT NULL,
Purchase_date Date NOT NULL,
PRIMARY KEY (Id)) """
cursor = connection.cursor()
result = cursor.execute(mySql_Create_Table_Query)
print("Laptop Table created successfully ")
except mysql.connector.Error as error:
print("Failed to create table in MySQL: {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")
view raw createtable.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
try:
connection = mysql.connector.connect(host='localhost',
database='devopsschool1',
user='root',
password='MyNewPass')
cursor = connection.cursor()
print("Displaying laptop record Before Deleting it")
sql_select_query = """select * from Laptop where id = 2"""
cursor.execute(sql_select_query)
record = cursor.fetchone()
print(record)
sql_Delete_query = """Delete from Laptop where id = 2"""
cursor.execute(sql_Delete_query)
connection.commit()
cursor.execute(sql_select_query)
records = cursor.fetchall()
if len(records) == 0:
print("\nRecord Deleted successfully ")
except mysql.connector.Error as error:
print("Failed to delete record from table: {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")
view raw delete.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
def insertVariblesIntoTable(id, name, price, purchase_date):
try:
connection = mysql.connector.connect(host='localhost',
database='devopsschool1',
user='root',
password='MyNewPass')
cursor = connection.cursor()
mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date)
VALUES (%s, %s, %s, %s) """
recordTuple = (id, name, price, purchase_date)
cursor.execute(mySql_insert_query, recordTuple)
connection.commit()
print("Record inserted successfully into Laptop table")
except mysql.connector.Error as error:
print("Failed to insert into MySQL table {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")
insertVariblesIntoTable(2, 'Area 51M', 6999, '2019-04-14')
insertVariblesIntoTable(3, 'MacBook Pro', 2499, '2019-06-20')
view raw insert.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
try:
connection = mysql.connector.connect(host='localhost',
database='devopsschool1',
user='root',
password='MyNewPass')
sql_select_Query = "select * from Laptop"
cursor = connection.cursor()
cursor.execute(sql_select_Query)
records = cursor.fetchall()
print("Total number of rows in Laptop is: ", cursor.rowcount)
print("\nPrinting each laptop record")
for row in records:
print("Id = ", row[0], )
print("Name = ", row[1])
print("Price = ", row[2])
print("Purchase date = ", row[3], "\n")
except Error as e:
print("Error reading data from MySQL table", e)
finally:
if (connection.is_connected()):
connection.close()
cursor.close()
print("MySQL connection is closed")
view raw read.py hosted with ❤ by GitHub
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
# $ sudo pip install mysql-connector
# $ sudo pip install mysql-connector-python-rf
try:
connection = mysql.connector.connect(host='localhost',
database='devopsschool1',
user='root',
password='MyNewPass')
cursor = connection.cursor()
print("Before updating a record ")
sql_select_query = """select * from Laptop where id = 2"""
cursor.execute(sql_select_query)
record = cursor.fetchone()
print(record)
# Update single record now
sql_update_query = """Update Laptop set Name = 'Lenvo' where id = 3"""
cursor.execute(sql_update_query)
connection.commit()
print("Record Updated successfully ")
print("After updating record ")
cursor.execute(sql_select_query)
record = cursor.fetchone()
print(record)
except mysql.connector.Error as error:
print("Failed to update table record: {}".format(error))
finally:
if (connection.is_connected()):
connection.close()
print("MySQL connection is closed")
view raw update.py hosted with ❤ by GitHub
Subscribe
Notify of
guest


0 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

Certification Courses

DevOpsSchool has introduced a series of professional certification courses designed to enhance your skills and expertise in cutting-edge technologies and methodologies. Whether you are aiming to excel in development, security, or operations, these certifications provide a comprehensive learning experience. Explore the following programs:

DevOps Certification, SRE Certification, and DevSecOps Certification by DevOpsSchool

Explore our DevOps Certification, SRE Certification, and DevSecOps Certification programs at DevOpsSchool. Gain the expertise needed to excel in your career with hands-on training and globally recognized certifications.

0
Would love your thoughts, please comment.x
()
x