Applied Programming/Databases/Python3 SQL

databases.pyEdit

"""This program demonstrates SQLite3 database processing.

Input:
    None

Output:
    Sample data.

References:
    https://en.wikiversity.org/wiki/Python_Programming/Databases
    
"""

import sqlite3
import sys

DATABASE = "users.db"

def execute_sql(sql):
    """Executes the given sql statement.
    
    Args:
        sql (string): A valid SQL statement to execute.
        
    Returns:
        None.
    
    """
    try:
        connection = sqlite3.connect(DATABASE)
    except:
        print("Unable to connect to %s" % DATABASE)
        raise

    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        connection.commit()
    except Exception as exception:
        print("Unable to execute %s" % sql)
        print(exception)
    finally:
        connection.close()


def create_table():
    """Creates the Users table.
    
    Args:
        None.
        
    Returns:
        None.
    
    """
    sql = "DROP TABLE IF EXISTS Users;"
    execute_sql(sql)
    
    sql = """
        CREATE TABLE Users(
            UserID INT PRIMARY KEY NOT NULL,
            User TEXT NOT NULL
        );
        """
    execute_sql(sql)


def insert_users():
    """Inserts data into the Users table.
    
    Args:
        None.
        
    Returns:
        None.
    
    """
    sql = "INSERT INTO Users(UserID, User) VALUES(1, 'Moe');"
    execute_sql(sql)

    sql = "INSERT INTO Users(UserID, User) VALUES(2, 'Larry');"
    execute_sql(sql)

    sql = "INSERT INTO Users(UserID, User) VALUES(3, 'Curly');"
    execute_sql(sql)


def display_table():
    """Displays the Users table.
    
    Args:
        None.
        
    Returns:
        None.
    
    """
    try:
        connection = sqlite3.connect(DATABASE)
    except:
        print("Unable to connect to %s" % DATABASE)
        raise

    try:
        cursor = connection.cursor()
    
        sql = """
            SELECT UserID, User FROM Users;
            """
        cursor.execute(sql)
        rows = cursor.fetchall()
    
        for row in rows:
            print(row)
        print()
    except Exception as exception:
        print("Error processing %s" % sql)
        print(exception)
    finally:
        connection.close()


def update_user():
    """Updates the Users table.
    
    Args:
        None.
        
    Returns:
        None.
    
    """
    sql = """
        UPDATE Users
        SET User = 'Shemp'
        WHERE UserID = 3;
        """
    execute_sql(sql)


def delete_user():
    """Deletes a record from the Users table.
    
    Args:
        None.
        
    Returns:
        None.
    
    """
    sql = """
        DELETE FROM Users 
        WHERE UserID = 3;
        """
    execute_sql(sql)


def main():
    """Runs the main program logic."""

    try:
        print("Users")
        create_table()
        insert_users()
        display_table()
    
        print("Users After Update")
        update_user()
        display_table()
    
        print("Users After Delete")
        delete_user()
        display_table()
    except:
        print("Unexpected error.")
        print("Error:", sys.exc_info()[1])
        print("File: ", sys.exc_info()[2].tb_frame.f_code.co_filename) 
        print("Line: ", sys.exc_info()[2].tb_lineno)
    

main()

Try ItEdit

Copy and paste the code above into one of the following free online development environments or use your own Python3 SQL compiler / interpreter / IDE.

See AlsoEdit