MySQL with Python

In this article you will learn to create databases, manipulate databases, and will also learn some operations on handling databases in MySQL with Python. You need to download and install MySQL from here, and after installing MySQL you also need to server to use it with Python. mysql.connector will provide you the features as a server to your python script, you can download it here.

Connecting MySQL with Python

If you have installed MySQl in your systems, you must have your username and password, I suggest you to never forget your username and password, now we need to use these in our python script to connect it with MySQL.

## Connecting to the database
## importing 'mysql.connector' as mysql for convenient
import mysql.connector as mysql

## connecting to the database using 'connect()' method
## it takes 3 required parameters 'host', 'user', 'passwd'
db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "aman"
)

print(db) # it will print a connection object if everything is fineCode language: Python (python)

<mysql.connector.connection_cext.CMySQLConnection object at 0x0000020C26A84C50>

In this way we can connect MySQL with Python. Now let’s move further with creating databases in MySQL with Python.

Creating Database

In MySQL, when we need to create a Database we use a command CREATE DATABASE DATABASE NAME, Now lets see how we can create a database using python:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "aman"
)

## creating an instance of 'cursor' class which is used to execute the 'SQL' statements in 'Python'
cursor = db.cursor()

## creating a databse called 'datacamp'
## 'execute()' method is used to compile a 'SQL' statement
## below statement is used to create the 'thecleverprogrammer' database
cursor.execute("CREATE DATABASE thecleverprogrammer")Code language: Python (python)

Make sure whenever you create a new database, it should be with a unique name, otherwise it will give error. Now let’s see all the databases already present in my MySQL. To see all the databases that we have created before SHOW DATABASES command is used:

cursor.execute("SHOW DATABASES")

## 'fetchall()' method fetches all the rows from the last executed statement
databases = cursor.fetchall() ## it returns a list of all databases present

## printing the list of databases
print(databases)

## showing one by one database
for database in databases:
    print(database)Code language: Python (python)

Creating Tables in MySQL Database with Python

We make tables in a database to store information. But to create a table we need to select a database from all the databases that we have build in our system. So I will select the database that I just created above as “thecleverprogrammer”:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "aman",
    database = "thecleverprogrammer"
)Code language: Python (python)

Now we will Create a New Table in our database, in MySQL while creating a table CREATE TABLE TABLE NAME command is used:

ursor = db.cursor()

## creating a table called 'users' in the 'datacamp' database
cursor.execute("CREATE TABLE users (name VARCHAR(255), user_name VARCHAR(255))")Code language: Python (python)

If you don’t know what is a VARCHAR, it means variable character, it is a datatype used in MySQL. We need to give number of predefined spaces, that we think will be the length of the each entry in the table.

The most commonly used datatypes used in MySQL are – Integer, Char, Varchar, Float, and Date. So now we have created a table named as “users”. Let’s see how can see see the table:

cursor.execute("SHOW TABLES")

tables = cursor.fetchall() ## it returns list of tables present in the database

## showing all the tables one by one
for table in tables:
    print(table)Code language: Python (python)

('users',) 

Primary Key

Primary key is a column in a table, that can become a column of unique values in the table, for example – Admission number, enrollment number, id number, and so on. Now let’s see how we can make a primary key in a table:

cursor = db.cursor()

## first we have to 'drop' the table which has already created to create it again with the 'PRIMARY KEY'
## 'DROP TABLE table_name' statement will drop the table from a database
cursor.execute("DROP TABLE users")

## creating the 'users' table again with the 'PRIMARY KEY'
cursor.execute("CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), user_name VARCHAR(255))")Code language: Python (python)

Inserting Data in a Table

To insert data into a table INSERT INTO Table Name(Column Name) VALUES (Entry) command is used. Now let’s see how we can execute this statement with python:

cursor = db.cursor()

## defining the Query
query = "INSERT INTO users (name, user_name) VALUES (%s, %s)"
## storing values in a variable
values = ("Hafeez", "hafeez")

## executing the query with values
cursor.execute(query, values)

## to make final output we have to run the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "record inserted")Code language: Python (python)

1 record inserted

So, the above code inserted one record in the table, lets see how we can insert multiple records in the table:

cursor = db.cursor()

## defining the Query
query = "INSERT INTO users (name, user_name) VALUES (%s, %s)"
## storing values in a variable
values = [
    ("Peter", "peter"),
    ("Amy", "amy"),
    ("Michael", "michael"),
    ("Hennah", "hennah")
]

## executing the query with values
cursor.executemany(query, values)

## to make final output we have to run the 'commit()' method of the database object
db.commit()

print(cursor.rowcount, "records inserted")Code language: Python (python)

4 records inserted

Select Data From Table

To select the data from the table SELECT column name FROM table name command is used. But if you want to see all the data rather than a particular column then SELECT * FROM table name command is used. Now let’s have a look at all the records in the table:

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)Code language: Python (python)

(1, 'Hafeez', 'hafeez') 
(2, 'Peter', 'peter') 
(3, 'Amy', 'amy') 
(4, 'Michael', 'michael') 
(5, 'Hennah', 'hennah')

To select data from a particular column:

cursor = db.cursor()

## defining the Query
query = "SELECT user_name FROM users"

## getting 'user_name' column from the table
cursor.execute(query)

## fetching all usernames from the 'cursor' object
usernames = cursor.fetchall()

## Showing the data
for username in usernames:
    print(username)Code language: Python (python)

('hafeez',) 
('peter',) 
('amy',) 
('michael',) 
('hennah',)

Select data from more than one column:

cursor = db.cursor()

## defining the Query
query = "SELECT name, user_name FROM users"

## getting 'name', 'user_name' columns from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
data = cursor.fetchall()

## Showing the data
for pair in data:
    print(pair)Code language: Python (python)

('Hafeez', 'hafeez') 
('Peter', 'peter') 
('Amy', 'amy') 
('Michael', 'michael') 
('Hennah', 'hennah')

Where Command in MySQL with Python

The Where command is one of the most used commands in MySQL. It is used to select the data on the basis on any condition. While doing analysis Where command is highly used as compared to all other commands. Let’s have a look how we can use a Where Command:

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users WHERE id = 5"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)Code language: Python (python)

(5, 'Hennah', 'hennah')

You can use the the Where command by specifying the conditions on the basis of your data.

Update Command

The Update command is used to update the records of your table. Let’s have a look how we can use an Update command:

cursor = db.cursor()

## defining the Query
query = "UPDATE users SET name = 'Kareem' WHERE id = 1"

## executing the query
cursor.execute(query)

## final step to tell the database that we have changed the table data
db.commit()Code language: Python (python)

Now let’s have a look at the data whether the records are updated or not:

cursor = db.cursor()

## defining the Query
query = "SELECT * FROM users"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

## Showing the data
for record in records:
    print(record)Code language: Python (python)

('Kareem', 'hafeez') 
('Peter', 'peter') 
('Amy', 'amy') 
('Michael', 'michael') 
('Hennah', 'hennah')

Also, Read: Image Classification with Neural Networks.

These were the most useful commands if you are looking to create a database using MySQL with Python. Now you are ready to use this knowledge in creating your own databases. I hope you liked this article. Feel free to ask your valuable questions in the comments section below.

Follow Us: