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 method also works with MariaDB databases as MariaDB is for the most part is just a drop-in replacement for MySQL.

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

Steps to access MySQL database using Python:

  1. 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
  2. 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.
  3. Import mysql.connector module.
    In [1]: import mysql.connector
  4. Establish connection to your MySQL or MariaDB database.
    In [2]: conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
  5. Create a cursor for your connection.
    In [3]: cursor = conn.cursor()
  6. Create an SQL statement.
    In [4]: stmt = "SELECT * FROM user"
  7. Execute your SQL statement.
    In [5]: cursor.execute(stmt)
  8. Get records from executed statement.
    In [6]: records = cursor.fetchall()
  9. 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')
  10. Close connection to database.
    In [9]: conn.close()
  11. Close cursor.
    In [10]: cursor.close()
    Out[10]: True
  12. 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()
Discuss the article:

Comment anonymously. Login not required.

Share!