DDL Queries

Create a Python SQLite table, add a column with ALTER TABLE, and remove the table with DROP TABLE. These are the data definition queries.

Example

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
    "CREATE TABLE badge (code INTEGER PRIMARY KEY, name TEXT NOT NULL)"
)
cur.execute("ALTER TABLE badge ADD COLUMN room TEXT")
cur.execute("SELECT code, name, room FROM badge")
print([m[0] for m in cur.description])
cur.execute("DROP TABLE badge")
print("dropped")
con.close()

The column names print as ['code', 'name', 'room'], then dropped. CREATE TABLE defines the table. code is the primary key. name must be present because it is NOT NULL. ALTER TABLE ... ADD COLUMN adds room. Oracle notes also use ALTER TABLE ... MODIFY to change a column type. SQLite does not use that statement, so this lesson adds a column instead. DROP TABLE removes the table. SQLite types in this lesson are INTEGER and TEXT. Oracle notes often write NUMBER and VARCHAR2 for the same idea. In SQLite, a DDL statement commits the open transaction. If this browser has no sqlite3, the playground says so and still prints the column names and dropped.

Key takeaways

• DDL defines or removes structure: CREATE, ALTER, and DROP

• CREATE TABLE names the columns and their types

• PRIMARY KEY identifies one row, and NOT NULL requires a value

• ALTER TABLE ... ADD COLUMN adds a column

• DROP TABLE removes the table

• Next: DML Queries — insert, update, delete, commit, and rollback

• Previous: Database Communication