Python MySQL Example Tutorial With Examples

Welcome to Python MySQL example tutorial. MySQL is one of the most widely used database and python provides support to work with mysql database.

Python MySQL

First of all we have to install python mysql connector package. To install mysql package do the following: go to the Scripts directory in the python. For my case:

D:SoftwarePythonPython36-32Scripts

Open command prompt here. Then give the following command:

You will see as following:

mysql3

That’s it, python mysql connector package is installed.

Database Connection

I am using xampp for my database. This is an optional step, if you have MySQL already installed then you can skip this step.

Before starting coding run the xampp control panel and start Apache & MySQL. From browser (https://localhost/phpmyadmin/), I have created a database named databaseName which is shown below:

mysql3

Python MySQL Example

First we have to build connection with the mysql. The following will connect to mysql database in python program.

First we have imported the pymysql, then established a connection. The pymysql.connect() takes four arguments. First one is host name i.e. localhost, and the rest three are as they are declared. Using this connection we have created a cursor that will be used for different queries.

Python MySQL Example – creating table

Let’s now create a table named Artist having columns – name, id and track.

A table named Artists will be created. You can see it in your browser.

mysql3

Python MySQL insert

Now our interest is to insert some row entities in the table. First you have to write the queries to insert different data, then execute it with the help of cursor.

Python MySQL select

We have inserted two rows in the above code. Now we want to retrieve those. To do this have a look at the following example:

It will output:

mysql3mysql3

Python MySQL update

Suppose, you want to rename the name of the first artist from Towang to Tauwang. To update any attribute of any entity do the following:

Python MySQL delete

To delete an entity you have to execute the as followings:

Python MySQL Example – Drop Table

Sometimes you may need to drop any table before creating any new table so that name collision does not take place. To drop the Artists table, you can do this as following:

Then the Artists table will be dropped from the database databaseName. This is all for python mysql tutorial. For further information you can visit here.

By admin

Leave a Reply

%d bloggers like this: