MySQL with Python: pip install mysql-connector-python

Did You Know!
The name MySQL owes its name to co-founder Michael “Monty” Widenius’s daughter My.

Hey, Coders, met again! You may have read the fact, So, today we are going to learn how to control MySQL and its databases using python. It is as simple as the MySQL syntax so just keep on reading and comments are always open for your queries.
And yes, you must have MySQL installed on your system for this tutorial. If you don't know how to do that just comment I will write a new tutorial on how to install it.

So let's start coding...

Table of Contents

Introduction and necessary things

Did You Know! MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson, and Michael “Monty” Widenius.

So let's first begin with an introduction of MySQL and install the necessary python modules. If you are not interested in theory you can directly jump to the coding area but remember theories to coding are like IDE's for languages.

MySQL

MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language. A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database. source: Wikipedia

That's just some information from Wikipedia. I just copied it.

Now we can start installing the python module or should I introduce python also? I don't think anyone needs that. Python is so popular because it's one of my favorite languages.

Installing mysql-connector-python

Python will need a driver to access the MySQL databases. And for this tutorial, we are going to use the mysql-connector driver. We are going to use the pip package manager to install the mysql-connector-python package and pip is always installed with python so you don't have to do anything extra work.

As a developer, you should know how to install a python module. but let me guide you. Just follow the steps...

1. Open Powershell/cmd or any terminal you have in your OS
2. Type in the following command and hit enter...

   # Command to install mysql-connector-python
   C:\Users\Username>python -m pip install mysql-connector-python
       
   # OR go to this folder and hit the same command
   C:\Users\Username\AppData\Local\Programs\Python\Python38\Scripts>python -m pip install mysql-connector-python

3. Try running import mysql.connector in python. If you don't get any error then the driver is installed successfully.
4. Now you are ready to code

Did You Know! MariaDB is named after Monty’s younger daughter Maria, similar to how MySQL is named after his other daughter My.

Code

In this tutorial, we will learn how to create databases, tables, execute commands in the MySQL command line with python, and much more...

Connecting to database

First, we have to connect with our mysql database to access its data and also modify it. Use the username and password to connect to the database you have set while installing mysql on your system.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password"
)

print(mydb)

# OUTPUT
<mysql.connector.connection_cext.CMySQLConnection object at 0x00000260E6CB70D0>

Now we can start querying into our database.

Creating a Database

A database is a structured and well-organized collection of data stored over the network or locally. To create a database in mysql we use the CREATE DATABASE <databasename> query.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE <databasename>")  # e.g CREATE DATABASE MYDB

So same here run it ⇢ no error ⇢ Success!.

Now let's check databases already present on the server and let's learn a shortcut to access a database without using the USE <databasename> query.

Checking the Databases

To check the databases already there in mysql, we use the SHOW DATABASES query.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for db in mycursor:
  print(db)
  

You can specify the database to use while making the connection also,

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="<databasename>"
)

Creating Tables

To create a table we use the CREATE TABLE query.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="<databasename>"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE employees (eno INT, ename VARCHAR(20),dept VARCHAR(255))")

You can use the SHOW TABLES to list already existing tables in the database.

Inserting into Table

A table cannot be kept empty, so to fill(or insert) data into this table we use the INSERT INTO query and provide the table name then the values to be inserted into the table.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="<databasename>"
)

# CREATE A CURSOR OBJECT AS mycursor
mycursor = mydb.cursor()

# QUERY TO BE EXECUTED
sql = "INSERT INTO employee (eno, ename, dept) VALUES (%s, %s)"
val = (101, "MR. INDIA", "IT")

# EXECUTING THE QUERY
mycursor.execute(sql, val)

# SAVING THE DATA TO TABLE
mydb.commit()

print(mycursor.rowcount, "record inserted.")

Important!:
mydb.commit() is required to make the changes, otherwise, no changes are made to the table.

To insert more than one record instead of a single tuple val you can store a list of tuples in the same variable and other things in the code remains the same. like...

val = [
  (101, 'Labhe', 'IT'),
  (102, 'Mavya', 'CS'),
  (103, 'Bobya', 'IT'),
  (104, 'Mowgli', 'GRAPHICS'),
  (105, 'Fe', 'CS'),
  (106, 'Nilu', 'IT'),
  (107, 'Zapya', 'DESIGN'),
  (108, 'Doya', 'IT'),
  (109, 'Buddha', 'CS'),
  (110, 'Butlya', 'CS'),
]

Display Data

Now the data is inserted to see it, we use the SELECT statement. We will also use the fetchall() method to fetch all the rows returned by the select statement.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="<databasename>"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM employee")

myresult = mycursor.fetchall()

for rows in myresult:
  print(rows)

This will print the data in the command line as tuples one by one.

Instead, fetchall() you can use the fetchone() method if you want to fetch the first record only.

The SELECT query can be used as we do in mysql to fetch all columns(*) or particular columns(columnname1, columnname2,...).
Similarly, You can use the queries as it is from mysql in python like applying a WHERE clause, wildcard characters(%)[Used to select the records that start, includes, or end with a given letter or phrase.], GROUP BY, DELETE, DROP, UPDATE, LIMIT or JOIN.

All above are just simple SQL queries and you know how to use them and what their syntax is like so not explaining them here.

Preventing MySQL Injections

With great data, there is also a great risk. Hackers are always trying to collect data from such databases illegally. So to make a secure database there are certain measures to follow and a general one is explained here.

When we take the query values from the user they should be escaped. Escaping is a method that allows us to tell a computer to do something special with the text we supply or to ignore the special function of a character.

This is useful to prevent MySQL Injection. SQL Injection happens when you ask a user for information, such as their name, and they instead provide you a MySQL statement that you inadvertently run on your database. Never trust a user's data; only process it after it has been validated, which is usually done by pattern matching or escaping the values. for example, see the following code...

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="<databasename>"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM employee WHERE eno = %s" # escaping query values
adr = ("108", )

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

In the above code, we used the placeholder %s to escape the values.

So that is how we manage SQL with python and securely control our database. If you have any queries you can ask in the comments or contact me directly by email.

Keep Reading. Keep Learning Devs✌.

Sayonara! 👋