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 fine
<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")

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)

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" )

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))")

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)
('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))")

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")
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")
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)
(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)
('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)
('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)
(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()

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)
('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.

Receive Daily Newsletters

Leave a Reply