Python Tutorial - Working with MySQL Database in Python

In this Python Tutorial we are going to learn Working with MySQL Database in Python, if you want to connect your Python code with MySQL Database than you need to use third party library, there are different libraries that you can use, in this Python Tutorial we want to use Mysql Connector. as you know MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

Installation

First of all you need to install mysql connector, you can simply use pip for the installation.

pip install mysql-connector-python

1	pip install mysql-connector-python

Also you need to download and install Wamp Server, because we want to use Wamp Server as virtual server.

1: Creating MySQL Database in Python

So first we want to create a database using Python code, so this is the code for creating mysql database in Python.

import mysql.connector as mc



try:
    mydb = mc.connect(

        host = "localhost",
        user= "root",
        password =""

    )


    dbname = input("Please Enter Database Name : ")

    cursor = mydb.cursor()


    cursor.execute("CREATE DATABASE {} ".format(dbname))
    print("Database Created ")



except mc.Error as e:
    print("Database creation failed")

import mysql.connector as mc

try:

mydb = mc.connect(

host = "localhost",

user= "root",

password =""

)

dbname = input("Please Enter Database Name : ")

cursor = mydb.cursor()

cursor.execute("CREATE DATABASE {} ".format(dbname))

print("Database Created ")

except mc.Error as e:

print("Database creation failed")

First of all you need to import mysql connector for python, after that connect your code with the wamp server like this. give the host and user name, also give the password if you have.

mydb = mc.connect(

        host = "localhost",
        user= "root",
        password =""

    )

mydb = mc.connect(

host = "localhost",

user= "root",

password =""

)

In here we want to get the database name from the user or we can say from the input, and according to that input we want to create our database in mysql.

 dbname = input("Please Enter Database Name : ")

1	dbname = input("Please Enter Database Name : ")

For executing the query you need to create the object of the cursor.

cursor = mydb.cursor()

1	cursor = mydb.cursor()

At the end we need to execute our database creation query.

 cursor.execute("CREATE DATABASE {} ".format(dbname))

1	cursor.execute("CREATE DATABASE {} ".format(dbname))

Run the code give the name of the database that you want.

Python Tutorial - Working with MySQL Database in Python — Python Tutorial – Working with MySQL Database in Python

Check your Wamp Server we have the database.

2: Checking MySQL Database Connection in Python

After creating of the database, now we want to check the database connection, you can use this code for checking mysql database connection in python.

import mysql.connector as mc



try:
    mydb = mc.connect(
        host = "localhost",
        user='root',
        password = "",
        database = "pythondb"


    )

    print("There is a connection")


except mc.Error as e:
    print("Error in database connection ")

import mysql.connector as mc

try:

mydb = mc.connect(

host = "localhost",

user='root',

password = "",

database = "pythondb"

)

print("There is a connection")

except mc.Error as e:

print("Error in database connection ")

Right now we have the correct database name and if we run the code, you will see the connection.

3: Insert Data to Mysql Database in Python

Now we want to insert some data in our MySQL database, first of all you need to create a table in your pythondb database, iam going to call my table name users. and add three fields id,email and password. and this is the code for inserting the data.

import mysql.connector as mc



try:
    mydb = mc.connect(

        host="localhost",
        user="root",
        password="",
        database = "pythondb"

    )


    mycursor = mydb.cursor()


    email = input("Please Enter Your Email : ")
    password = input("Please Enter Your Password : ")


    query = "INSERT INTO users (email, password) VALUES (%s, %s)"
    value = (email, password)

    mycursor.execute(query, value)

    mydb.commit()
    print("Data Inserterd")



except mc.Error as e:
    print("Unable To Insert Data")

import mysql.connector as mc

try:

mydb = mc.connect(

host="localhost",

user="root",

password="",

database = "pythondb"

)

mycursor = mydb.cursor()

email = input("Please Enter Your Email : ")

password = input("Please Enter Your Password : ")

query = "INSERT INTO users (email, password) VALUES (%s, %s)"

value = (email, password)

mycursor.execute(query, value)

mydb.commit()

print("Data Inserterd")

except mc.Error as e:

print("Unable To Insert Data")

We want to insert data according to the user input.

email = input("Please Enter Your Email : ")
password = input("Please Enter Your Password : ")

1 2	email = input("Please Enter Your Email : ") password = input("Please Enter Your Password : ")

This is our query for inserting the data.

query = "INSERT INTO users (email, password) VALUES (%s, %s)"
value = (email, password)

1 2	query = "INSERT INTO users (email, password) VALUES (%s, %s)" value = (email, password)

Also you need to execute your query and commit the database.

mycursor.execute(query, value)
mydb.commit()

1 2	mycursor.execute(query, value) mydb.commit()

Run the code give email and password, this will be the result.

Check Wamp Server and you have the data.

4: Select Data From Mysql Database in Python

In this part we want to select or retrieve our data from mysql database in Python. so this is the code for selecting the data.

import mysql.connector as mc


try:
    dbname = input("Please Enter Database Name : ")
    tablename = input("Please Enter Table Name : ")

    mydb = mc.connect(

        host="localhost",
        user="root",
        password="",
        database = dbname

    )


    mycursor = mydb.cursor()

    mycursor.execute("SELECT * FROM {} ".format(tablename))


    result = mycursor.fetchall()

    for data in result:
        print(data)


except mc.Error as e:
    print("Error")

import mysql.connector as mc

try:

dbname = input("Please Enter Database Name : ")

tablename = input("Please Enter Table Name : ")

mydb = mc.connect(

host="localhost",

user="root",

password="",

database = dbname

)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM {} ".format(tablename))

result = mycursor.fetchall()

for data in result:

print(data)

except mc.Error as e:

print("Error")

We want to select the data according to user input by entering database name and table name.

dbname = input("Please Enter Database Name : ")
tablename = input("Please Enter Table Name : ")

1 2	dbname = input("Please Enter Database Name : ") tablename = input("Please Enter Table Name : ")

This is the query for selecting the data.

mycursor.execute("SELECT * FROM {} ".format(tablename))

1	mycursor.execute("SELECT * FROM {} ".format(tablename))

Also we need to fetch all the data from the cursor object using fetchall() method.

  result = mycursor.fetchall()

1	result = mycursor.fetchall()

Iterate over the data.

    for data in result:
        print(data)

1 2	for data in result: print(data)

Run the code and this is the result.

5: Updating Data From Mysql Database in Python

For updating the data you can use this code, just we have connected our python code to the Wamp Server using mysql connector and after that we create the object of cursor and execute the query.

import mysql.connector as mc

try:
    mydb=mc.connect(
        host="localhost",
        user="root",
        password="",
        database="pythondb"
    )


    mycursor = mydb.cursor()


    query = "UPDATE users SET email = 'updated@gmail.com' WHERE email = 'parwiz@gmail.com'"

    mycursor.execute(query)

    mydb.commit()

    print(mycursor.rowcount, "record affected")


except mc.Error as e:
    print("Updating Failed ")

import mysql.connector as mc

try:

mydb=mc.connect(

host="localhost",

user="root",

password="",

database="pythondb"

)

mycursor = mydb.cursor()

query = "UPDATE users SET email = 'updated@gmail.com' WHERE email = 'parwiz@gmail.com'"

mycursor.execute(query)

mydb.commit()

print(mycursor.rowcount, "record affected")

except mc.Error as e:

print("Updating Failed ")

Run the code and this is the result.

6: Deleting Data From Mysql Database in Python

This code is for deleting the data in Python Mysql.

import mysql.connector as mc

try:
    mydb=mc.connect(
        host="localhost",
        user="root",
        password="",
        database="pythondb"
    )


    mycursor = mydb.cursor()

    query = "DELETE FROM users WHERE email = 'updated@gmail.com'"

    mycursor.execute(query)


    mydb.commit()

    print(mycursor.rowcount, "record deleted")


except mc.Error as e:
    print("Deleting Failed")

import mysql.connector as mc

try:

mydb=mc.connect(

host="localhost",

user="root",

password="",

database="pythondb"

)

mycursor = mydb.cursor()

query = "DELETE FROM users WHERE email = 'updated@gmail.com'"

mycursor.execute(query)

mydb.commit()

print(mycursor.rowcount, "record deleted")

except mc.Error as e:

print("Deleting Failed")

Also you can watch the complete vide for this article

Subscribe and Get Free Video Courses & Articles in your Email