DDL with MariaDB Connector/Python

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:

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"""

   try:
      cur.execute("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)")
   except mariadb.Error as e:
      print(f"Error altering contacts: {e}")
      conn.close()
      sys.exit(1)

# 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()

alter_contacts(cur)

# Close Connection
conn.close()

# Module Import
import mariadb
import sys

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

   try:
      cur.execute("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)")
   except mariadb.Error as e:
      print(f"Error altering contacts: {e}")
      conn.close()
      sys.exit(1)

# 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()

alter_contacts(cur)

# Close Connection
conn.close()

• 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 TABLEALTER 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 DESCDESC 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"""

   try:
      cur.execute("TRUNCATE test.contacts")
   except mariadb.Error as e:
      print(f"Error altering contacts: {e}")
      conn.close()
      sys.exit(1)

# 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()

truncate_contacts(cur)

# Close Connection
conn.close()

# Module Import
import mariadb
import sys

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

   try:
      cur.execute("TRUNCATE test.contacts")
   except mariadb.Error as e:
      print(f"Error altering contacts: {e}")
      conn.close()
      sys.exit(1)

# 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()

truncate_contacts(cur)

# Close Connection
conn.close()

• 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 TRUNCATETRUNCATE 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 SELECTSELECT statement on the same table. The statement should return an empty set because the TRUNCATETRUNCATE 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)