Python MySQL Example Tutorial With Examples [Latest]

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:

D:SoftwarePythonPython36-32Scripts> pip install pymysql

You will see as following:

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:

Python MySQL Example

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

import pymysql
#database connection
connection = pymysql.connect(host="localhost",user="root",passwd="",database="databaseName" )
cursor = connection.cursor()
# some other statements  with the help of cursor
connection.close()

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.

import pymysql
#database connection
connection = pymysql.connect(host="localhost", user="root", passwd="", database="databaseName")
cursor = connection.cursor()
# Query for creating table
ArtistTableSql = """CREATE TABLE Artists(
ID INT(20) PRIMARY KEY AUTO_INCREMENT,
NAME  CHAR(20) NOT NULL,
TRACK CHAR(10))"""
cursor.execute(ArtistTableSql)
connection.close()

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

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.

import pymysql
#database connection
connection = pymysql.connect(host="localhost", user="root", passwd="", database="databaseName")
cursor = connection.cursor()
# queries for inserting values
insert1 = "INSERT INTO Artists(NAME, TRACK) VALUES('Towang', 'Jazz' );"
insert2 = "INSERT INTO Artists(NAME, TRACK) VALUES('Sadduz', 'Rock' );"
#executing the quires
cursor.execute(insert1)
cursor.execute(insert2)
#commiting the connection then closing it.
connection.commit()
connection.close()

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:

import pymysql
#database connection
connection = pymysql.connect(host="localhost", user="root", passwd="", database="databaseName")
cursor = connection.cursor()
# queries for retrievint all rows
retrive = "Select * from Artists;"
#executing the quires
cursor.execute(retrive)
rows = cursor.fetchall()
for row in rows:
   print(row)
#commiting the connection then closing it.
connection.commit()
connection.close()

It will output:

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:

updateSql = "UPDATE  Artists SET NAME= 'Tauwang'  WHERE ID = '1' ;"
cursor.execute(updateSql  )

Python MySQL delete

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

deleteSql = "DELETE FROM Artists WHERE ID = '1'; "
cursor.execute(deleteSql )

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:

dropSql = "DROP TABLE IF EXISTS Artists;"
cursor.execute(dropSql)

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.