Connect Python with MySQL server [ Simplest Method ]

Connect Python with MySQL server using Python 3.7 and MySQL server 5.7 using the simple steps listed in this article. The article is written on Windows Operating system 10 using Visual studio code.

The tutorials available at w3school and other resources are outdated and comes with some confusing steps and need a lot of learning curve so I decided to publish the latest connection steps with MySQL server.

Python3.7 does not come with built-in features to connect with MySQL or any other Database program so we have some python package that helps us to make this connection.

Python Packages required to connect with MySQL server

There are a lot of python packages that can help you to manipulate your MySQL server but the easiest one is mysqldb.

Python-mysqldb is the default industry standard python package to connect with the MySQL server.  The only thing that you need is to install this package on your system.

Pip command is one such python command that fetches python packages from its repository and installs on your local system. All you have to supply the following command at the command prompt

pip install mysqlclient

actually this is a new wrapper package created to support mysqldb for python 3.0 and above.

Connect Python with MySQL server

Before trying the below program make it very sure that the mysql server is running on your system and you have the following information

1. Name of the computer – The default is – localhost
2. Name of MySQL user- In our case, it is root ( This is also a default user name in MySQL for windows)
3. User password – in our case it is not set so we will leave this position blank
4. Name of the database

Since, we are trying to connect with our server using a third-party package so all we have to do it – we have to import the package in our system using the following command

import MySQLdb

Try the above command in python prompt if the python prompt appears without any error message, it means it is installed properly and ready to serve.

db = MySQLdb.connect("localhost", "root", "", "cable" )

now make a connection using the connect method and store the connection in a python variable db.

cursor = db.cursor()

The above statement will generate a cursor that we will use to execute SQL statements on mysql.

cursor.execute(“select version”)

Now fetch a single row from the connected database and store in a new python variable data

data = cursor.fetchone()

The information is now store in

data

print("Database version : %s " % data )
# disconnect from server
db.close()

The last statement will close your connection from the server. So your final program will look like this

"""Database connectivity mysql."""
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost", "root", "", "cable" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Database version : %s " % data )
# disconnect from server
db.close()

The output of the above python program to connect with MySQL is as follows

rakesh@folio MINGW64 /e/python (master)
$ C:/Python37/python.exe e:/python/database/mysql_connection.py
Database version : 5.7.23

This is the simplest method to connect Python with MySQL server. If you know any other method that is easy to implement. Let us know, we will try to publish it immediately.