Connect to MySQL Through Python Using MySQL Connector Python

In this blog, we will connect MySQL through Python using the MySQL connector Python. After connecting, we will write some SQL queries and fetch records from MySQL.

Created by Nutan

What is MySQL Connector/Python?

MySQL Connector/Python enables Python programmes to access MySQL databases using an API that is compatible with Python.

MySQL Connector/Python includes support for:

1. Almost all features provided by MySQL Server up to and including MySQL Server version 8.0

2. Converting parameter values back and forth between Python and MySQL data types, for example, Python datetime and MySQL DATETIME You can turn automatic conversion on for convenience or off for optimal performance.

3. All MySQL extensions to standard SQL syntax

4. Protocol compression, which enables compressing the data stream between the client and server.

5. connections using TCP/IP sockets and on Unix using Unix sockets.

6. Secure TCP/IP connections using SSL.

7. Self-contained driver. Connector/Python does not require the MySQL client library or any Python modules outside the standard library.

Connector/Python Installation

Python driver for communicating with MySQL servers using pip

pip install mysql-connector-python

Python driver for communicating with MySQL servers using conda

conda install -c anaconda mysql-connector-python

Connecting to MySQL Using Connector/Python

Import mysql connector

import mysql.connector

Establish a MySQL connection

mysql.connector.connect() Method

This method sets up a connection, establishing a session with the MySQL server. If no arguments are given, it uses the already configured or default values.

A connection with the MySQL server can be established using either the mysql.connector.connect() method or the mysql.connector.MySQLConnection() class.

We have to provide the database user, password, host, and database name to which we want to connect.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    print("Connected to Mysql")

except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    connection.close()

Output: Connected to Mysql

Create a cursor and execute sql command

What is Python MySQL Cursor?

The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object.

To create a cursor, use the cursor() method of a connection object.

MySQLCursor.execute() Method

This method executes the given database operation (query or command). The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style). execute() returns an iterator if multi is True.

We have a student table in the sample database. We will use the SELECT command to see student table data. The cursor returns the tuple, which we will use for looping to print student details.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()

    query = ("SELECT * FROM student")
    cursor.execute(query)
    
    for (id, first_name, last_name, gender, roll_no, grade, description) in cursor:
        print("{}, {} {}".format(id, first_name, last_name))
        pass
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    connection.close()

Output: 1, John Methew

In the student table, there is only one record.

Create a table

We are going to create a person table.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()

    query = ("CREATE TABLE Person (\
    id int PRIMARY KEY NOT NULL,\
    name varchar(255) NOT NULL,\
    address varchar(255))")
    
    cursor.execute(query)
    print("Table created successfully...")
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Table created successfully…

We can see it in the phpMyAdmin user interface.

Insert data into a person table

MySQLConnection.commit() Method

This method sends a COMMIT statement to the MySQL server, committing the current transaction. Since by default Connector/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    
    add_person = ("INSERT INTO person "
               "(id, name, address) "
               "VALUES (%s, %s, %s)")
    data_person = (1, 'Nutan', 'Hyderabad')

    cursor.execute(add_person, data_person)
    
    connection.commit()
    print(cursor.rowcount, "Data inserted into a table.")
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: 1 Data inserted into a table.

Insert multiple records

MySQLCursor.executemany() Method

cursor.executemany(operation, seq_of_params): This method prepares a database operation (query or command) and executes it against all parameter sequences or mappings found in the sequence seq_of_params.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    
    add_person = ("INSERT INTO person "
               "(id, name, address) "
               "VALUES (%s, %s, %s)")
    data_person = [
        (2, 'John', 'New York'),
        (3, 'Peter', 'New Jersey'),
        (4, 'Priyanka', 'New York'),
        (5, 'Methew', 'United Kingdom'),
        (6, 'Denis', 'Finland')
    ]

    cursor.executemany(add_person, data_person)
    
    connection.commit()
    print("Data inserted into a table.")
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Data inserted into a table.

Update a record

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()

    query = ("UPDATE person SET name = 'Johana' WHERE id = 2")
    cursor.execute(query)
    
    connection.commit()
    print("Record updated successfully.")
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Record updated successfully.

Delete a record

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()

    query = ("DELETE FROM person WHERE id = 6")
    cursor.execute(query)
    
    connection.commit()
    print("Record deleted successfully.")
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Record deleted successfully.

Now, there is no sixth record. It deleted.

Fetch one record

MySQLCursor.fetchone() Method

This method retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available. By default, the returned tuple consists of data returned by the MySQL server, converted to Python objects.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    query = "SELECT * FROM person"
    
    cursor.execute(query)

    result = cursor.fetchone()
    
    print(result)
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: (1, ‘Nutan’, ‘Hyderabad’)

Fetch many records

MySQLCursor.fetchmany() Method

This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.

The number of rows returned can be specified using the size argument, which defaults to one. Fewer rows are returned if fewer rows are available than specified.

You must fetch all rows for the current query before executing new statements using the same connection.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    query = "SELECT * FROM person"
    
    cursor.execute(query)

    results = cursor.fetchmany(size = 3)
    
    print(results)
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: [(1, ‘Nutan’, ‘Hyderabad’), (2, ‘Johana’, ‘New York’), (3, ‘Peter’, ‘New Jersey’)]

Fetch all records

MySQLCursor.fetchall() Method

The method fetches all (or all remaining) rows of a query result set and returns a list of tuples. If no more rows are available, it returns an empty list.

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    query = "SELECT * FROM person"
    
    cursor.execute(query)

    results = cursor.fetchall()

    print("Total row count: ", cursor.rowcount)
    print("Column names: ", cursor.column_names)
    
    print(results)
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Total row count: 5
Column names: (‘id’, ‘name’, ‘address’)
[(1, ‘Nutan’, ‘Hyderabad’), (2, ‘Johana’, ‘New York’), (3, ‘Peter’, ‘New Jersey’), (4, ‘Priyanka’, ‘New York’), (5, ‘Methew’, ‘United Kingdom’)]

Fetch all records whose address is starting with ‘new’

try:
    connection = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='sample')
    
    
    cursor = connection.cursor()
    query = "SELECT * FROM person WHERE address LIKE 'new%'"
    
    cursor.execute(query)

    results = cursor.fetchall()

    print("Total row count: ", cursor.rowcount)
    print(results)
    
except mysql.connector.Error as error:
    if error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif error.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(error)
else:
    cursor.close()
    connection.close()

Output: Total row count: 3
[(2, ‘Johana’, ‘New York’), (3, ‘Peter’, ‘New Jersey’), (4, ‘Priyanka’, ‘New York’)]

That’s it in this part. If you have questions related to my blog, you can email me at nutanbhogendrasharma@gmail.com.

Thanks for reading, Happy reading 😊😊😊.