How to access MySQL database using Python

Python can be used to access MySQL databases with the help of an additional module. There are a few available Python modules to allow MySQL database access, and each come with their pros and cons.

This example will be based on mysql-connector module, though other modules would function the same with only minor differences.

1. Install pip for your system.

   How to install pip on Ubuntu and Debian
   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. Get records from executed statement.

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

10. Process records accordingly.

    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 cursor.

    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()