DML with MariaDB Connector/Python
This page is part of MariaDB's Documentation.
The parent of this page is: MariaDB Connector/Python
Topics on this page:
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, , , , , and .
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 |
| Executes a single SQL statement. |
| Executes the given SQL statement for each tuple in a list. |
Code Example: INSERT, UPDATE, DELETE
, , and 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 statement in the code example with an or 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()
The functions to add data must be defined before being called with regards to their ordering in the script.
The
autocommitattribute must be set toTruein themariadb.connect()function call to enableauto-commitmode because by default Connector/Python does not enableauto-commit.The
execute()method is called on the cursor, which executes an 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 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 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
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()
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 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
forloop is used to iterate the list of tuples. Other options for iterating results are to use the following methods:fetchall(),fetchmany(), andfetchone().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>