Building a Database-Backed ATM Project in Python (Step-by-Step)

How I Built a Database-Driven ATM App in Python

After creating a basic ATM simulation in Python with just a balance = 1000 variable, I decided to upgrade the project by connecting it to a real database. Instead of storing data in memory, this version uses SQLite to keep track of each user’s name, password, and balance.

This turned my toy project into something more serious — and more fun to build!

What’s New Compared to My Previous Version?

Yesterday’s version was simple: one hardcoded balance variable and no user system. Once the script ended, everything reset.

Today’s version adds:

• A login system using username and password
• A users table inside an SQLite database
• Persistent data storage — data stays after closing the app
• Multiple users supported
• All balance changes saved directly in the database

New Python Concepts I Learned

To pull this off, I had to learn some new Python techniques for working with databases. Here’s a breakdown of each one:

import sqlite3

This is the built-in Python library for working with SQLite databases. No install needed!

import sqlite3

sqlite3.connect("atm.db")

This line connects to a SQLite database file. If the file doesn’t exist yet, Python creates it automatically.

conn = sqlite3.connect("atm.db")

cursor = conn.cursor()

A cursor is your gateway to the database. It lets you send SQL queries like SELECT or UPDATE.

cursor = conn.cursor()

cursor.execute(...)

This is how you run a single SQL command.

cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

Notice the ? — this is safer than using string concatenation because it prevents SQL injection.

cursor.executemany(...)

Useful for inserting multiple records at once.

cursor.executemany("INSERT INTO users (...) VALUES (?, ?, ?, ?)", user_list)

cursor.fetchone()

After running a SELECT query, this returns the first matching row — or None if there isn’t one.

user = cursor.fetchone()

conn.commit()

This saves any changes made to the database.

conn.commit()

Like clicking “Save” in a file — without this, changes might be lost.

conn.close()

This closes the database connection. Always do this when you’re done.

conn.close()

Summary Table of New Concepts

Concept	Purpose	Example
import sqlite3	Load SQLite module	import sqlite3
connect()	Open/create database file	sqlite3.connect("atm.db")
cursor()	Create command interface	conn.cursor()
execute()	Run a single SQL command	cursor.execute("SELECT * FROM users")
executemany()	Run multiple inserts	cursor.executemany(...)
fetchone()	Get a single result row	user = cursor.fetchone()
commit()	Save changes to the database	conn.commit()
close()	Safely close the database connection	conn.close()

Step 1: Creating the Database

Here’s the script I used to create the database and add some sample users:

import sqlite3

conn = sqlite3.connect("atm.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    password TEXT NOT NULL,
    balance REAL NOT NULL
)
""")

users = [
    ("alex123", "Alex", "1234", 1000.0),
    ("lisa456", "Lisa", "abcd", 1500.0),
    ("mark789", "Mark", "9999", 750.0)
]

cursor.executemany("""
INSERT OR IGNORE INTO users (username, name, password, balance)
VALUES (?, ?, ?, ?)
""", users)

conn.commit()
conn.close()

print("Database created and sample users added.")

Just run this once. It creates atm.db and fills it with users.

Step 2: Login Function

Users need to log in with a username and password before accessing their account.

def login():
    conn = sqlite3.connect("atm.db")
    cursor = conn.cursor()

    print("=== Login ===")
    while True:
        username = input("Username: ")
        password = input("Password: ")

        cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
        user = cursor.fetchone()

        if user:
            print(f"\nWelcome, {user[2]}! Your current balance is {user[4]} USD.\n")
            conn.close()
            return user
        else:
            print("Incorrect username or password. Please try again.\n")

Step 3: Full ATM Program

The main program combines login with the interactive ATM menu.

def main():
    user = login()
    user_id = user[0]

    while True:
        print("""=== ATM MENU ===
1. Check Balance
2. Deposit Money
3. Withdraw Money
q. Quit
""")
        action = input("Choose an option: ")

        conn = sqlite3.connect("atm.db")
        cursor = conn.cursor()

        if action == "1":
            cursor.execute("SELECT balance FROM users WHERE id = ?", (user_id,))
            balance = cursor.fetchone()[0]
            print(f"Your balance is {balance} USD.\n")

        elif action == "2":
            amount = float(input("Amount to deposit: "))
            cursor.execute("UPDATE users SET balance = balance + ? WHERE id = ?", (amount, user_id))
            conn.commit()
            print(f"{amount} USD deposited successfully.\n")

        elif action == "3":
            amount = float(input("Amount to withdraw: "))
            cursor.execute("SELECT balance FROM users WHERE id = ?", (user_id,))
            balance = cursor.fetchone()[0]

            if amount > balance:
                print("Insufficient funds.\n")
            else:
                cursor.execute("UPDATE users SET balance = balance - ? WHERE id = ?", (amount, user_id))
                conn.commit()
                print(f"{amount} USD withdrawn successfully.\n")

        elif action.lower() == "q":
            print("Thank you for using our ATM. Goodbye!\n")
            conn.close()
            break

        else:
            print("Invalid selection. Please try again.\n")

        conn.close()

if __name__ == "__main__":
    main()

What I Accomplished

Created a real database (atm.db)
Built a login system using credentials
Managed user balances via SQL queries
Learned how to structure data in a table
Got more confident using Python with SQLite