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.

 

 

 

 

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.

 

 

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. 

 

Python Mysql Database
Python Mysql Database

 

 

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

 

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 Database with MySQLdb
Python MySQL Database with MySQLdb

 

 

 

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.

 

 

 

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

your database.

Python Mysql Creating Tables
Python Mysql Creating Tables

 

 

 

 

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.

 

 

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

database table.

 

 

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

 

 

 

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

How to insert data in Python Mysql
How to insert data in Python Mysql

 

 

 

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

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.

Inserting Data in Python Mysql
Inserting Data in Python Mysql

 

 

 

 

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.

 

 

 

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

 

 

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

 

 

 

Run the code and this is the result.

Python Mysql Selecting Data
Python Mysql Selecting Data

 

 

 

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.

 

 

 

If you run the code this is the result.

Python Mysql Updating Data
Python Mysql Updating Data

 

 

 

 

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

using placeholders.

 

 

 

Python Mysql Database Deleting Data

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

 

 

 

Now first record is deleted.

Python Mysql Deleting Data
Python Mysql Deleting Data

 

Thank you for visiting my website! If you enjoyed the free courses, please consider supporting my works on Patreon.

Leave a Comment