In this article, we’ll learn how to connect MySQL database with Python using the PyMySQL database driver.
Connect Database with Python
First, we need to install a MySQL database driver called PyMySQL that will help us bridge Python and MySQL.
pip install PyMySQL
Now, after installing this package, we can start the connection process by importing it into our Python script.
Create a Python file, let’s say db_connection.py, and write the following code.
import pymysql
# Establish MySQL connection with Python
sql_connection = pymysql.connect(
host="localhost",
user="root",
password="xxxxxxxxx",
database="consumer",
cursorclass=pymysql.cursors.DictCursor
)
print("Connected to database.")
# Create cursor to interact with database
cursor = sql_connection.cursor()
# Create an entry in the database
cursor.execute(
"""INSERT INTO consumers (name, address, phone)
values ('Max', '21 St. Parkway', 871231);"""
)
# Save the changes in the database
sql_connection.commit()
# Fetch data from the database
cursor.execute(
"""SELECT * FROM consumers;"""
)
data = cursor.fetchall()
print(data[0])
# Close the connection with database
sql_connection.close()
print("Operation successful.")
This code creates a connection to the MySQL database and then writes an entry in the database table.
First, the package is imported which will help us bridge Python and MySQL using Python code.
Next, we established a connection using pymysql.connect()
and passed the required arguments:
hostname
: The location of the MySQL database. We have a MySQL server in the local system hence, we passed"localhost"
.user
: Username of the MySQL server. In this case, we passed"root"
, which is a default name.password
: Password of the MySQL server.database
: The name of the database with which you want to connect. In this case, we are connecting to the"consumer"
database on the server.cursorclass
: This decides the format of the result returned by the cursor. In this case, the cursor will return results in a dictionary (DictCursor
) format.
Then we created a cursor (sql_connection.cursor()
) object that helps in executing SQL queries.
Using cursor.execute()
, we executed an SQL query to insert an entry in the consumers
table of the consumer
database.
Then we saved the changes to the database using sql_connection.commit()
.
We then executed an SQL query to get all the data from the consumers
table in the database.
Using cursor.fetchall()
, we fetched all the entries from the table and printed the first result.
Finally, we closed the connection with the database using sql_connection.close()
.
Now, when we run this code, we’ll get the following result.
Connected to database.
{'name': 'Max', 'address': '21 St. Parkway', 'phone': 871231}
Operation successful.
That’s all for now.
Keep Coding✌✌.