Python-MySQL Connection and Set-up

When you create useful applications using Python, you need to store the data somewhere in a database so that it can be used later. One great relational database that you can use is MySQL.

You can easily connect Python with the MySQL database and do the database operations using Python. For that, you need to set up the MySQL database on your system and connect it to Python.

In this article, let’s see how we can set up the MySQL database and connect it to Python. We will also learn how we can do basic operations like creating databases, creating tables, inserting data, retrieving data, etc. Let’s dive right in.

MySQL Installation and Setup

First of all, you need to install MySQL on your system and set everything up so that you can connect to the database. You can refer the following video to install MySQL on your system.

Once you have set up MySQL on your system, you can use MySQL Workbench to write SQL queries and do the database operations.

I’m attaching the queries for doing the basic CRUD operations in MySQL. You can download it if you want to refer.

Connecting MySQL and Python

Now, let’s see how we can connect MySQL with Python so that we can write the Python code to do the database operations.

To connect Python with MySQL, you need a MySQL connector. We can install it using the package installer which is pip. If you have Python installed in your system, you might already have pip installed on your system. Now, open your command prompt and type in the following command.

pip install mysql-connector-python

Now, everything is ready. Let’s see how we can write Python code to connect to the MySQL database and do some operations.

Now, open your favorite code editor and create a new Python file. I have created a file called “hellomysql.py”.

Creating a Connection

Whenever you want to connect to the mysql database, you need to import the mysql.connector module. Let’s do that first.

import mysql.connector

Now, let’s see how we can create a connection. We can create a connection using the connect() method available in the mysql.connector module and pass the values for hostname, username, and password.

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password"
)
print(mydb)

Now, go to your command prompt and run this Python file.

python hellomysql.py

If you get a message like this, the MySQL connection is established.

Creating a Database

Now, let’s see how we can create a new database with Python.

To create a database, you can create a cursor object by calling the cursor() method.

import mysql.connector
mydb = mysql.connector.connect(host="localhost",
  user="username",
  password="password")

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE pydatabase")

Now, run the python code. If you don’t see any errors, the database was successfully created.

Now, if you want to see the databases that are existing in the system, you can run the following code. We can use a for loop to print all the database names one by one.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password"
)

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
  print(x)

Now that you have learned how to create a database, let’s see how we can directly connect to a database and perform operations on that database. To connect to a database, you can pass the database name in the connect method.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="pydatabase"
)
print(mydb)

Now that we have connected to a database, we can create tables and do all the activities inside this database.

How To Execute Basic Queries using Python and MySQL

Let’s see how we can execute SQL commands and do database management using Python and MySQL. You can execute all the SQL commands using the execute() method.

Creating Tables

Let’s see an example of creating a table.

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="pydatabase"
)

mycursor = mydb.cursor()
mycursor.execute('Create table Student(RollNo int(5), Name varchar(30))')

Now, run the Python code. Let’s check if the table is created.

import mysql.connector
mydb = mysql.connector.connect(host="localhost",
  user="username",
  password="password",
  database="pydatabase")

mycursor = mydb.cursor()

mycursor.execute('SHOW TABLES')
for x in mycursor:
  print(x)

You can see that the student table was created.

Inserting Data

Let’s see how we can insert data into the table that we have created.

import mysql.connector
mydb = mysql.connector.connect(host="localhost",username="username",password="password",database="pydatabase")

mycursor = mydb.cursor()

sql = 'INSERT INTO student (RollNo, Name) VALUES (%s, %s)'
val = ("1", "John")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

When you run the code, you can see that the row has been inserted into the table.

If you want to insert multiple rows, you can do that in the following way.

import mysql.connector
mydb = mysql.connector.connect(host="localhost",username="username",password="password",database="pydatabase")

mycursor = mydb.cursor()

sql = 'INSERT INTO student (RollNo, Name) VALUES (%s, %s)'
val = [("2", "Bob"),("3","Elisa"),("4", "Maxx")]
mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Select Data From a Table

Now, let’s see how we can retrieve data from the table.

import mysql.connector
mydb = mysql.connector.connect(host="localhost",username="username",password="password",database="pydatabase")

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

for x in myresult:
  print(x)

You can see the output when you run the Python code.

Final Thoughts

Now you know how to connect to a MySQL database using Python and do basic database operations. If you want to do more advanced operations, you can learn more and implement anything you want. You can check out this article for some more information about Python and MySQL.

I hope this article gave you the basics that you want to know to set up a MySQL database and write queries using Python. Thanks for reading!