Python DB-API 2.0 Database Driver

A pure Python implementation of a DB-API 2.0 compliant database driver with in-memory storage. This project is designed for learning how database drivers work internally.

Features

• DB-API 2.0 (PEP 249) compliant interface
• In-memory database storage
• Basic SQL operations: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE
• Simple WHERE clause support (equality conditions)
• Standard DB-API exceptions
• Pure Python implementation (no C extensions)

Installation

No external dependencies required. Just clone the repository and use it:

git clone <repository-url>
cd learn-python-db-driver

Usage

Basic Example

import mydriver

# Create a connection
conn = mydriver.connect(":memory:")
cursor = conn.cursor()

# Create a table
cursor.execute("CREATE TABLE users (id INT, name TEXT)")

# Insert data
cursor.execute("INSERT INTO users VALUES (1, 'John')")
cursor.execute("INSERT INTO users VALUES (2, 'Jane')")
conn.commit()

# Query data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print(rows)  # [(1, 'John'), (2, 'Jane')]

# Update data
cursor.execute("UPDATE users SET name = 'Johnny' WHERE id = 1")
conn.commit()

# Delete data
cursor.execute("DELETE FROM users WHERE id = 2")
conn.commit()

# Clean up
cursor.close()
conn.close()

Using Fetch Methods

cursor.execute("SELECT * FROM users")

# Fetch one row at a time
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()

# Or fetch all at once
rows = cursor.fetchall()

# Or fetch in batches
rows = cursor.fetchmany(5)  # Fetch 5 rows

Cursor Attributes

cursor.execute("SELECT id, name FROM users")

# Get column descriptions
print(cursor.description)
# ((('id', 'INT', None, None, None, None, None),
#   ('name', 'TEXT', None, None, None, None, None),))

# Get number of rows affected
cursor.execute("UPDATE users SET name = 'John' WHERE id = 1")
print(cursor.rowcount)  # 1

Supported SQL Syntax

CREATE TABLE

CREATE TABLE table_name (col1 TYPE1, col2 TYPE2, ...)

INSERT

INSERT INTO table_name VALUES (val1, val2, ...)

SELECT

SELECT * FROM table_name
SELECT col1, col2 FROM table_name
SELECT * FROM table_name WHERE col = value
SELECT * FROM table_name WHERE col1 = value1 AND col2 = value2

UPDATE

UPDATE table_name SET col1 = value1, col2 = value2
UPDATE table_name SET col1 = value1 WHERE col2 = value2

DELETE

DELETE FROM table_name
DELETE FROM table_name WHERE col = value

DB-API Compliance

The driver implements the following DB-API 2.0 components:

• connect() function
• Connection class with cursor(), commit(), rollback(), close()
• Cursor class with execute(), fetchone(), fetchall(), fetchmany(), close()
• Standard exceptions: Error, DatabaseError, InterfaceError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, NotSupportedError
• Constants: apilevel = "2.0", threadsafety = 1, paramstyle = "qmark"

Running Tests

python -m unittest tests.test_driver

Or:

python tests/test_driver.py

Project Structure

mydriver/
├── __init__.py          # Module initialization, connect() function
├── exceptions.py         # DB-API exception classes
├── connection.py         # Connection class
├── cursor.py             # Cursor class
├── storage.py            # In-memory database storage
└── parser.py             # Simple SQL parser

tests/
└── test_driver.py        # Test suite

Limitations

This is a learning project and has several limitations:

• In-memory storage only (data is lost when connection closes)
• Basic SQL parsing (regex-based, not a full SQL parser)
• Simple WHERE clauses only (equality conditions with AND)
• No parameterized queries
• No JOIN operations
• No transactions (commit/rollback are no-ops)
• Limited type handling

Learning Resources

• PEP 249 - Python Database API Specification v2.0
• PyMySQL - Example of a pure Python driver
• Python sqlite3 documentation

License

This is an educational project for learning purposes.