DML with MariaDB Connector/Python

Overview

Python developers can use MariaDB Connector/Python to connect to MariaDB database products 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, DELETEDELETE, INSERTINSERT, REPLACEREPLACE, SELECTSELECT, and UPDATEUPDATE.

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

INSERTINSERT, UPDATEUPDATE, and DELETEDELETE 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 INSERTINSERT statement in the code example with an UPDATEUPDATE or DELETEDELETE 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(
      host="192.0.2.1",
      port=3306,
      user="db_user",
      password="USER_PASSWORD",
      autocommit=True)
except mariadb.Error as e:
   print(f"Error connecting to the database: {e}")
   sys.exit(1)

# Instantiate Cursor
cur = conn.cursor()

# Initialize Data to add a single contact
new_contact_fname = "Hao"
new_contact_lname = "Nguyen"
new_contact_email = "hao.nguyen@example.com"

# 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 = [
    ("Dani", "Smith", "dani.smith@example.com"),
    ("Lee", "Wang", "lee.wang@example.com"),
    ("Kai", "Devi", "kai.devi@example.com")
  ]

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

# Close Connection
conn.close()
# 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(
      host="SKYSQL_SERVICE.mdb0000001.db.skysql.net",
      port=5009,
      ssl_ca="/path/to/skysql_chain.pem",
      user="DB00000001",
      password="USER_PASSWORD",
      autocommit=True)
except mariadb.Error as e:
   print(f"Error connecting to the database: {e}")
   sys.exit(1)

# Instantiate Cursor
cur = conn.cursor()

# Initialize Data to add a single contact
new_contact_fname = "Hao"
new_contact_lname = "Nguyen"
new_contact_email = "hao.nguyen@example.com"

# 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 = [
    ("Dani", "Smith", "dani.smith@example.com"),
    ("Lee", "Wang", "lee.wang@example.com"),
    ("Kai", "Devi", "kai.devi@example.com")
  ]

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

# Close Connection
conn.close()
  • 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 INSERTINSERT 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 INSERTINSERT 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 SELECTSELECT statement:

SELECT * from contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
| 6  | Hao        | Nguyen    | hao.nguyen@example.com |
| 7  | Dani       | Smith     | dani.smith@example.com |
| 8  | Lee        | Wang      | lee.wang@example.com   |
| 9  | Kai        | Devi      | kai.devi@example.com   |
+----+------------+-----------+------------------------+

Code Example: SELECT

SELECTSELECT 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(
      host="192.0.2.1",
      port=3306,
      user="db_user",
      password="USER_PASSWORD")
except mariadb.Error as e:
   print(f"Error connecting to the database: {e}")
   sys.exit(1)

# Instantiate Cursor
cur = conn.cursor()

# Call function to print contacts
print_contacts(cur)

# Close Connection
conn.close()
# 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(
      host="SKYSQL_SERVICE.mdb0000001.db.skysql.net",
      port=5009,
      ssl_ca="/path/to/skysql_chain.pem",
      user="DB00000001",
      password="USER_PASSWORD")
except mariadb.Error as e:
   print(f"Error connecting to the database: {e}")
   sys.exit(1)

# Instantiate Cursor
cur = conn.cursor()

# Call function to print contacts
print_contacts(cur)

# Close Connection
conn.close()
  • 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 SELECTSELECT 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:

Hao Nguyen <hao.nguyen@example.com>
Dani Smith <dani.smith@example.com>
Lee Wang <lee.wang@example.com>
Kai Devi <kai.devi@example.com>