PYTHON MYSQL DATABASE

Previous
Next

In this tutorial, we are going to learn about Python MySQL database. Since, it’s important to have a database for storing data and keep a record of all the information that you want to contain.

Python supports the integration with MySQL through many databases modules. For this tutorial, we are going to install and import the pymysql module to work with our database.



Installing Python MySQL

In order to connect Python to a database we need to have a mysql connecter package installed. To install this connector we need to go inside the ‘python’ official directory and find the scripts folder. For example, the directory location on my system looks like this: First of all we have to install python mysql connector package. To install mysql package do the following: go to the Scripts directory in the python and copy the file path: 

C:\Users\Hira\AppData\Local\Programs\Python\Python37-32\Scripts

Next you have to open the command prompt and enter the following command:

cd C:\Users\Hira\AppData\Local\Programs\Python\Python37-32\Scripts

Once you are inside the directory after entering cd and pasting the filepath where your python scripts folder is located; next step is to enter pip install pymysql as shown below:
pip install pymysql
Make sure you have an active internet connection to complete the steps.

Note: Your python folder location could be different than mine.

Create the Database

We have already installed MAMP server for our database, you can install MAMP here. If you already have MySQL installed then you can move on to the other step.

Once you have installed MAMP, simply open it and start the server, your MAMP should look like this:

Python MySQL Database

Once the servers have started, go to your browser and go to this address: http://localhost/phpMyAdmin/

You will see a phpmyAdmin dashboard, now create a new database and name it as pythondatabase.

Python MySQL Database

Connecting Database with Python

Once, you have created the database, simply move to your python IDE and import pymysql in order to build connection with MySQL.

import pymysql


#database connection
db = pymysql.connect(host="localhost",
user="root",
passwd="root",
database="pythondatabase")

#Make sure to initiate the cursor to fetch rows
cursor = db.cursor()

print(db)
# make a habit to close the database connection once you create it 
db.close()

Initially, we have imported pymysql to establish a connection by using pymsql.connect() method which takes four arguments:

  1. hostname
  2. user
  3. password
  4. database name

Next, in order to be able to fetch data of pythondatabase, we have created a cursor() method too. We use cursor to operate on query result in MySQL. Lastly, always make this a good habit to close the connection when you create a connection between a database in python, this is done to save memory load.



Creating a Table

We have successfully established the connection between Python and MySQL database, now it’s time to create a table inside pythondatabase.

import pymysql


#database connection
db = pymysql.connect(host="localhost",
user="root",
passwd="root",
database="pythondatabase")


#Make sure to initiate the cursor to fetch rows
cursor = db.cursor()

#Create a Table
students_info = """CREATE TABLE students_info(
ID INT(20) PRIMARY KEY AUTO_INCREMENT,
NAME  CHAR(20) NOT NULL,
AGE INT(3))"""

cursor.execute(students_info)

print(db)

# make a habit to close the database connection once you create it 
db.close()

You will see that a table students_info is created in our pythondatabase, now you can see that we have stored the table creating query inside the variable ‘students_info’. We have done it so in order to execute it simply through using the cursor.execute(students_info) method.

mysql_table

Insert Queries

Once we have created the table, now it’s time to insert queries inside the table and execute them through the cursor method. You also have to enter the commit()  operation which allows the database to finalize the changes made in it. Once this operation runs then you cannot revert it back.

import pymysql

#database connection
db = pymysql.connect(host="localhost",
  user="root",
  passwd="root",database="pythondatabase")


#Make sure to initiate the cursor to fetch rows
cursor = db.cursor()

#Create a Table
students_info = """CREATE TABLE students_info(
ID INT(20) PRIMARY KEY AUTO_INCREMENT,
NAME  CHAR(20) NOT NULL,
AGE INT(3))"""


# insert queries
row1 = "INSERT INTO students_info(NAME, AGE) VALUES('Smith', 21);"
row2 = "INSERT INTO students_info(NAME, AGE) VALUES('John', 19);"

#executing the quires
cursor.execute(row1)
cursor.execute(row2)

print(db)

#commit the connection
db.commit()


# make a habit to close the database connection once you create it
db.close()



Select in Python MySQL

As you know that we have inserted two rows in the above example. Now if you want to fetch those rows, you can do that as follows:

import pymysql


#database connection
db = pymysql.connect(host="localhost",
  user="root",
  passwd="root",
  database="pythondatabase")

#Make sure to initiate the cursor to fetch rows
cursor = db.cursor()

# fetch all the queries in students_info Table
fetch_queries = "Select * from students_info;"

#queries execution
cursor.execute(fetch_queries)
lines = cursor.fetchall()
for line in lines:
   print(line)

#commit the connection
db.commit()

# make a habit to close the database connection once you create it
db.close()

Output will be:

(1, 'Smith', 21)
(2, 'John', 19)

Update in Python MySQL

Let’s say that you want to update a current name present in one of your rows of table. To do that, you can access the attribute and then update the value of that attribute:

#Update an Attribute Value in the Table
update_name = "UPDATE  students_info SET NAME= 'Lana'  WHERE ID = '1' ;"
cursor.execute(update_name)

Delete in Python MySQL

You can delete an entire query from your table by using the DELETE  statement in MySQL:

#Delete a query
delete_query = "DELETE FROM students_info WHERE ID = '1'; "
cursor.execute(delete_query)



Drop Table in Python MySQL

You can delete an entire table in Python MySQL by using the DROP command as follows:

#Drop an entire table
drop_table = "DROP TABLE IF EXISTS students_info;"
cursor.execute(drop_table)

Once you execute this code, students_info table will be dropped from pythondatabase.

Previous
Next