Tutorial on how to connect python with MySQldb

Date posted: 20/03/2019

Introduction:

In this article helps you in connect python with MySQldb and insert queries in DB.  This example is explained based on Linux environment in Centos 7.

Prerequisites:

  1. For centOS and RHEL based environment:
# sudo yum install python

2. Pip installation is most important for installing python packages

# curl -O https://bootstrap.pypa.io/get-pip.py

3. Confirm the installed PIP version so that we will proceed our work further:

# pip –version


Importing Modules and their Pip Installation:

While Importing, if the packaging Modules of  any one among the above missed, will arise  Import error. To eradicate the error follow these step by step modules to import  using pip Installation.

1. Importing Mysqldb:

Yum install MYSQL-python

2. Importing mysql.connector:

pip install mysql-connector-python

Create a table and insert into it

#!/usr/bin/env python

# Connect to db and create a table called blog that each have a name field and insert a couple blogs

import MySQLdb as mysqldb

connection = mysqldb.connect('localhost', 'bloguser', 'blogpw', 'blogdb');

with connection:
    cursor = connection.cursor()
    cursor.execute("create table blogs(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(25))")
    cursor.execute("insert into blogs(name) values('author')")
 cursor.execute("insert into blogs(name) values('editor')")
 cursor.execute("insert into blogs(name) values('admin')")

We can verify this on the MySQL side after running

mysql> select * from blogs;
+----+-----------+
| id | name      |
+----+-----------+
| 2 | author    |
| 3 | editor    |
| 4 | admin     |
+----+-----------+
3 rows in set (0.00 sec)

Select data from a table

#!/usr/bin/env python

# Connect to db and create a table called blogs that each have a name field and insert a couple blogs

import MySQLdb as mysqldb

connection = mysqldb.connect('localhost', 'bloguser', 'blogpw', 'blogdb');

with connection:
    cursor = connection.cursor()
    cursor.execute("select * from blogs")
blogs = cursor.fetchall()
    for blog in blogs:
        print(blog)

That’s it! Hope it’s useful.

Thanks for using pheonix solutions.

You find this tutorial helpful? Share with your friends to keep it alive.

Leave a Reply