Python Tutorial: How to Connect MySQL Database in Python

How to Connect MySQL Database in Python - CtechF

Python is a scripting language, SQL is a standard language for managing data’s in databases. In this tutorial, You Learn How to Connect MySQL Database in Python

Read first:
Python Tutorial: Introduction for Beginners (Part 1)
Python Tutorial: Introduction for Beginners (Part 2)
SQL Tutorial: Introduction for beginners (Basic)

1) What is Python?

CtechF - Python

Python is a scripting language and that is used for creating engineering-analysis tools, web development (server-side), software development, system scripting, and animation software. It designed by Guido van Rossum and first released in 1991. It’s an interpreted, High-level. General-purpose language. Python runs on Windows, Mac OS, and Linux.

Python Tutorial: Introduction for Beginners (Part 1)

2) What is SQL?

mysql tutorial

SQL (Structured Query Language) is a standard language for storing, managing and retrieving data in databases. SQL was developed at IBM by Donald Chamberlin and Raymond Boyce. and first released in 1991. SQL became a standard of the ANSI (American National Standards Institute) in 1986.

SQL Tutorial: Introduction for beginners (Basic)

3) How to Connect MySQL Database in Python

Install MySQL Driver

We use MySQL connector for this tutorial. Before starting this tutorial, you should have MySQL installed on your PC.

CtechF - How to install XAMPP - Screenshot

How to install MySQL on Windows 10 (XAMPP)

Install PIP

To install PIP We need to download get-pip.py. Open CMD and enter the following command. It will download get.pip.py python file.
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
Then run the following.
python get-pip.py

You can use the following pip command to install MySQL Connector. Open CMD and enter the following command. It will download mysql-connector (14 MB)

pip install mysql-connector
or
pip install mysql-connector-python==version-of-the-moduler (8.0.16)

How to Connect MySQL Database in Python - CtechF

* Python/MySQL – Create MySQL Connection

import mysql.connector
db = mysql.connector.connect(
  host = "localhost", #hostname
  user = "root", #MySQL username
  password = "" #MySQL password
)

* Python/MySQL – Creating a Database

import mysql.connector
db = mysql.connector.connect(
  host = "localhost", #hostname
  user = "root", #MySQL username
  password = "" #MySQL password
)
mc = db.cursor()

query = "CREATE DATABASE cstudents"
mc.execute(query)

* Python/MySQL – Show Databases

import mysql.connector
db = mysql.connector.connect(
  host = "localhost", #hostname
  user = "root", #MySQL username
  password = "" #MySQL password
)
mc = db.cursor()

query = "SHOW DATABASES"
mc.execute(query)

for name in mc:
  print(name)

* Python/MySQL – Creating a Table

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents" #Database name
)
mc = db.cursor()

query = "CREATE TABLE namelist(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50), 
  age INT,
  country VARCHAR(25),
  note TEXT )"
mc.execute(query)

* Python/MySQL – Show Table Data

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SHOW TABLES"
mc.execute(query)
for data in mc:
  print(data)

* Python/MySQL – Insert Into Table

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "INSERT INTO namelist (name, age, country, note) VALUES (%s, %s, %s, %s)"
values = [
  ('Chathura', '20', 'LK', 'Student'),
  ('Deshan', '20', 'LK', 'Student'),
  ('Eliot', '20', 'US', '2018'),
  ('Peter', '20', 'AU', 'New'),
  ('Ross', '20', 'CA', 'New'),
  ('Chan', '20', 'JP', 'Student'),
  ('Tekla', '20', 'JP', 'Note'),
  ('Melo', '20', 'O', 'Student'),
]

mc.executemany(query, values)
db.commit()

print("Record Added!")

* Python/MySQL – Select

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SELECT * FROM namelist"
mc.execute(query)
result = mc.fetchall()

for data in result:
  print(data)

* Python/MySQL – Select (First Row)

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SELECT * FROM namelist LIMIT 1"
mc.execute(query)
result = mc.fetchall()

for data in result:
  print(data)

* Python/MySQL – Select (Where)

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SELECT * FROM namelist WHERE country ='LK'"
mc.execute(query)
result = mc.fetchall()

for data in result:
  print(data)

* Python/MySQL – Select (Order By)

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SELECT * FROM namelist ORDER BY name" #Default ASC
mc.execute(query)
result = mc.fetchall()

for data in result:
  print(data)

* Python/MySQL – Select (Order By – DESC)

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "SELECT * FROM namelist ORDER BY name DESC"
mc.execute(query)
result = mc.fetchall()

for data in result:
  print(data)

* Python/MySQL – Delete

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "DELETE FROM namelist WHERE name = 'Tekla'"
mc.execute(query)
db.commit()

print("Record Deleted!")

* Python/MySQL – Delete a Table

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "",
  database = "cstudents"
)
mc = db.cursor()

query = "DROP TABLE IF EXISTS namelist"
mc.execute(query)

print("Table Deleted!")

* Python/MySQL – Update

import mysql.connector
db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  passwd = "",
  database = "cstudents"
)
mc = db.cursor()

query = "UPDATE namelist SET note = 'Updated', name = 'Tro' WHERE name = 'Deshan'"
mc.execute(query)
db.commit()

print("Updated")

Hello Friends,

Hope this post (How to Connect MySQL Database in Python will help you to learn something, If you enjoy my work then please share my posts with your friends and anyone who might be interested in programming and don’t forget to subscribe my mailing list.

If you had any problem with this post, please do mention it in the comment section.

Like ctechf Facebook fan page : https://www.facebook.com/ctechf

Also Read :

Sources : 
https://pixabay.com/ (images)
https://pngimg.com (images)
ICT Books (Details)

Liked it? Take a second to support CtechF on Patreon!

1 thought on “Python Tutorial: How to Connect MySQL Database in Python”

Leave a Reply

489 Shares
Share
Tweet
Share
Pin
Share