Python for mysql

One of the key reasons for Python’s success is its ability to be easily integrated with databases. In particular, Python is an excellent choice for anyone looking to work with MySQL databases, thanks to a variety of libraries and APIs that make querying and adding to databases simpler and more intuitive. With this tutorial, you’ll be able to learn how to use Python in conjunction with MySQL and gain the skills needed to start building powerful applications.

How to use MySQL with Python :

• MySQL is a widely used open source relational database management system (RDBMS).
• Follow the steps given below to use MySQL with python 
  • Step 1: Install the required Packages
  • Step 2: Establishing a Connection
  • Step 3: Create and Execute SQL Queries
  • Step 4: Execute CRUD Operations
  • Step 5: Close the Connection 

Step 1: Install the required Packages

• The first step to using Python with MySQL is to install the required packages.
• Two popular packages for connecting to MySQL in Python are 
  • mysql-connector and 
  • pymysql.
• These packages can be installed using pip through the command line.
• Code Sample :

pip install mysql-connector
pip install pymysql

Step 2: Establishing a Connection

• After installing the required packages, the next step is to establish a connection to the MySQL database from Python.
• This is done using the   connect()  function provided by the 
  • mysql-connector or 
  • pymysql package.
• Once the required library is imported, we can establish a connection to the database by creating a new Connection object.
• We can pass in the following parameters to the Connection object: 
  • host : the hostname or IP address of the MySQL server user. 
  • user : the username for the MySQL account.
  • password : the password for the MySQL account database.
  • database : the name of the database to connect to.
• Code Sample :

import mysql.connector

mydb = mysql.connector.connect(
 host="localhost",
 user="yourusername",
 password="yourpassword"
)

print(mydb)

Step 3: Creating and Executing SQL Queries

• Once a connection to the database has been established, SQL queries can be created and executed using Python.
• To do this, we'll need to create a Cursor object. 
• This can be done using the   cursor()  method provided by the 
  • mysql-connector or 
  • pymysql package.
• Once we have a Cursor object, we can execute SQL queries by calling the execute() method.
• We can pass in the SQL query as a string to the execute() method. 
• Code Sample :

import mysql.connector

mydb = mysql.connector.connect(
 host="localhost",
 user="yourusername",
 password="yourpassword",
 database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()

for x in myresult:
 print(x)
 
 
 

Step 4: Executing CRUD Operations

• After successfully executing SQL queries from Python, CRUD operations can be performed on the database.
• CRUD is an abbreviation for Create, Read, Update, and Delete operations.
• We can retrieve the results by calling the   fetchall()  method on the  ResultSet  object. 
• This method will return a list of tuples. 
• Each tuple represents a row in the  ResultSet . 
• Code Sample :

import mysql.connector

mydb = mysql.connector.connect(
 host="localhost",
 user="yourusername",
 password="yourpassword",
 database="mydatabase"
)
mycursor = mydb.cursor()


# Create
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()


# Read
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
 print(x)


# Update
sql = "UPDATE customers SET address = 'Canyon 123' WHERE name = 'John'"
mycursor.execute(sql)
mydb.commit()


# Delete
sql = "DELETE FROM customers WHERE name = 'John'"
mycursor.execute(sql)
mydb.commit()

Step 5 : Close the Connection

• When we are finished working with the database, we need to close the connection 
• This can be done by calling the close() method on the Connection object. connection.close()