You can access MySQL and MariaDB databases from Python using a helper module. Some of the more popular Python modules to access MySQL and MariaDB databases are mysql-connector and PyMySQL.

These modules are more similar than they are different, and for this example, we'll be using the mysql-connector module.

Steps to access MySQL database from 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!