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.

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