Python Academy · Lesson

Connecting and Creating Tables

Open a database and define schema.

Connecting and Creating Tables is a free Python Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What is sqlite3?

Python ships with the sqlite3 module in its standard library. It lets you work with SQLite, a lightweight, file-based SQL database that needs no separate server.

No installation required
The whole database lives in a single file
Perfect for prototypes, tests, and small apps

import sqlite3
print(sqlite3.version)

Connecting to a Database

Use sqlite3.connect() to open a database. If the file does not exist, SQLite creates it.

Passing ':memory:' creates a temporary in-memory database that disappears when the connection closes.

import sqlite3
conn = sqlite3.connect(':memory:')
print('Connected:', conn)
conn.close()

The Cursor Object

To run SQL you need a cursor. Call conn.cursor() to get one, then use cursor.execute() to send SQL statements.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
print('Cursor ready:', cur)
conn.close()

Creating a Table

Define a schema with CREATE TABLE. Each column needs a name and a type such as INTEGER, TEXT, or REAL.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER, name TEXT, age INTEGER)')
print('Table created')
conn.close()

Primary Keys

A primary key uniquely identifies each row. Declaring INTEGER PRIMARY KEY makes SQLite auto-increment the value for you.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)')
print('books table ready')
conn.close()

IF NOT EXISTS

Running CREATE TABLE twice raises an error. Add IF NOT EXISTS so the statement is safe to run repeatedly.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
sql = 'CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)'
cur.execute(sql)
cur.execute(sql)
print('No error on second run')
conn.close()

Column Constraints

Constraints enforce data rules at the database level.

NOT NULL requires a value
UNIQUE forbids duplicates
DEFAULT supplies a fallback value

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, active INTEGER DEFAULT 1)')
print('accounts table with constraints')
conn.close()

Inspecting the Schema

SQLite stores schema info in sqlite_master. Query it to list your tables.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cur.fetchall())
conn.close()

Saving to a File

To persist data, pass a filename to connect(). The database survives after your program ends.

You must call conn.commit() to write changes to disk.

import sqlite3
conn = sqlite3.connect('app.db')
cur = conn.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT)')
conn.commit()
print('Saved to app.db')
conn.close()

Dropping a Table

Remove a table with DROP TABLE. Use IF EXISTS to avoid errors when it is already gone.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE temp (id INTEGER)')
cur.execute('DROP TABLE IF EXISTS temp')
print('Dropped temp table')
conn.close()

Always Close the Connection

Open connections hold file handles. Always close them with conn.close() when finished, or use a with block for automatic cleanup.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE t (id INTEGER)')
conn.close()
print('Connection closed cleanly')

Quick Check

Test your understanding of creating tables.

Recap

You learned the basics of sqlite3.

connect() opens (or creates) a database file
cursor() gives you an object to run SQL
CREATE TABLE defines schema with types and constraints
commit() persists changes and close() releases the connection

Next you will insert and read rows.

Frequently asked questions

Is the “Connecting and Creating Tables” lesson free?

Yes — the full text of “Connecting and Creating Tables” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Connecting and Creating Tables”?

Open a database and define schema. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Connecting and Creating Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy