Today in this article, we will use Python MySQL Connectors- Connect To A MySQL Database and perform efficient CRUD operations on MySQL database.
We will see different techniques for connecting to MySQL using Python.
- Using pymysql – Please visit this article for more details.
- Using MySQL connectors- we will discuss this approach in this article,
However, this article will focus on how to use PyMySQL which is MySQL client library.
Installing mysql.connector
python3 -m pip install mysql-connector-python
----- Installing 'mysql-connector-python' -----
Collecting mysql-connector-python
Downloading mysql_connector_python-8.0.31-cp37-cp37m-win_amd64.whl (7.9 MB)
---------------------------------------- 7.9/7.9 MB 5.2 MB/s eta 0:00:00
Collecting protobuf<=3.20.1,>=3.11.0
Downloading protobuf-3.20.1-cp37-cp37m-win_amd64.whl (905 kB)
-------------------------------------- 905.1/905.1 kB 5.7 MB/s eta 0:00:00
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.31 protobuf-3.20.1
----- Successfully installed 'mysql-connector-python' -----
mysql-connector-python is MySQL driver written in Python which does not depend on MySQL libraries and implements the DB API v2.0 specification (PEP-249).
Step 1: Adding import statements
Adding import statement to python,
import mysql.connector
Step 2: Connect to the database
Connect to the database using mysql.connector connect method as below.
Below in the example connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object.
The following example shows how to connect to the MySQL server:
connection = mysql.connector.connect(host='localhost',
user='user',
password='passwd',
database='db')
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Please provide valid DB credentials")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
Step 3: Read from the database table
Let’s perform read operations on the MySQL Database,
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `College`, `Name` FROM `Employee` WHERE `ID`=%s"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
Step 4: Read all tables from MySQL database using Python
Read All tables from MYSQL
try:
with connection.cursor() as cursor:
# Read tables
sql = "SHOW TABLES"
cursor.execute(sql)
result = cursor.fetchone()
print(result)
finally:
connection.close()
Example:
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.
