DDL with MariaDB Connector/Python

Python developers can use MariaDB Connector/Python to connect to MariaDB Enterprise to perform basic DDL (Data Definition Language) operations.

DDL (Data Definition Language) refers to all SQL-schema statements in the SQL standard (ISO/IEC 9075-2:2016). Connector/Python is compliant with the Python Database API Specification v2.0 (PEP 249) .

Some examples of DDL include ALTER TABLE, CREATE TABLE, DROP TABLE, CREATE DATABASE, and TRUNCATE TABLE.

DDL Operations

MariaDB Connector/Python accesses the database through a cursor, which is obtained by calling the cursor() method on the connection. This cursor object provides you with 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: ALTER TABLE

The following example shows how to alter the data definition for the example table created in Setup for Examples:

# Module Import
import mariadb
import sys

# Alter Contacts
def alter_contacts(cur):
    """Alters data definition of contacts table to add a column"""

    cur.execute("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)")

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

    # Instantiate Cursor
    cur = conn.cursor()

    #Call the ``alter_contacts()`` function
    alter_contacts(cur)

    # Close Connection
    conn.close()

except mariadb.Error as e:
         print(f"Error connecting to MariaDB Enterprise, or running DDL : {e}")
         sys.exit(1)
  • The function to alter table 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 ALTER TABLE statement to alter the table.

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

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

Confirm the table was properly altered by using MariaDB Client to execute a DESC statement on the same table:

DESC contacts;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(25)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | YES  |     | NULL    |                |
| email         | varchar(100) | YES  |     | NULL    |                |
| contact_since | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Code Example: TRUNCATE TABLE

The following example shows how to truncate the example table created in Setup for Examples:

# Module Import
import mariadb
import sys

# Truncate Contacts
def truncate_contacts(cur):
    """Truncates contacts table, and removes all data from the table"""

    cur.execute("TRUNCATE test.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 the function to truncate table
    truncate_contacts(cur)

    # Close Connection
    conn.close()

except mariadb.Error as e:
    print(f"Error connecting to MariaDB Enterprise, or running DDL : {e}")
    sys.exit(1)
  • The function to truncate table 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 TRUNCATE statement to truncate the table.

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

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

Confirm the table was properly truncated by using MariaDB Client to execute a SELECT statement on the same table. The statement should return an empty set because the TRUNCATE statement deletes all data and resets the AUTO_INCREMENT column counter to 0. The next contact you add will be assigned id as 1.

Example:

SELECT * from test.contacts;
Empty set (0.000 sec)