Python MySQL Database with MySQLdb

In this Python MySQL tutorial we are going to learn about Python MySQL Database with MySQLdb ,we will learn how you can connect your Python code with MySQL Database, how you can insert data with python mysql, how you can update, select or delete data in Python with Mysql Database, we are going to use MySQLdb or MysqlClient for our database operations in Python.

Learn More on Python

What is MySQLdb ?

MySQLdb is an interface to the popular MySQL database server that provides the Python database API. originally MySQLdb was for Python 2.7, but there another version of MySQLdb that is called MysqlClient, and it supports Python 3.0, at the writing of this article MysqlClient supports Python 3.0 up to 3.8, you can check their Documentation for more updates.

Installation

You can simply use pip for the installation.

pip install mysqlclient

1	pip install mysqlclient

Python Database Connection

All right, after installation we are going to create a database in the Wamp Server, you need to download and install Wamp Server in your computer. iam going to create a database at name of geekscoders.

So now this is the code for database connection in python with MySQLdb.

import MySQLdb as mdb

DBNAME = "geekscoders"
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 = "geekscoders"

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")

In the above code first of all we have imported our Mysqldb library, and after that you need to write your database name, host, password and username. you can use MySQLdb.connect(), for database connection, and their you need to pass your database requirements like dbname, dbhost,dbuser and dbpass.

Run the code and this is the result.

Python MySQL Creating Tables

All right we have our database and we have connected our python code to the database, now we want to create table in our database, right now we don’t have any table in the database. this is the code for creating table in our database, basically we are going to create a table of Users in our database, and it will have three fields like name, email and age.

import MySQLdb as mdb

DBNAME = "geekscoders"
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 Users")

    sqlquery = """
    CREATE TABLE Users (
    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 = "geekscoders"

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 Users")

sqlquery = """

CREATE TABLE Users (

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")

Run the code and now check your Wam Server, you have the table with the fields in your database.

Python MySQL Inserting Data

Let’s insert some data in our database table, right now we don’t have any data in our table, you can use this code for inserting data. there are two ways that you can insert data , this is the first way and it is not saver against SQL Injection.

import MySQLdb as mdb

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

db.close()

import MySQLdb as mdb

DBNAME = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

querysql = """

INSERT INTO users (Name, Email, Age) VALUES ('geekscoders', 'myemail@gmail.com', '25')

"""

try:

cur.execute(querysql)

#commit changes in the database

db.commit()

print("Data Inserted Successfully")

except:

db.rollback()

print("ERror")

#roolback if there is an error

db.close()

You can see in the above code we have used Inserting query for adding data in our database table.

querysql = """
INSERT INTO users (Name, Email, Age) VALUES ('geekscoders', 'myemail@gmail.com', '25')
"""

querysql = """

INSERT INTO users (Name, Email, Age) VALUES ('geekscoders', 'myemail@gmail.com', '25')

"""

After query you need to execute the query, and commit the changes in the database.

cur.execute(querysql) 
db.commit()

1 2	cur.execute(querysql) db.commit()

This is the result and we have the data in our database table.

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

import MySQLdb as mdb

DBNAME = "geekscoders"
DBHOST = "localhost"
DBPASS = ""
DBUSER = "root"
db = mdb.connect(DBHOST, DBUSER, DBPASS, DBNAME )
cur = db.cursor()
querysql = """
INSERT INTO Users (Name, Email, Age) VALUES (%s,%s,%s)
"""
val = ("Parwiz", "par@gmail.com", 27)
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 = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

querysql = """

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

"""

val = ("Parwiz", "par@gmail.com", 27)

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()

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

Now we have another data in our table.

Python Mysql Database Selecting Data

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

import MySQLdb as mdb

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


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

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 = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

sqlquery = "SELECT * FROM Users"

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()

So after database connection we need to create our query with cursor object.

cur = db.cursor()
sqlquery = "SELECT * FROM Users"

1 2	cur = db.cursor() sqlquery = "SELECT * FROM Users"

You need to execute your query, also we for selecting the data from database you can use fetchall() function. if you want to fetch one row you can use fetchone().

cur.execute(sqlquery)
results = cur.fetchall()

1 2	cur.execute(sqlquery) results = cur.fetchall()

Run the code and this is the result.

Python Mysql Database Updating Data

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

import MySQLdb as mdb


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

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

cur = db.cursor()


sqlquery = "UPDATE users SET Name = 'updatedname' WHERE Name = 'geekscoders'"



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

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


db.close()

import MySQLdb as mdb

DBNAME = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

sqlquery = "UPDATE users SET Name = 'updatedname' WHERE Name = 'geekscoders'"

try:

cur.execute(sqlquery)

db.commit()

print("Data Updated Successfully")

except:

print("Unable To Update The Data")

db.close()

If you run the code this is the result.

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

import MySQLdb as mdb


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

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

cur = db.cursor()


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

value = ("newupdated", "updatedname")


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 = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

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

value = ("newupdated", "updatedname")

try:

cur.execute(sqlquery, value)

db.commit()

print("Data Updated Successfully")

except:

print("Unable To Update The Data")

db.close()

Python Mysql Database Deleting Data

All right guys now we want to delete our data from database table.

import MySQLdb as mdb


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


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


cur = db.cursor()


deletequery = "DELETE FROM users WHERE Name = 'newupdated'"

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


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


db.close()

import MySQLdb as mdb

DBNAME = "geekscoders"

DBHOST = "localhost"

DBPASS = ""

DBUSER = "root"

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

cur = db.cursor()

deletequery = "DELETE FROM users WHERE Name = 'newupdated'"

try:

cur.execute(deletequery)

db.commit()

print("Data Deleted Successfully")

except:

print("Unable To Delete The Data")

db.close()

Now first record is deleted.

Learn More on Python

What is MySQLdb ?

Installation

Python Database Connection

Python MySQL Creating Tables

Python MySQL Inserting Data

Python Mysql Database Selecting Data

Python Mysql Database Updating Data

Python Mysql Database Deleting Data

Leave a Comment Cancel reply