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:
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 |
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.