Python Mysql Database For Beginners

In this article we are going to learn about Python Mysql Database For Beginners so when we are going to work with Python Mysql Database first we need to install MySQLdb. also we are using WAMP Server in this article .

What is MySQL Database ?

MySQL is an open source relational database management system (RDBMS) that is used to store, manage and organize data. it is one of the most popular databases used in web applications, powering some of the most visited websites in the world.

MySQL allows users to create and manipulate databases, tables and other database objects, as well as perform operations such as querying, indexing and sorting data. it uses Structured Query Language (SQL) to interact with the database and retrieve data.

MySQL supports different operating systems and programming languages and this makes it powerful database management system that can be used for various types of applications. it is also known for its speed and scalability and this makes it popular choice for applications that require fast data retrieval and processing.

In result we can say that MySQL is reliable and widely used database system that is suitable for small to large scale applications, from personal websites to enterprise level applications.

What is Python MySQLdb ?

Python MySQLdb is Python library that provides Python interface to MySQL databases. it allows Python programs to interact with MySQL databases by providing different Python functions that implement different SQL commands used to manipulate data in MySQL databases.

MySQLdb is built on top of the MySQL C API and therefore provides low level interface to MySQL. it allows you to execute SQL queries, manage transactions and handle exceptions. the library also supports prepared statements and it allows you to execute SQL statements with placeholders for parameterized queries.

Python MySQLdb is compatible with Python 2 and Python 3, and it is an alternative to other MySQL libraries such as PyMySQL and mysql-connector-python.

What is Wamp Server ?

WampServer is software stack that allows users to create and manage local web development environments on their Windows computers. name “Wamp” is an acronym for the components included in the stack: Windows, Apache, MySQL, and PHP.

WampServer bundles together these components into an easy to install package and it provides users with a ready web server environment that can be used for developing and testing web applications on their local machine. it includes the following components:

Apache: An open source web server software that is used to serve web pages to clients over HTTP.
MySQL: An open source relational database management system that is used to store and manage data.

PHP: A popular programming language that is used to create dynamic web pages and web applications.

WampServer also includes phpMyAdmin, it is web based tool that allows users to manage their MySQL databases through graphical user interface.

WampServer is popular choice for web developers who work on Windows, as it provides an easy way to set up a local development environment that includes all the necessary components for developing web applications.

Also you can watch the complete video for this article

Before starting of our main topic about Python Mysql Database For Beginners, let me have a few words about MySQLdb.

How to install MySQLdb ?

Before proceeding, you make sure you have MySQLdb installed on your machine.

To install MySQLdb module, use the following command

pip install MySQL-python

1	pip install MySQL-python

Also you can use Mysqlclient.

 pip install mysqlclient.

1	pip install mysqlclient.

Database Connection

Before connecting to a MySQL database, make sure of the followings, you have created a database dbtest on your wamp server.

This is the code for database connection with Mysql.

import MySQLdb as mdb

DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"

try:
    db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print("Database Connected Successfully")

except mdb.Error as e:
    print("Database Not Connected Successfully")

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

try:

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME)

print("Database Connected Successfully")

except mdb.Error as e:

print("Database Not Connected Successfully")

This is the output

Creating Mysql Tables In Python

In this section i want to show you how you can create tables in Mysql With Python Programming Language.

import MySQLdb as mdb

DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"
try:
    db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME)
    print("Database Connected Successfully")

    cur = db.cursor()

    # we are droping the table if it already exists
    cur.execute("DROP TABLE IF EXISTS Employee")

    sqlquery = """
    CREATE TABLE Employee (
    Name CHAR(20) NOT NULL,
    Email CHAR(20),
    Age INT
    )

    """
    cur.execute(sqlquery)
    print("Table Created Successfully")
    db.close()
except mdb.Error as e:
    print("Database Not Connected Successfully")

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

try:

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME)

print("Database Connected Successfully")

cur = db.cursor()

# we are droping the table if it already exists

cur.execute("DROP TABLE IF EXISTS Employee")

sqlquery = """

CREATE TABLE Employee (

Name CHAR(20) NOT NULL,

Email CHAR(20),

Age INT

)

"""

cur.execute(sqlquery)

print("Table Created Successfully")

db.close()

except mdb.Error as e:

print("Database Not Connected Successfully")

So in the above query I want to create a table with the fields of Name, Email and Age.

This is the output

Check the database for the table

Inserting Data to Mysql Tables In Python

So we have created our employee table now it is time to insert some data in this table, this is the query for inserting data, there are two ways that you can insert data , this is the first way and it is not saver.

import MySQLdb as mdb

DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"
db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )
cur = db.cursor()
querysql = """
INSERT INTO Employee (Name, Email, Age) VALUES ('John', 'john@gmail.com', '25')
"""
try:
    cur.execute(querysql)
    #commit changes in the database
    db.commit()
    print("Data Inserted Successfully")
except:
    db.rollback()
    #roolback if there is an error

db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

querysql = """

INSERT INTO Employee (Name, Email, Age) VALUES ('John', 'john@gmail.com', '25')

"""

try:

cur.execute(querysql)

#commit changes in the database

db.commit()

print("Data Inserted Successfully")

except:

db.rollback()

#roolback if there is an error

db.close()

Data inserted successfully

Python Mysql Database — Data Inserted Python

Now check the database

Second way inserting data is saver against SQL Injections because we are using placeholders.

import MySQLdb as mdb

DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"
db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )
cur = db.cursor()
querysql = """
INSERT INTO Employee (Name, Email, Age) VALUES (%s,%s,%s)
"""
val = ("Jamil", "ja@gmail.com", 45)
try:
    cur.execute(querysql, val)
    #commit changes in the database
    db.commit()
    print("Data Inserted Successfully")
except:
    db.rollback()
    #roolback if there is an error

db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

querysql = """

INSERT INTO Employee (Name, Email, Age) VALUES (%s,%s,%s)

"""

val = ("Jamil", "ja@gmail.com", 45)

try:

cur.execute(querysql, val)

#commit changes in the database

db.commit()

print("Data Inserted Successfully")

except:

db.rollback()

#roolback if there is an error

db.close()

So in the above code we have some changes in the query instead of hard coded values , we have added palceholders.

Python Mysql Database Reading Data

So in this section we are going to learn about reading data from Mysql Database with Python Programming Language.

import MySQLdb as mdb

DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"


db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )
cur = db.cursor()
sqlquery = "SELECT * FROM Employee"

try:
    cur.execute(sqlquery)
    #fetch all data
    results = cur.fetchall()
    for row in results:
        name = row[0]
        email = row[1]
        age = row[2]

        print("Name : " + name)
        print("Email : " + email)
        print("Age : " + str(age)


except:
    print("Unable To Fetch Data From Database")
db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

sqlquery = "SELECT * FROM Employee"

try:

cur.execute(sqlquery)

#fetch all data

results = cur.fetchall()

for row in results:

name = row[0]

email = row[1]

age = row[2]

print("Name : " + name)

print("Email : " + email)

print("Age : " + str(age)

except:

print("Unable To Fetch Data From Database")

db.close()

Python Mysql Database Updating Data

Now we are going to learn about updating data, there two ways for updating , the first way is not saver and the second way is saver.

import MySQLdb as mdb


DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()


sqlquery = "UPDATE Employee SET Name = 'Bob' WHERE Name = 'Parwiz'"



try:
    cur.execute(sqlquery)
    db.commit()
    print("Data Updated Successfully")

except:
    print("Unable To Update The Data")


db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

sqlquery = "UPDATE Employee SET Name = 'Bob' WHERE Name = 'Parwiz'"

try:

cur.execute(sqlquery)

db.commit()

print("Data Updated Successfully")

except:

print("Unable To Update The Data")

db.close()

Data updated successfully

Check your database

Second way for updating data, it is saver against SQL injections because we are using placeholders.

import MySQLdb as mdb


DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()


sqlquery = "UPDATE Employee SET Name = %s WHERE Name = %s"

value = ("Tom", "Bob")


try:
    cur.execute(sqlquery, value)
    db.commit()
    print("Data Updated Successfully")

except:
    print("Unable To Update The Data")

db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

sqlquery = "UPDATE Employee SET Name = %s WHERE Name = %s"

value = ("Tom", "Bob")

try:

cur.execute(sqlquery, value)

db.commit()

print("Data Updated Successfully")

except:

print("Unable To Update The Data")

db.close()

So in the above code we have just brought some changes in the query. Instead of adding hard coded value we have added placeholders.

Python Mysql Database Deleting Data

In this section we are going to learn about deleting data.

import MySQLdb as mdb


DBNAME = "dbtest"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"


db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )


cur = db.cursor()


deletequery = "DELETE FROM Employee WHERE Name = 'Bob'"

try:
    cur.execute(deletequery)
    db.commit()
    print("Data Deleted Successfully")


except:
    print("Unable To Delete The Data")


db.close()

import MySQLdb as mdb

DBNAME = "dbtest"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )

cur = db.cursor()

deletequery = "DELETE FROM Employee WHERE Name = 'Bob'"

try:

cur.execute(deletequery)

db.commit()

print("Data Deleted Successfully")

except:

print("Unable To Delete The Data")

db.close()

Data deleted successfully

Check your database

Also you can check more Python articles in the below links

Complete PyQt5 GUI Development Course

Subscribe and Get Free Video Courses & Articles in your Email

What is MySQL Database ?

What is Python MySQLdb ?

What is Wamp Server ?

How to install MySQLdb ?

Database Connection

Creating Mysql Tables In Python

Inserting Data to Mysql Tables In Python

Python Mysql Database Reading Data

Python Mysql Database Updating Data

Python Mysql Database Deleting Data

Leave a Comment Cancel reply