Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python MySQL library allows the conversion between Python and MySQL data types. MySQL Connector API is implemented using pure Python and does not require any third-party library.
This Python MySQL tutorial will help to learn how to use MySQL with Python from basics to advance, including all necessary functions and queries explained in detail with the help of good Python MySQL examples. So, let’s get started.
Installation
To install the Python-mysql-connector module, one must have Python and PIP, preinstalled on their system. If Python and pip are already installed type the below command in the terminal.
pip3 install mysql-connector-python
Note: If Python is not present, go through How to install Python on Windows and Linux? and follow the instructions provided.
Connecting to MySQL Server
We can connect to the MySQL server using the connect() method.
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password"
)
print(dataBase)
dataBase.close()
|
Output:
<mysql.connector.connection_cext.CMySQLConnection object at 0x7f73f0191d00>
Note: For more information, refer to Connect MySQL database using MySQL-Connector Python.
Creating Database
After connecting to the MySQL server let’s see how to create a MySQL database using Python. For this, we will first create a cursor() object and will then pass the SQL command as a string to the execute() method. The SQL command to create a database is –
CREATE DATABASE DATABASE_NAME
Example: Creating MySQL database with Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password"
)
cursorObject = dataBase.cursor()
cursorObject.execute("CREATE DATABASE gfg")
|
Output:
Creating Tables
For creating tables we will follow the similar approach of writing the SQL commands as strings and then passing it to the execute() method of the cursor object. SQL command for creating a table is –
CREATE TABLE
(
column_name_1 column_Data_type,
column_name_2 column_Data_type,
:
:
column_name_n column_Data_type
);
Example: Creating MySQL table using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
studentRecord =
cursorObject.execute(studentRecord)
dataBase.close()
|
Output:
Insert Data into Tables
To insert data into the MySQL table Insert into query is used.
Syntax:
INSERT INTO table_name (column_names) VALUES (data)
Example 1: Inserting Single Row
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("Ram", "CSE", "85", "B", "19")
cursorObject.execute(sql, val)
dataBase.commit()
dataBase.close()
|
Output:
Example 2: Inserting Multiple Rows
To insert multiple values at once, executemany() method is used. This method iterates through the sequence of parameters, passing the current parameter to the execute method.
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
sql = "INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = [("Nikhil", "CSE", "98", "A", "18"),
("Nisha", "CSE", "99", "A", "18"),
("Rohan", "MAE", "43", "B", "20"),
("Amit", "ECE", "24", "A", "21"),
("Anil", "MAE", "45", "B", "20"),
("Megha", "ECE", "55", "A", "22"),
("Sita", "CSE", "95", "A", "19")]
cursorObject.executemany(sql, val)
dataBase.commit()
dataBase.close()
|
Output:
Fetching Data
We can use the select query on the MySQL tables in the following ways –
- In order to select particular attribute columns from a table, we write the attribute names.
SELECT attr1, attr2 FROM table_name
- In order to select all the attribute columns from a table, we use the asterisk ‘*’ symbol.
SELECT * FROM table_name
Example: Select data from MySQL table using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "SELECT NAME, ROLL FROM STUDENT"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
dataBase.close()
|
Output:
Where Clause
Where clause is used in MySQL database to filter the data as per the condition required. You can fetch, delete or update a particular set of data in MySQL database by using where clause.
Syntax:
SELECT column1, column2, …. columnN FROM [TABLE NAME] WHERE [CONDITION];
Example: Where clause in MySQL using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "SELECT * FROM STUDENT where AGE >=20"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
dataBase.close()
|
Output:
('Rohan', 'MAE', 43, 'B', 20)
('Amit', 'ECE', 24, 'A', 21)
('Anil', 'MAE', 45, 'B', 20)
('Megha', 'ECE', 55, 'A', 22)
Order By Clause
OrderBy is used to arrange the result set in either ascending or descending order. By default, it is always in ascending order unless “DESC” is mentioned, which arranges it in descending order. “ASC” can also be used to explicitly arrange it in ascending order. But, it is generally not done this way since default already does that.
Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;
Example: Order By clause in MySQL using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "SELECT * FROM STUDENT ORDER BY NAME DESC"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
dataBase.close()
|
Output:
Limit Clause
The Limit clause is used in SQL to control or limit the number of records in the result set returned from the query generated. By default, SQL gives out the required number of records starting from the top but it allows the use of OFFSET keyword. OFFSET allows you to start from a custom row and get the required number of result rows.
Syntax:
SELECT * FROM tablename LIMIT limit;
SELECT * FROM tablename LIMIT limit OFFSET offset;
Example: Limit Clause in MySQL using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "SELECT * FROM STUDENT LIMIT 2 OFFSET 1"
cursorObject.execute(query)
myresult = cursorObject.fetchall()
for x in myresult:
print(x)
dataBase.close()
|
Output:
('Nikhil', 'CSE', 98, 'A', 18)
('Nisha', 'CSE', 99, 'A', 18)
Update Data
The update query is used to change the existing values in a database. By using update a specific value can be corrected or updated. It only affects the data and not the structure of the table. The basic advantage provided by this command is that it keeps the table accurate.
Syntax:
UPDATE tablename
SET ="new value"
WHERE ="old value";
Example: Update MySQL table using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "UPDATE STUDENT SET AGE = 23 WHERE Name ='Ram'"
cursorObject.execute(query)
dataBase.commit()
dataBase.close()
|
Output:
Delete Data from Table
We can use the Delete query to delete data from the table in MySQL.
Syntax:
DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE
Example: Delete Data from MySQL table using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query = "DELETE FROM STUDENT WHERE NAME = 'Ram'"
cursorObject.execute(query)
dataBase.commit()
dataBase.close()
|
Output:
Drop Tables
Drop command affects the structure of the table and not data. It is used to delete an already existing table. For cases where you are not sure if the table to be dropped exists or not DROP TABLE IF EXISTS command is used. Both cases will be dealt with in the following examples.
Syntax:
DROP TABLE tablename;
DROP TABLE IF EXISTS tablename;
At first, let’s see the list of tables in our database.
We can see that there are two tables for students, so let’s drop the second table.
Example 1: Drop Table in MySQL using Python
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query ="DROP TABLE Student;"
cursorObject.execute(query)
dataBase.commit()
dataBase.close()
|
Output:
Example 2: Drop Table if exists
Python3
import mysql.connector
dataBase = mysql.connector.connect(
host ="localhost",
user ="user",
passwd ="password",
database = "gfg"
)
cursorObject = dataBase.cursor()
query ="Drop Table if exists Employee;"
cursorObject.execute(query)
dataBase.commit()
dataBase.close()
|
The above example, will not create any error and output because we have used the Drop Table is exists query. If we will simply use the Drop table Employee then ProgrammingError: 1051 (42S02): Unknown table ‘gfg.Employee’ is raised.
Python MySQL Exercises
Working With Tables
Working on Columns
Python MySQL Applications and Projects
Similar Reads
Python MySQL
Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python MySQL library allows the conversion between Python and MySQL data types. MySQL Connector API is implemented using pure Python and does not require any third-party library. This Python MySQL tutorial will
9 min read
How to install MySQL connector package in Python?
Prerequisite: Python Language Introduction MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. A connector is employ
2 min read
Connect MySQL database using MySQL-Connector Python
While working with Python we need to work with databases, they may be of different types like MySQL, SQLite, NoSQL, etc. In this article, we will be looking forward to how to connect MySQL databases using MySQL Connector/Python.MySQL Connector module of Python is used to connect MySQL databases with
2 min read
How to Install MySQLdb module for Python in Linux?
In this article, we are discussing how to connect to the MySQL database module for python in Linux. MySQLdb is an interface for connecting to a MySQL database server from Python. It implements the Python Database API v2.0 and is built on top of the MySQL C API. Installing MySQLdb module for Python o
2 min read
MySQL Queries
Python MySQL - Select Query
Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Database such as MySQL, GadFly, mySQL, PostgreSQL, Microsoft SQL Server 2000
2 min read
CRUD Operation in Python using MySQL
In this article, we will be seeing how to perform CRUD (CREATE, READ, UPDATE and DELETE) operations in Python using MySQL. For this, we will be using the Python MySQL connector. For MySQL, we have used Visual Studio Code for python. Before beginning we need to install the MySQL connector with the co
6 min read
Python MySQL - Create Database
Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Database such as MySQL, GadFly, mSQL, PostgreSQL, Microsoft SQL Server 2000,
2 min read
Python MySQL - Update Query
A connector is employed when we have to use MySQL with other programming languages. The work of MySQL-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server. Update Clause The update is used to ch
2 min read
Python MySQL - Insert into Table
MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify
3 min read
Python MySQL - Insert record if not exists in table
In this article, we will try to insert records and check if they EXISTS or not. The EXISTS condition in SQL is used to check if the result of a correlated nested query is empty (contains no tuples) or not. It can be used to INSERT, SELECT, UPDATE, or DELETE statements. Pre-requisite Connect MySQL Da
4 min read
Python MySQL - Delete Query
Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Databases such as MySQL, GadFly, PostgreSQL, Microsoft SQL Server 2000, Info
3 min read
MySQL Working with Data
MySQL | Regular Expressions (Regexp)
In MySQL, regular expressions (REGEX) are a powerful tool used to perform flexible pattern matching within string data. By utilizing REGEXP and RLIKE operators, developers can efficiently search, validate, and manipulate string data in more dynamic ways than simple LIKE queries. In this article, we
6 min read
SQL Query to Match Any Part of String
It is used for searching a string or a sub-string to find a certain character or group of characters from a string. We can use the LIKE Operator of SQL to search sub-strings. The LIKE operator is used with the WHERE Clause to search a pattern in a string of columns. The LIKE operator is used in conj
3 min read
SQL Auto Increment
In SQL databases, a primary key is important for uniquely identifying records in a table. However, sometimes it is not practical to manually assign unique values for each record, especially when handling large datasets. To simplify this process, SQL databases offer an Auto Increment feature that aut
6 min read
SQL Query to Delete Duplicate Rows
Duplicate rows in a database can cause inaccurate results, waste storage space, and slow down queries. Cleaning duplicate records from our database is an essential maintenance task for ensuring data accuracy and performance. Duplicate rows in a SQL table can lead to data inconsistencies and performa
5 min read
SQL Query to Convert an Integer to Year Month and Days
With this article, we will be knowing how to convert an integer to Year, Month, Days from an integer value. The prerequisites of this article are you should be having a MSSQL server on your computer. What is a query? A query is a statement or a group of statements written to perform a specific task,
2 min read
Calculate the Number of Months between two specific dates in SQL
In this article, we will discuss the overview of SQL Query to Calculate the Number of Months between two specific dates and will implement with the help of an example for better understanding. Let's discuss it step by step. Overview :Here we will see, how to calculate the number of months between th
3 min read
How to Compare Two Queries in SQL
Queries in SQL :A query will either be an invitation for data results from your info or for action on the info, or each. a question will provide you with a solution to a straightforward question, perform calculations, mix data from totally different tables, add, change, or delete data from info. Cre
2 min read
Joining 4 Tables in SQL
The purpose of this article is to make a simple program to Join two tables using Join and Where clause in SQL. Below is the implementation for the same using MySQL. The prerequisites of this topic are MySQL and the installment of Apache Server on your computer. Introduction :In SQL, a query is a req
3 min read
MySQL Working with Images
Working with MySQL BLOB in Python
In Python Programming, We can connect with several databases like MySQL, Oracle, SQLite, etc., using inbuilt support. We have separate modules for each database. We can use SQL Language as a mediator between the python program and database. We will write all queries in our python program and send th
4 min read
Retrieve Image and File stored as a BLOB from MySQL Table using Python
Prerequisites: MySQL server should be installed In this post, we will be talking about how we can store files like images, text files, and other file formats into a MySQL table from a python script. Sometimes, just like other information, we need to store images and files into our database and provi
3 min read
How to read image from SQL using Python?
In this article, we are going to discuss how to read an image or file from SQL using python. For doing the practical implementation, We will use MySQL database. First, We need to connect our Python Program with MySQL database. For doing this task, we need to follow these below steps: Steps to Connec
3 min read
Boutique Management System using Python-MySQL Connectivity
In this article, we are going to make a simple project on a boutique management system using Python MySql connectivity. Introduction This is a boutique management system made using MySQL connectivity with Python. It uses a MySQL database to store data in the form of tables and to maintain a proper r
15+ min read