MySQL Tutorial 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 the server to use it with Python. mysql.connector will provide you with 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.
In this way, we can connect MySQL with Python. Now let’s move further with creating databases in MySQL with Python.
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:
Make sure whenever you create a new database, it should be with a unique name, otherwise, it will give an 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:
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 built-in our system. So I will select the database that I just created above as “thecleverprogrammer”:
import mysql.connector as mysqldb = 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 data type used in MySQL. We need to give a number of predefined spaces, that we think will be the length of 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:
The 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:
1 record inserted
So, the above code inserted one record in the table, let's see how we can insert multiple records in the table:
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:
(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:
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:
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. You can also follow my website to learn every topic of Machine Learning.
Originally published at https://thecleverprogrammer.com on July 19, 2020.