Coders Camp
Published in

Coders Camp

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.

Get Full Code

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:

Get Full Code

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:

Get Full Code

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:

Get Full Code
('users',)

Primary Key

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:

Get Full Code
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:

Get Full Code
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:

Get Full Code
(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')

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.

--

--

--

We are here to guide you from Hello World to Programming Robots. We hope you will learn a lot in your journey towards programming with us.

Recommended from Medium

Connecting to Microsoft Graph in Azure Automation PowerShell runbooks

Flutter Integration Test: JUnit Report Generation with Gitlab Integration

Elastic Cloud Bootloop: load of Death

CS373 Fall 2021, Week 11: Nitin Jain

How React Native can Reduce App Development Cost?

Couch Co-op NFT Games

ArrayList vs. LinkedList vs. Vector

Timeline Unity: Adding a Panning Effect to a Virtual Camera

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Aman Kharwal

Aman Kharwal

I write stories behind the data📈 | https://www.instagram.com/the.clever.programmer/

More from Medium

Visualizing Netflix Data With Python

Netflix

Data Processing in Python

RPA in Python