How to Connect Python with Database

In this tutorial, we will discuss how Connect Python with a Database by the use of the ‘MySQL Connector Python’ module to connect to a MySQL database in Python. This article on Python MySQL shows how to create and integrate Python applications with a MySQL database server.

To communicate with MySQL in Python, we can use the following modules:

  1. Python MySQL Connector
  2. PyMySQL
  3. MySQLDB
  4. MySqlClient
  5. OurSQL

You can select any of the above modules to meet your needs. The MySQL database can still be accessed in
the same way. I recommend that you use one of the two modules below:

  1. MySQL Connector Python
  2. PyMySQL

The MySQL Connector Python module is the subject of this article. MySQL Connector Python was used
to generate all of the examples.

MySQL Connector Python’s advantages and benefits include:

  • MySQL Connector Python is developed entirely in Python and is self-contained in terms of database query execution.
  • It is an official Oracle-supported MySQL and Python driver.
  • It supports Python 3 and is regularly maintained.

How to connect MySQL database in Python

Let’s see how to connect the MySQL database in Python using the ‘MySQL Connector Python module.

You need to know the following detail of the MySQL server to perform the connection from Python.

NameDescription
UsernameThe username that you use to work with MySQL Server. The default
username for the MySQL database is a root.
PasswordPassword is given by the user at the time of installing the MySQL server. If
you are using root then you won’t need the password.
HostnameThe server name or Ip address on which MySQL is running. if you are running
on localhost, then you can use localhost or its IP 127.0.0.0
Database nameThe name of the database to which you want to connect and perform the
operations.

Connect to MySQL Database in Python

Step 01: Install MySQL connector module, Use the pip command to install MySQL connector Python. If you are using an old version of python then you must install pip in your system. ‘How To Install PIP to Manage Python Packages On Windows‘.

pip install mysql-connector-python

Step 02: Import MySQL connector module. Import using an import MySQL.connector statement so you can use this module’s methods to communicate with the MySQL database.

Step 03: Use the connect() method of the MySQL Connector class with the required arguments to connect MySQL. It would return a MySQLConnection object if the connection was established successfully.

Step 04: Use the cursor() method of a MySQLConnection object to create a cursor object to perform various SQL operations.

Step 05: Use the execute() method. The execute() methods run the SQL query and return the result.

Step 06: Extract result using fetchall(). Use cursor.fetchall() or fetchone() or fetchmany() to read query result.

Step 07: Close cursor and connection objects. Use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

Run the below query on the MySQL console if you have not created any database in MySQL. Otherwise, you can skip the below query.

Create Database in MySQL

Query:

Create database techshop;
Connect Python with Database img0

Result:

Connect Python with Database img1

Now we have to connect MySQL Database in Python. Copy the following code and paste it into your file.

Code:

import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='techshop',
                                         user='root',
                                         password=' ')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

img3

Catch exceptions that may occur during this process by importing the Error class from the MySQL connector module using a from mysql.connector import Error statement. Error class is useful to debug when we failed to connect to MySQL. For example, ACCESS DENIED ERROR when the username or password is wrong.

The connect() method can throw a Database error exception if one of the required parameters is wrong. For example, if you provide a database name that is not present in MySQL.

The is_connected() is the method of the MySQLConnection class through which we can verify is our Python application is connected to MySQL.

At last, we are closing the MySQL database connection using a close() method of MySQLConnection class.

Create MySQL table from Python

We’ll learn how to build a table in MySQL from Python in this part now that you know how to connect to a MySQL server from Python. Create a mobile table in the techshop database.

Code:

import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='techshop',
                                         user='root',
                                         password='')
    mySql_Create_Table_Query = """CREATE TABLE Mobile ( 
                             Id int(11) NOT NULL,
                             Name varchar(250) NOT NULL,
                             Price float NOT NULL,
                             Purchase_date Date NOT NULL,
                             PRIMARY KEY (Id)) """
    cursor = connection.cursor()
    result = cursor.execute(mySql_Create_Table_Query)
    print("Mobile Table created successfully ")
except mysql.connector.Error as error:
    print("Failed to create table in MySQL: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output:

img4

Connect to MySQL Using Connector Python C Extension

To connect to the MySQL database, the Python connector module offers a C Extension interface. The use pure connection option specifies whether a pure Python interface or a C Extension is used to connect to MySQL.

The default option of using pure is False, which means it connects using the pure Python implementation we covered earlier. The example below shows how to use a C extension to connect.

Code:

import mysql.connector
from mysql.connector import Error
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='techshop',
                                         user='root',
                                         password='', use_pure=True)
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL database... MySQL Server version on ", db_Info)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Output:

img5

That’s all for this article if you have any confusion contact us through our website or email us at [email protected] or by using LinkedIn

Suggested Articles:

  1. How to read JSON files in Python
  2. How to create a virtual environment in Python?
  3. What is the difference between pip and pip3?

2 thoughts on “How to Connect Python with Database”

Leave a Comment