How to access MySQL database using Python

Connecting Python to a MySQL or MariaDB database is a common task for developers who need to interact with these databases programmatically. To accomplish this, you can use the mysql-connector module, which is a reliable and straightforward way to establish a connection between your Python code and the database. This module allows you to execute queries, retrieve data, and manage database records directly from your Python environment.

The mysql-connector module is specifically designed for seamless integration with MySQL, providing compatibility and support for the latest MySQL features. It is easily installed via pip and can be used in any Python script to handle database operations efficiently. This direct access to the database from Python is crucial for tasks that involve data analysis, application development, and automation.

Using mysql-connector, you can perform various database operations like creating connections, executing SQL statements, fetching results, and managing database resources. This approach simplifies database management, allowing developers to focus on building and deploying applications that rely on real-time data interaction.

1. Install pip for your system.

   Related: How to install pip on Ubuntu and Debian
   Related: How to install pip on CentOS / Red Hat / Fedora

2. Install mysql-connector module using pip.

   $ pip3 install mysql-connector
   Collecting mysql-connector
     Downloading mysql-connector-2.2.9.tar.gz (11.9 MB)
        |████████████████████████████████| 11.9 MB 11.6 MB/s
   Building wheels for collected packages: mysql-connector
     Building wheel for mysql-connector (setup.py) ... done
     Created wheel for mysql-connector: filename=mysql_connector-2.2.9-cp38-cp38-linux_x86_64.whl size=247948 sha256=4baaca9e6150eb5bac152a3ec95dee71ff2235f83fdd41b83bf5c89346adef29
     Stored in directory: /home/user/.cache/pip/wheels/57/e4/98/5feafb5c393dd2540e44b064a6f95832990d543e5b4f53ea8f
   Successfully built mysql-connector
   Installing collected packages: mysql-connector
   Successfully installed mysql-connector-2.2.9

3. Run your preferred Python shell.

   $ ipython3
   Python 3.8.2 (default, Apr 27 2020, 15:53:34)
   Type 'copyright', 'credits' or 'license' for more information
   IPython 7.13.0 -- An enhanced Interactive Python. Type '?' for help.

4. Import mysql.connector module.

   In [1]: import mysql.connector

5. Establish connection to your MySQL or MariaDB database.

   In [2]: conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")

6. Create a cursor for your connection.

   In [3]: cursor = conn.cursor()

7. Create an SQL statement.

   In [4]: stmt = "SELECT * FROM user"

8. Execute your SQL statement.

   In [5]: cursor.execute(stmt)

9. Fetch the results from the executed SQL query.

   In [6]: records = cursor.fetchall()

10. Process the fetched records as needed.

    In [7]: print("Total users: ", cursor.rowcount)
    Total users:  2
    In [8]: for record in records:
       ...:     print("User: ", record[1])
       ...:
    User:  bytearray(b'root')
    User:  bytearray(b'user')

11. Close connection to database.

    In [9]: conn.close()

12. Close the cursor after finishing the operations.

    In [10]: cursor.close()
    Out[10]: True

13. Combine steps into a Python script.

    access-mysql-database.py

    #!/usr/bin/env python3
     
    #Import required module
    import mysql.connector
     
    #Establish connection to database
    conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
     
    #Create cursor
    cursor = conn.cursor()
     
    #Create an SQL statement
    stmt = "SELECT * FROM user"
     
    #Execute SQL statement
    cursor.execute(stmt)
     
    #Get records from executed statement
    records = cursor.fetchall()
     
    #Process
    print("Total users: ", cursor.rowcount)
     
    for record in records:
        print("User: ", record[1])
     
    #Close connection to database
    conn.close()
     
    #Close cursor
    cursor.close()