Python MySQL Database with MySQL Connector

In this Python MySQL article we learn about Python MySQL Database with MySQL Connector

, for connecting of Python code to Mysql database, there are different libraries, 

before this i had an article that how you can use MySQLDb with Python, but this time we

want to use MySQL Connector.

 

 

 

 

What is MySQL Connector ?

It is MySQL driver written in Python which does not depend on MySQL C client libraries

and implements the DB API v2.0 specification (PEP-249).

 

 

Installation

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

 

 

Also you need to download and install Wamp Server, because we want to use Wamp Server

as virtual server.

 

 

Creating MySQL Database in Python

OK now let’s create our MySQL Database using Python code, we are going to create

the database based on the user input.

 

 

 

OK the first thing we need to connect our Python code with the local host, as i have already

said that we are using Wamp Server, you can use mysql.connector.connect() function

for this.

 

 

In this code we are going to get the database name from the user input, and according to

that input we want to create our database in mysql.

 

 

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

 

 

 

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

 

 

Run your code give the database name, in here i want to create a database at

name of geekscoders.

Python MySQL Database with MySQL Connector
Python MySQL Database with MySQL Connector

 

 

 

And if you see Wamp Server our database is created.

Python Mysql Connector Database Creation
Python Mysql Connector Database Creation

 

 

 

 

How to Check MySQL Database Connection in Python

After database creation, we want to check the database connection, you can use

this code for checking mysql database connection in python.

 

 

 

Run the code and you will receive positive response from the database, because we

have correct name for the database.

Python MySQL Connector Established Connection
Python MySQL Connector Established Connection

 

 

 

How to 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 geekscoders database, iam going to call my table name users. and add three fields id, name

and age. this is the structure for our users table make sure that you make the id auto increment.

Python Mysql Database Structure
Python Mysql Database Structure

 

 

 

 

And this is the code for inserting the data.

 

 

 

We want to insert data according to the user input.

 

 

This is our query for inserting the data.

 

 

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

 

 

Run the code give name and age, this will be the result.

Python Mysql Connector Data Inserted
Python Mysql Connector Data Inserted

 

 

 

Now check your Wamp Server, we have the data.

Mysql Connector Data Inserted
Mysql Connector Data Inserted

 

 

 

How to Select Data From MySQL Database in Python

In this part we want to select or retrieve our data from mysql database in Python using

mysql connector. so this is the code for selecting the data.

 

 

 

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

table name.

 

 

This is the query for selecting the data.

 

 

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

want one data than you can use fetchone().

 

 

 

Run the code and this is the result.

Python Mysql Connector Selecting Data
Python Mysql Connector Selecting Data

 

 

 

 

How to Update Data in MySQL with 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.

 

 

 

Now check your Wamp Server, our first row is updated.

Python Mysql Connector Updated
Python Mysql Connector Updated

 

 

 

How to Delete Data From MySQL in Python

So now this is the code for deleting data, and we want to delete our first row in the

database table.

 

 

 

 

Run the code and check your Wamp Server we don’t have the first record in the table.

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

Leave a Comment