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
- Name of the computer – The default is – localhost
- Name of MySQL user- In our case, it is root ( This is also a default user name in MySQL for windows)
- User password – in our case it is not set so we will leave this position blank
- 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.