Python MySQL Connector example, pip, install, and download

In this tutorial, we will see Python mysql connector example to download or install with pip. We all know that Python is a high-level, all-purpose programming language. It is utilized in various fields, including software development, data science, computational mathematics, and server-side web development. Database applications can also use Python. In this tutorial, we’ll discover Python MySQL Connection and Commands.

This article is all about learning Python MySQL connector examples and commands like INSERT, SELECT, UPDATE, and DELETE. We will start with a Python MySQL connection, then will go for Python MySQL connector install or Python MySQL connector download, and then we will learn about each query in detail with an explanation.

Python MySQL Connection:

Before we move ahead in this article on Python MySQL Connection to the database we will first download MySQL on our machine. Use the following link to download MySQL:

https://www.mysql.com/downloads/

Let’s see some steps to download and install MySQL inside your system-

You can continue to download and install MySQL in your system, it’s not that touch, but still, if you face any problem in installing MySQL to your system, we recommend you follow this tutorial to learn How to download and install MySQL?

MySQL Connector Python:

Now, moving on to the python connection to the MySQL database, we will first install the MySQL Connector Python module. This module actually helps Python to communicate with the MySQL database and use it to build applications with databases. Installing the MySQL Python Connector is the following step. Connectors enable communication between two distinct kinds of programs by acting as a middleman. Python and MySQL can communicate with each other thanks to connectors for both languages. On the command prompt, we’ll run the command to install the MySQL Connector Python.

There are multiple methods to connect Python with MySQL. Here in this article, we will be using the MySQL Connector Python module to set up our connection.

Installing MySQL Connector Python

In order to install the MySQL Connector Python module, use the below command, in your terminal:

pip install mysql-connector-python

If there is some error in installing the MySQL Connector Python module, you can try this command:

pip install pymysql

We searched over the internet that what type of errors may come to this point but we found nothing so we expect we can go ahead without any problems.

Python MySQL Creating a Database

Once the installation is done, we will create a database in MySQL:

CREATE DATABASE name_of_the_database

Here the name_of_the_database should be replaced with the desired name of the database.

Python MySQL Establishing Connection

We have already developed our system for connecting MySQL Python to a database. We’ll start setting up the database connection right now. You can adhere to the instructions listed below to connect MySQL and Python:

• Import MySQL connector
• Make a connection
• Create a request

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='your_username', password='your_password')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping database copyassignment_db if already exists.
cursor.execute("DROP database IF EXISTS copyassignment_db")

#Preparing query to create a database
sql = "CREATE database copyassignment_db";

#Creating a database
cursor.execute(sql)

#Retrieving the list of databases
print(">>> LIST OF DATABASES <<<")
cursor.execute("SHOW DATABASES")
print(cursor.fetchall())

#Closing the connection
conn.close()

Output:

Explanation:
Line 1: We imported the MySQL connector module.
Line 2: To connect MySQL, call the connect() function of the MySQL Connector class with the necessary arguments. If the connection was established successfully, a MySQLConnection object would be returned.

The arguments in this function are stated below:
Username: This is simply the name using which one accesses the MySQL database. The default username provided by MySQL is the root.
Password: This is the password that the user set when installing MySQL.
Replace your_username and your_passoword with your MySQL username (default username is root) and password.
Hostname: This is the name of the server. If it’s localhost then we simply use localhost or 127.0.0.0
Database name: Here we specify the name of the database.

Line 3: Within a stored process, a cursor is used to manage a result set. Using a cursor, we may traverse across a group of rows returned by a query and handle each result separately.
Line 4: execute() is used to execute the SQL query. In this, we passed a query to drop the table if there already exists one with the same name.
Line 5 to 6: A variable “sql” is declared and inside that, we stored a SQL query. We then executed this query that will create a database with the name “copyassignment_db”
Line 7 to 9: Now, to see what all databases exist, we used the SHOW DATABASES query and printed if using fetchall() that fetches all the rows and stores the result in the form of a tuple.
Line 10: We closed the connection with our database using close().

Finally, the process of setting up Python MySQL Connection is finished here. In the above code, we can also use the try and except block to display a message if the connection is successful or not.

Python MySQL Create Table

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='your_username', password='your_password', database="copyassignment_db")

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#creating new table named interns and salary
cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
cursor.execute("CREATE TABLE salary (name VARCHAR(255), salary VARCHAR(255))")

print(">>> List of tables in copyassignment_dbdatabase  <<< ")
cursor.execute("SHOW TABLES")
for x in cursor:
  print(x)

#Closing the connection
conn.close()

Output:

Explanation:
Line 1 to 3: Basic SQL connection code. The only difference is that this time in connect() we added a parameter with the name “database” that holds the name of the database inside which the tables are to be created.
Line 4 to 5: We used a SQL CREATE TABLE query to create a table.

• Syntax: CREATE TABLE <table_name>(column1 datatype, column2 datatype)
• <table_name>: We created two tables named customers and another named salary
• (column1 datatype, column2 datatype): In place of column1 and column2 we have to specify the name of the column and in place of the datatype, we have to specify which type of data it will hold i.e. int, varchar, etc.
• Line 6 to 10: SQL query SHOW TABLES displays all the tables in that database. We used a for loop to print all the tables and finally, we closed the connection.

Python MySQL Insert Data

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='your_username', password='your_password',database="copyassignment_db")

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = "INSERT INTO customers (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'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]
cursor.executemany(sql, val)

#printing no. of records inserted
print("\n\n")
print(cursor.rowcount, "record inserted.")

Output:

Explanation:
Line 1 to 3: Usual setting up the Python MySQL connection. (Please note that you need to replace your_username and your_password with your MySQL username and password.)
Line 4: Now in sql variable we have passed a SQL query that is responsible for inserting the data into the table.

• Syntax: INSERT INTO <table_name> (column1_name, column2_name) VALUES (<specifier1>, <specifier2>)
• (column1_name, column2_name) here in both these fields, we have to mention the name of the column. Here it is (name, position)
• VALUES (<specifier1>, <specifier2>) here in place of specifier1 and 2, we have to mention the specifiers of the data types our column will hold. Here in the above query, both the columns will have a string and so we mentioned “%s”

Line 5: The val variable holds the data that is to be inserted in the form of tuples wrapped inside a list.
Line 6: executemany() helps in inserting multiple rows simultaneously.
Line 7 to 8: And we simply used rowcount to count the number of rows.

Python MySQL Select Data

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='your_username', password='your_password', database="copyassignment_db")

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#creating new table named interns and salary
cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
cursor.execute("CREATE TABLE salary (name VARCHAR(255), salary VARCHAR(255))")

print(">>> List of tables in copyassignment_dbdatabase  <<< ")
cursor.execute("SHOW TABLES")
for x in cursor:
  print(x)

#Closing the connection
conn.close()

Explanation:
In order to select data from the database, we use the SELECT query.

• Syntax: SELECT * FROM <table_name>
• Here * men’s all columns that are all the column’s data will be selected
• Whenever we want to select data from any particular column then we can use
  SELECT <column1>,<column2> FROM customers

The fetchall() fetches all the selected data and we printed the fetched data using for loop

Python MySQL Update Data

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='your_username', password='your_password',database="copyassignment_db")

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"
cursor.execute(sql)
print(cursor.rowcount, "record(s) affected")

#selecting data and printing
cursor.execute("SELECT * FROM customers")
result = cursor.fetchall()
print("\n\n")
print(">>> RECORDS AFTER UPDATION IN 'customers' TABLE  <<< ")
for x in result:
  print(x)
conn.commit()

Output:

Here, the only change is that to update the data we used the UPDATE query.

• Syntax: UPDATE <table_name> SET <column_name>= ‘<updated_value>’ WHERE <condition>’
• Here in column name we will specify the name of the column and assign a value (that we want to update). Our column name is address and the value we assigned is Canyon 123
• But the question is that this value needs to be changed where? And so we use WHERE in which we specify the condition address = ‘Valley 345’. This simply means that we will update the value of the address column where the address is Valley 345.

Python MySQL Delete Data

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='root', password='dv2themoon', database="copyassignment_db")

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

cursor.execute(sql)

conn.commit()

print(cursor.rowcount, "record(s) deleted")

Explanation: This DELETE query is used when we want to delete any existing record from the table.

• Syntax: DELETE FROM <table_name> WHERE <condition>
• As usual, we have to mention the table name from which we want to delete the record
• <condition> this will contain the condition in which the record is to be deleted. Here in the above come we used address = ‘Mountain 21’ This means that the record whose address field has the value Mountain 21 is to be deleted.

Reference Links:

Official MySQL Connection Documentation: MySQL Connector Python

Projects Based on Python MySQL:

• Employee Management System
• Restaurant Management System

Summary

Here’s the end. One of the most important topics we have covered in this article in the easiest way possible. We have covered python connection to mysql database and also MySQL queries. There are many other SQL statements like JOIN, LIMIT, DROP TABLE, etc that we will cover in our other article.

Thank you for visiting our website.

Also Read:

• Flower classification using CNN
• Music Recommendation System in Machine Learning
• Create your own ChatGPT with Python
• Bakery Management System in Python | Class 12 Project
• SQLite | CRUD Operations in Python
• Event Management System Project in Python
• Ticket Booking and Management in Python
• Hostel Management System Project in Python
• Sales Management System Project in Python
• Bank Management System Project in C++
• Python Download File from URL | 4 Methods
• Python Programming Examples | Fundamental Programs in Python
• Spell Checker in Python
• Portfolio Management System in Python
• Stickman Game in Python
• Contact Book project in Python
• Loan Management System Project in Python
• Cab Booking System in Python
• Brick Breaker Game in Python
• Tank game in Python
• GUI Piano in Python
• Ludo Game in Python
• Rock Paper Scissors Game in Python
• Snake and Ladder Game in Python
• Puzzle Game in Python
• Medical Store Management System Project in Python
• Creating Dino Game in Python
• Tic Tac Toe Game in Python
• Test Typing Speed using Python App
• MoviePy: Python Video Editing Library