DML with MariaDB Connector/Python

Python developers can use MariaDB Connector/Python to connect to MariaDB Enterprise to perform basic DML (Data Manipulation Language) operations. DML means all SQL-data statements (ISO/IEC 9075-2:2016). Connector/Python is compliant with the Python Database API Specification v2.0 (PEP 249) .

DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016), for example, DELETE, INSERT, REPLACE, SELECT, and UPDATE.

DML Operations

MariaDB Connector/Python accesses the database through a cursor, which is obtained by calling the cursor() method on the connection. This object provides an interface for performing basic operations in this section.

The cursor provides two methods for executing SQL statements:

Method

Description

execute()

Executes a single SQL statement.

executemany()

Executes the given SQL statement for each tuple in a list.

Code Example: INSERT, UPDATE, DELETE

INSERT, UPDATE, and DELETE are DML (Data Manipulation Language) operations that modify the data in a table.

The following example shows how to insert data into the example table created in Setting up the Environment:

To update or delete data, replace the INSERT statement in the code example with an UPDATE or DELETE statement:

# Module Import
import mariadb
import sys

# Adds single contact
def add_contact(cur, first_name, last_name, email):
    """Adds the given contact to the contacts table"""

    cur.execute("INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
          (first_name, last_name, email))

# Adds Multiple contacts
def add_multiple_contacts(cur, data):
    """Adds multiple contacts to database from given data"""

    cur.executemany("INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)", data)

# Instantiate Connection
try:
   conn = mariadb.connect(
      user="connpy_test",
      password="passwd",
      host="192.0.2.1",
      port=3306,
      autocommit=True)

   # Instantiate Cursor
   cur = conn.cursor()

   # Initialize Data to add a single contact
   new_contact_fname = "J. R. R."
   new_contact_lname = "Tolkien"
   new_contact_email = "jrr.tolkien@example.edu"

   # Call function to add a single contact
   add_contact(cur,
       new_contact_fname,
       new_contact_lname,
       new_contact_email)

   # Initialize Data to add multiple contacts
   new_contacts = [
       ("Howard", "Lovecraft", "hp.lovecraft@example.net"),
       ("Flannery", "O'Connor", "flan.oconnor@example.com"),
       ("Walker", "Percy", "w.percy@example.edu")
     ]

   # Call function to add multiple contacts
   add_multiple_contacts(cur, new_contacts)

   # Close Connection
   conn.close()

except mariadb.Error as e:
       print(f"Error connecting to MariaDB Enterprise, or running DML : {e}")
       sys.exit(1)
  • The functions to add data must be defined before being called with regards to their ordering in the script.

  • The autocommit attribute must be set to True in the mariadb.connect() function call to enable auto-commit mode because by default Connector/Python does not enable auto-commit.

  • The execute() method is called on the cursor, which executes an INSERT statement to add a single contact to the table.

  • The query string is the only value specified to the execute() method.

  • The executemany() method is called on the cursor, which executes an INSERT statement to add multiple contacts to the table.

  • The query string is the only value specified to the executemany() method.

  • When you are done with a connection, close it to free resources. Close the connection using the close() method.

Confirm the data was properly inserted by using MariaDB Client to execute a SELECT statement:

MariaDB [test]> SELECT * from contacts;
+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  6 | J. R. R.   | Tolkien   | jrr.tolkien@example.edu  |
|  7 | Howard     | Lovecraft | hp.lovecraft@example.net |
|  8 | Flannery   | O'Connor  | flan.oconnor@example.com |
|  9 | Walker     | Percy     | w.percy@example.edu      |
+----+------------+-----------+--------------------------+
4 rows in set (0.000 sec)

Code Example: SELECT

SELECT is a DML (Data Manipulation Language) operation that reads the data from a table.

The following example shows how to select data from the example table created in Setup for Examples:

# Module Import
import mariadb
import sys

# Print List of Contacts
def print_contacts(cur):
    """Retrieves the list of contacts from the database and prints to stdout"""

    # Initialize Variables
    contacts = []

    # Retrieve Contacts
    cur.execute("SELECT first_name, last_name, email FROM test.contacts")

    # Prepare Contacts
    for (first_name, last_name, email) in cur:
         contacts.append(f"{first_name} {last_name} <{email}>")

    # List Contacts
    print("\n".join(contacts))

# Instantiate Connection
try:
   conn = mariadb.connect(
      user="connpy_test",
      password="passwd",
      host="192.0.2.1",
      port=3306)

   # Instantiate Cursor
   cur = conn.cursor()

   # Call function to print contacts
   print_contacts(cur)

   # Close Connection
   conn.close()

except mariadb.Error as e:
       print(f"Error connecting to MariaDB Enterprise, or running DML : {e}")
       sys.exit(1)
  • The function to select data must be defined before being called with regards to its ordering in the script.

  • The execute() method is called on the cursor, which executes a SELECT statement to select all rows from the table.

  • The query string is the only value specified to the execute() method in this case.

  • When the query finishes executing, the cursor retrieves a list of the rows.

  • The cursor retrieves each row as a tuple.

  • A for loop is used to iterate the list of tuples. Other options for iterating results are to use the following methods: fetchall(), fetchmany(), and fetchone().

  • The values from each tuple are formatted and appended to a list.

  • When all results have been retrieved, the list is printed to stdout.

  • When you are done with a connection, close it to free resources. Close the connection using the close() method.

Example output:

J. R. R. Tolkien <jrr.tolkien@example.edu>
Howard Lovecraft <hp.lovecraft@example.net>
Flannery O'Connor <flan.oconnor@example.com>
Walker Percy <w.percy@example.edu>