How to connect to mysql with python

Test the module we just installed in the interactive Python interpreter

Once we have managed to download and install the mysql-connector module for interacting with the Mysql backend server through the help of the Python interactive shell, it is time to check out if the module works fine by doing a simple import like shown below.

import mysql

If the above code does not produce any error when executed in the Python interactive shell, it means that the module has been properly installed.

Experiment interactively with the mysql module

Now that we have managed to properly setup the python module which helps in automating the interaction with the Mysql database in the backend, we can easily write short pythonic codes to experiment in the interactive console.

Before we can execute any sql command in the Mysql backend database, we need to create a connection object like shown below.

connection = connector.connect(host='localhost', user='root', passwd='root@1
3@31')

Make sure to enter your credentials while creating the above object. Once you have executed the above piece of Python code, just print the object connection like shown below.

connection
mysql.connector.connection.MySQLConnection object at 0x0000000001DBB9E8>

Now that we have an object of type mysql.connector.connection.MySQLConnection we need to setup the cursor of the Mysql database so we can easily execute sql queries in the backend.

Let’s try to list all the current databases in the Mysql server.

# in the beginning we create a cursor object like shown below
cursor = connection.cursor()
# then we assign the sql query to a variable
sql_query = 'show databases'
# now we execute the sql query with the help of the cursor
cursor.execute(sql_query)

After the execution of the sql query with the help of the object cursor, we need to run a for loop with the main purpose of getting the results out.

for db in cursor:print(db)

After the execution of the Python code shown above you should see a list of the available databases in your backend Mysql server. My list is being shown below.

(u'information_schema',)
(u'bWAPP',)
(u'books',)
(u'bookstore',)
(u'employees',)
(u'hacking',)
(u'library',)
(u'mysql',)
(u'performance_schema',)
(u'students',)

As you can see from the results shown above, information_schema is a database within the Mysql server. It holds information about the data being stored in the Mysql server.

To use a specific database within the Mysql server, just make use of the following Python code.

name_of_db = ''
sql_code = 'use %s' % name_of_db
cursor.execute(sql_code)

You can also create a fresh database with a custom name. The code for doing so is being shown below.

db_name = 'new'
sql_code = 'create database %s' % db_name
cursor.execute(sql_code)

Final thoughts

As a coder, you will have plenty of projects which require the usage of a database management system in the backend. The Python programming language is a very practical utility when it comes to interacting with a database server. As you saw through this blog post, the module which helps us to interact with the Mysql server in the backend, is called mysql-connector.

Since this blog post is supposed to be a brief introduction to the mysql-connector, we did not cover the entire builtin functionalities it comes with. There are many functions offered by the mysql-connector that can be used to perform different operations in the backend database. We will cover some of them in the future blog posts, so you can become familiar with the main functions that help in interacting with a Mysql server in the backend.