DEV Community: James K.

Mastering Host & Network Penetration Testing: A Windows CTF Walkthrough

James K. — Wed, 18 Feb 2026 07:45:28 +0000

Welcome back to what is transforming into a series of articles, documenting an upcoming pentester's journey in acquiring the required skills. This article documents how I went about solving the Host & Network Penetration Testing CTF, specifically the Windows-based target.

The lab was focused on testing the understanding of system or host based attacks, which exploit the vulnerabilities that exist within the configuartion, software or hardware of a specific machine, often leading to the compromise of systems via root or admin privilege escalation
The lab provided 2 target machines and useful wordlists to help in the capturing of the following four flags;

Flag 1
Hint: User 'bob' might not have chosen a strong password. Try common passwords to gain access to the server where the flag is located.

My first step is usually to carry out basic host discovery and enumeration to discover the open ports and services running on the target. Using Nmap, the results were:

The results show a web server, specifically a Microsoft IIS server running on port 80. Trying to access it via a web browser requests authentication with a username and password. With the username being provided in the hint, and a suggestion of a weak password, a brute force attack was the next step. Using hydra, together with the provided username and password wordlist, the weak password is promptly cracked .

With user credentials, next was to find where the flag was located on the server. Scanning with the tool Dirb and the credentials revealed the following directories in the webserver;

Navigating to the webdav directory on the browser revealed the first flag

Flag 2
Hint: Valuable files are often on the C: drive. Explore it thoroughly.

The hint suggests the flag may be located on the C:\ drive. Given we have already gained initial access, and we know the target is running a Microsoft IIS server, the next step is to check if we can exploit this to gain elevated privileges and view the files on the webserver.
I used the tool Davtest to scan the webserver and identify the files that can be uploaded and executed on the server.

The results show that the following types of files can be executed; html, asp and txt. This means we can upload a malicious .asp file to the server and create a backdoor when the file is executed. Using the tool Cadaver and the webshell.asp file provided in the lab environment, I uploaded the malicious file to the target.

We can access and execute the file just uploaded via the web browser, which results in the page below that accepts windows command in the text-box input field. Searching through the C:\ drive as suggested in the hint reveals the second flag, and its obtained using the type command.

Flag 3
Hint:By attempting to guess SMB user credentials, you may uncover important information that could lead you to the next flag.
The hint mentions guessing credentials, which means a brute force attack to try and obtain credentials to use for authentication. I used the Metasploit module auxiliary/scanner/smb/smb_login to execute a brute force attack on the target and discovered the following credentials, which can be used for privilege escalation.

I initially enumerated the shares using the session opened by the module, by setting the CreateSession option to true and connecting with the share there and found the flag. However, I was curious about another module I had learnt about, psexec. Using the exploit/windows/smb/psexec module, along with the admin credentials obtained in the previous step, I was able to obtain a meterpreter shell. Searching through the files and folders reveals the third flag.

Flag 4
Hint: The Desktop directory might have what you're looking for. Enumerate its contents.
This flag was straight-forward. Navigating to the Desktop folder reveals the last flag, completing the ctf.

Conclusion
This lab was a great exercise in chaining vulnerabilities. We started with a simple web brute-force, pivoted to exploiting WebDAV misconfigurations for a shell, and finally used SMB brute-forcing to gain full system access. It highlights why strong passwords and disabling unused features (like WebDAV) are critical for system hardening.

Tools Used

Nmap: For initial host discovery and port scanning.
Hydra: For brute-forcing the web login form.
Dirb: For directory enumeration to find hidden web paths.
Davtest: To scan the WebDAV server and identify allowed file uploads.
Cadaver: A command-line WebDAV client used to upload the malicious shell.
Metasploit Framework:
- auxiliary/scanner/smb/smb_login: Used for brute-forcing SMB credentials.
- exploit/windows/smb/psexec: Used to gain a Meterpreter shell.

Thanks for following along, and stay tuned for the next article!

eJPT Lab Walkthrough: Vulnerability Assessment

James K. — Mon, 19 Jan 2026 08:24:55 +0000

Hello or hello again, depending on whether or not you've encountered one of my previous articles. I've been documenting my progress learning pentesting and trying to earn the eJPT certification, specifically my process of solving the CTF challenges throughout the course.
This challenge was designed to test knowledge and skills in vulnerability assessment and identifying hidden information on a target web server.
It involved the use of tools like Nmap to discover open ports and services, as well as platforms like Nessus to detect misconfigurations, outdated software and potential vulnerabilities. This assessment helps in understanding the security posture of the target environment, providing insights into exploitable weaknesses that attackers maight leverage, helping to not only discover hidden threats but also develop startegies to mitigate them effectively.

Flag 1

Hint: Explore hidden directories for verson control artifacts that may reveal valuable info.

After a quick ping and nmap scan to confirm that the target was reachable, a simple nmap scan revealed the following open ports:

The hint mentioned version control, hinting at git or bitbucket directories. Heading to the url containing the git repo reveals the first flag.

The first flag is found at the flag.txt file.

Flag 2

Hint: The data storage has some loose security measures. Can you find the flag hidden within it?
The hint suggests that the database, discovered to be a mysql database from the scans,Version: 5.5.47-0 to be specific, has weak security measures. Further enumeration using Nmap NSE scripts revealed that the target was configured to block connection attempts, meaning a brute-force attack couldn't be carried out as well. The nmap results, however, detailed some interesting directories that are easily accessible. The most relevant one to the database was /phpmyadmin

The webpage reveals some databases, including mysql.

Further enumeration of the database reveals a table called secret_info, in which the second flag is found.

Flag 3

Hint: A PHP file that displays server information might be worth examining. What could be hidden in plain sight?
A quick search revealed that the file that displays PHP server info is likely to be phpinfo.php. The nmap results from the previous step showed that the file exists, and possibly contains information. Navigating to it from the website reveals the file.

Scanning the PHP Server configuration file reveals the third flag in the configuration section:

Flag 4

Hint: Sensitive directories might hold critical information. Search through carefully for hidden gems.
Following the hint, and more focused review of the results from the dirb scan from the previous step, the directory that immediately stood out was the passwords directory. Further enumeration of the directory revealed the files below, one being the final flag.

Even though the lab was finshed, I was still curious about what Nessus had to offer. Using the provided credentials to access the dashboard, I carried out a basic network scan after discovering the target host, and found the 24 vulnerabilities in total, but only 1 held a severity score of medium:

Further research revealed that the specific CVEs were CVE-2020-11022 and CVE-2020-11023. I went about trying to confirm that the target is indeed vulnerable to this exploit.
Using burpsuite to inspect the requests and response trying to find a point from which to execute the XSS attack, I found some interesting comments that provided the needed info.

The vulnerable header was User-Agent. Using the proxy to intercept and modify the request to test the simple script <script>alert("XSS-Test")</script> confirmed that the target was vulnerable.

To wrap things up, this lab really put my vulnerability assessment skills, particularly the second flag. The lab also highlights that tools aren't everything, and that manual investigation is key to finding the hidden stuff that automated scans might miss.

Cracking the Shell: Enumerating SMB and SSH in the INE Skill Check Lab

James K. — Sun, 04 Jan 2026 12:42:30 +0000

Hello or welcome back, depending on whether you read my past article. This one will detail another CTF along my learning path. It focused on enumeration techniques to identify and analyze running sevices on the target machine. The challenge required me to apply my knowledge of network and system enumeration to identify misconfigurations, weak credentials and potential security vulnerabilities. I had to capture the following 4 flags, and this article details how I went about that.

Flag 1

Hint: There is a samba share that allows anonymous access. Wonder what's in there!

My first step was to ping the target to confirm that it was reachable, then an nmap scan to discover the ports on which samba was running. I also used a few NSE scripts to further enumerate the ports. To enumerate the shares, I used enum4linux to determine which shares allowed anonymous login.

The results showed that there were two shares, but none of them allowed anonymous access. This meant that to find the share containing the flag, brute forcing the shares was needed. There was wordlist provided that we could use, and with a little help from ChatGPT, I came up with a simple script to loop through each share in the wordlist and test if anonymous access was allowed.

for share in $(cat your_wordlist.txt); do smbclient -N \\\\TARGET_IP\\$share -c "ls" 2>/dev/null && echo "[+] FOUND SHARE: $share"; done

Here's a brief explanation of the script:

for share in...: loops through every line in your wordlist.
smbclient ... \\$share: attempts to connect to that specific share name.
2>/dev/null: silences the "Bad Network Name" errors so the screen isn't flooded with failures.
&& echo ...: Only prints the name if the connection is successful.

After saving the script in a file and providing executable permissions, run the script to identify the share that allowed anonymous access. The share found is pubfiles, and has the first flag.

Access the share using the smbclient tool, using the command
smbclient //target.ine.local/pubfiles -N
After dowloading the file to our local system using the get command, we can view its contents and find the first flag.

Flag 2

Hint: One of the samba users have a bad password. Their private share with the same name as their username is at risk!

In the enum4linux results in the previous step, I found a few usernames, and as the hint suggests, one of these users has a weak password.

I used Metasploit to brute force passwords on the samba users, using the smb_login module. I created a simple text file containing the usernames found to set it as the user_file. The file containing the passwords to be tried was also provided. After setting all the required options and executing the exploit, the results provided the user and the weak password

The hint says that the share with the same name as their username is the one at risk, so access the share using smbclient:
smbclient //target.ine.local/josh -U josh
Again, download the file found and view its contents to get the second flag.

Flag 3

Hint: Follow the hint given in the previous flag to uncover this one.

The hint for this flag was left in the previous flag, which suggested that there's an FTP service running, and we should check the banner. To find the service and the port its running on, I used nmap and found the service running on port 5554.

Trying to connect using the ftp command reveals that the accounts of ashley, alice and amanda have weak passwords and they should be changed.

The next step is to bruteforce these users, and the tool I used is Hydra.
I added the newly discovered usernames to the users.txt file I had created earlier and found the weak password:

I found a module on metasploit, ftp_login that can be used to do the same. I tried it out and the results were the same.

Now having the credentials, connecting to the ftp service and getting the flag was simple.

Flag 4

Hint: This is a warning meant to deter unauthorized users from logging in.
Some research revealed that the SSH banner is the administrative message configured on a server to warn users before authentication. This hinted that the flag is in the banner. Trying to connect to the SSH service revealed the banner and the final flag.

Building an Automated Data Pipeline: Injuries vs Performance in the Premier League

James K. — Wed, 22 Oct 2025 14:31:49 +0000

Having spent the better part of the last two months learning the basics of data engineering, I wanted to build a project to test my understanding of the concepts. This article documents the process of building an automated ETL pipeline using Python, PostgreSQL, and Apache Airflow — including the bugs, the lessons learned, and the insights drawn from the data.

I settled on analyzing whether player injuries contributed to the worst season in Manchester United’s recent history. The club cited injuries as a major reason for their poor performance, but I wasn’t convinced. Since data rarely lies, I decided to test that claim myself by combining injury data and team performance statistics, using my newly acquired data engineering skills.

What started as a curiosity about one team quickly turned into a league-wide project.

Tech Stack

Language: Python
Database: PostgreSQL
Orchestration: Apache Airflow
Libraries: BeautifulSoup, Requests, psycopg2
Tools: ScraperAPI

The github repo to this project can be found here

ETL Process

Data Extraction

The project began by obtaining raw data from two main sources:
Premier Injuries – for player injury data
Understat – for match and player statistics

a. Injury Data

To scrape injury data, I initially tried Selenium, but quickly ran into Cloudflare’s human verification challenge (the “I’m not a robot” checkbox).
After experimenting with Playwright, I found a more reliable solution — ScraperAPI, a service that handles CAPTCHAs and rotating proxies automatically.

Using ScraperAPI with BeautifulSoup, I extracted HTML content and parsed the table containing injury data:

def extract_injury_data():
    payload = {
        'api_key': 'your_api_key',
        'url': 'https://www.premierinjuries.com/injury-table.php',
        'render': 'true' 
    }

    response = requests.get('https://api.scraperapi.com/', params=payload)
    soup = BeautifulSoup(response.text, 'html.parser')

    # Basic validation — check that the table or expected content exists
    if not soup.find("table"):
        raise ValueError("Expected injury table not found in the page HTML.")

    return soup

This function fetches the rendered page via ScraperAPI and returns the parsed HTML tree for later processing.

b. Performance Data

I then had to get the match statistics, so that I can compare the team's performance when certain players were unavailable due to injury to when they were fit and playing. I scraped this data from Understat website by extracting the JSON data that's embedded inside the JavaScript code on the webpage. I first sent the HTTP request, then parsed the HTML response from the server containing the entire webpage using BeautifulSoup. Next, I extracted the script tags from the page as this is how Understat embeds the match data. I then looped through each script tag looking for the datesData variable where match information is stored and extracted the json string. The JavaScript code on the page looks something like this :
var datesData = JSON.parse('{"2024-08-16":[{"id":"12345",...}]}');
The loop extracts the JSON strings between the positions JSON.parse(' and ) thus extracts something like : {"date":"2024-08-16"}

The resulting JSON string contains escaped characters such as apostrophes and backslashes that needed to be converted into actual characters before being parsed into a Python data structure that can actually be returned by the function. Extracting player statistics was a fairly similar process, only now I had to look for the playersData variable.


def extract_match_data():
    #Send HTTP request
    url = 'https://understat.com/league/EPL/2024'
    res = requests.get(url)
    #Parse response
    soup = BeautifulSoup(res.content, 'lxml')

    #find and loop through <script> tags to find relevant info and extract JSON string
    script_tags = soup.find_all('script')
    for script in script_tags:
        if 'datesData' in script.text:
            raw_text = script.string
            start = raw_text.find("JSON.parse('") + len("JSON.parse('")
            end = raw_text.find("')", start)
            json_str = raw_text[start:end]
            decoded_json = json_str.encode('utf8').decode('unicode_escape')
            return json.loads(decoded_json)

    # raise error if no data is extracted
    raise RuntimeError("Failed to extract match data from Understat.")

Data Transformation

Now that I had the raw data, I had to clean and validate the raw data so that I can use it for analysis. Some of the operations done on the data included:

Name Standardization: both team an player names are normalized to ensure consistent matching across both data sources.
Foreign Key Resolution: Player and team names are mapped to database IDs using lookup tables
Date Parsing: Various date formats are converted to ISO standard format
Validation: Incomplete match entries are skipped

After transformation, the data was structured into two main entities:

Matches – containing match ID, date, teams involved, expected goals (xG), and result.
Players and Player Stats – separating static player information from season performance data.
Each player’s record included:
Matches and minutes played
Goals and assists
Expected goals (xG) and expected assists (xA)

Data Loading

Transformed data was loaded into a PostgreSQL database using psycopg2.
The schema consisted of the following tables:

player_details
player_stats
matches
injuries

To ensure integrity and efficiency, I implemented:

UPSERT operations using ON CONFLICT for handling duplicate updates
Batch inserts for efficiency
Explicit transactions for commit/rollback safety
Timestamp tracking for debugging and data lineage

The database structure diagram below shows how the schema is set up.

Automation with Apache Airflow

Running scripts manually became inefficient and error-prone, especially as data volumes grew.
To automate the ETL process, I used Apache Airflow to define and schedule the workflow as a Directed Acyclic Graph (DAG).

with DAG(
    dag_id="injury_etl_dag",
    description="ETL pipeline for Understat and Premier Injuries data",
    schedule=timedelta(days=1),
    start_date=datetime(2024, 1, 1),
    catchup=False,
    default_args=default_args,
    tags=["injury", "understat", "ETL"]
) as dag:

Next, I added the tasks to be carried out and encapsulated each task in its own @task() function. The data is stored temporarily using Airflow's Xcom and passed between tasks. I then specified the task dependencies and the workflow sequence.

    @task()
    def extract_task(**kwargs):
        injury_html = extract_injury_data()
        matches_json = extract_match_data()
        players_raw = extract_understat_player_stats()

        kwargs["ti"].xcom_push(key="injury_html", value=injury_html.prettify())
        kwargs["ti"].xcom_push(key="matches_json", value=matches_json)
        kwargs["ti"].xcom_push(key="players_raw", value=players_raw)

    @task()
    def transform_task(**kwargs):
        from bs4 import BeautifulSoup

        ti = kwargs["ti"]
        conn = get_db_connection()
        team_name_to_id = get_team_name_to_id(conn)

        # Load XComs
        injury_html = ti.xcom_pull(key="injury_html", task_ids="extract_task")
        matches_json = ti.xcom_pull(key="matches_json", task_ids="extract_task")
        players_raw = ti.xcom_pull(key="players_raw", task_ids="extract_task")

        # Transform
        match_data = transform_match_data(matches_json, team_name_to_id)
        players, stats = transform_player_stats(players_raw, team_name_to_id, conn)
        injuries = transform_injury_data(BeautifulSoup(injury_html, "lxml"), conn, team_name_to_id)

        # Push to XComs
        ti.xcom_push(key="matches", value=match_data)
        ti.xcom_push(key="players", value=players)
        ti.xcom_push(key="player_stats", value=stats)
        ti.xcom_push(key="injuries", value=injuries)

    @task()
    def load_task(**kwargs):
        ti = kwargs["ti"]
        conn = get_db_connection()

        matches = ti.xcom_pull(key="matches", task_ids="transform_task")
        players = ti.xcom_pull(key="players", task_ids="transform_task")
        player_stats = ti.xcom_pull(key="player_stats", task_ids="transform_task")
        injuries = ti.xcom_pull(key="injuries", task_ids="transform_task")

        with conn.cursor() as cur:
            create_injury_schema(conn)
            load_teams(cur)
            insert_matches(cur, matches)
            insert_player_details(cur, players)
            insert_player_stats(cur, player_stats)
            conn.commit()

        load_injuries_data(conn, injuries)

    # Set dependencies
    extract_task() >> transform_task() >> load_task()

The diagram below shows successful DAG runs, eventually:

Data Analysis

Once the data is consolidated, it can now be used for statistical analysis to try and find the correlation between injury occurrences and team performance metrics. I was able to get team and plater stats for the whole season, but the data covering the injuries spanned a few weeks before my free trial on ScrapperAPI expired, so proper analysis of the entire season wasn't possible. With data covering the last third or so of the 2024 season, I was able to do the following analysis:

Frequency of injuries by team

Purpose: Determine teams with the most injuries
Finding: Man United had a lower number of injuries compared to other teams. Chelsea had the most with 4, and Arsenal had the least with none. Man United had one.

Injury Rate vs Win Percentage Analysis

Purpose: Determine if teams with more injuries perform worse
Finding: Man United had less injuries than the other teams, but performed worse, showing injuries were not the only cause of poor performance.

Challenges faced during building this pipeline.

a. Data extraction

The Cloudflare CAPTCHA test prevented me from using basic python libraries
to extract injury data, resulting in the need for a paid-for API for extraction. This also meant I could not get injury data spanning the entire season for proper analysis.

b. Data Normalization

Since data was collected from multiple sources referencing the same players, each source used different identifiers to represent them. To ensure consistency and eliminate redundancy, I applied database normalization techniques — standardizing player identifiers, resolving data inconsistencies, and structuring the data into related tables for more efficient storage and retrieval.

Conclusion

Building this pipeline was more than just a coding exercise — it was a practical test of my data engineering foundations. From web scraping and normalization to ETL automation and database design, the project taught me how data moves through every stage of an analytical workflow.
Even with partial data, the findings challenged assumptions about Manchester United’s poor season and highlighted how data engineering enables evidence-based analysis.
This project strengthened my grasp of ETL orchestration, workflow automation, and schema design — setting the stage for future work involving analytics dashboards and machine learning-driven insights.

Writing Clear Python: A Guide to F-Strings and Docstrings

James K. — Wed, 21 May 2025 09:34:25 +0000

In Python, writing clean, maintainable, and well-documented code is not just encouraged—it’s built into the language. Two standout features that help achieve this are f-strings and docstrings.
This article explores how these features work, why they matter, and how to use them effectively to write expressive and self-documenting Python code.

F-strings

A formatted string literal or f-string is a string literal that is prefixed with 'f' or 'F'. These strings may contain replacement fields, which are expressions delimited by curly braces {}. It’s like a shortcut for making strings with dynamic content.
F-strings were introduced in Python version 3.6 . Before Python 3.6, you had two main tools for interpolating values, variables, and expressions inside string literals:

The string interpolation operator (%), or modulo operator
The str.format() method

The Modulo Operator(%)

The modulo operator (%) was the first tool for string interpolation and formatting in Python and has been in the language since the beginning. Here’s what using this operator looks like in practice:

name = "Jane"
age = 25

"Hello, %s! You're %s years old." % (name, age)

Output

Hello, Jane! You're 25 years old.

In this example, we use a tuple of values as the right-hand operand to %. Note that we've used a string and an integer. Because you use the %s specifier, Python converts both objects to strings.

The str.format() Method

The str.format() method is an improvement compared to the % operator because it fixes a couple of issues and supports the string formatting mini-language. With .format(), curly braces delimit the replacement fields.

name = "Jane"
age = 25

"Hello, {}! You're {} years old.".format(name, age)

The example above outputs:
"Hello, Jane! You're 25 years old."

F-strings

F-strings joined the party in Python 3.6 with PEP 498. With f-strings, you can easily insert values or expressions directly into the string without much fuss. While other string literals always have a constant value, formatted strings are really expressions evaluated at run time. The parts of the string outside curly braces are treated literally, except that any doubled curly braces '{{' or '}}' are replaced with the corresponding single curly brace. A single opening curly bracket '{' marks a replacement field, which starts with a Python expression.

So, f-strings are like a simple way to put variables or expressions into your strings without having to use complicated formatting methods. It’s just a more straightforward way to make your strings dynamic and flexible.
Here's a simple example of using f-strings in Python:

name = "Jane"
age = 25

print(f"My name is {name} and I am {age} years old.")

In the example, f"My name is {name} and I am {age} years old." is an f-string — a string prefixed with f that allows expressions inside curly braces {} to be evaluated and included directly in the output.
{name} is replaced with the value "Jane".
{age} is replaced with the value 25.
Thus, the output is:

My name is Jane and I am 25 years old.

Notice how readable and concise the string is now that we’re using the f-string syntax. You don’t need operators or methods anymore. You just embed the desired objects or expressions in your string literal using curly brackets.

You can embed almost any Python expression in an f-string. This allows you to do some nifty things like the following:

name = "Jane"
age = 25

f"Hello, {name.upper()}! You're {age} years old."

In the example above, you embed a call to the .upper() string method in the first replacement field. Python runs the method call and inserts the uppercased name into the resulting string. The output will be:

Hello, JANE! You're 25 years old.

F-strings are a bit faster than both the modulo operator (%) and the .format() method, thus are a better method to use when working with strings.
More detailed info on f-strings can be found here: https://docs.python.org/3/reference/lexical_analysis.html#f-strings

DocStrings

A docstring is a string literal that occurs as the first statement in a module, function, class, or method definition. It is used to explain what something does and can be accessed via the .doc attribute. All modules should normally have docstrings, and all functions and classes exported by a module should also have docstrings. String literals occurring elsewhere in Python code may also act as documentation. They are not recognized by the Python bytecode compiler and are not accessible as runtime object attributes (i.e. not assigned to doc).

Conventions for writing clear, consistent docstrings are outlined in PEP 257. They include:

Using triple quotes ("""Docstring""") even for one-liners.
Starting with a summary line.
Following the summary with a more detailed description (optional).
Keeping the summary in the imperative mood (e.g., "Return the sum..." rather than "Returns...").

One-Line Docstrings

Ideal for simple functions or methods, a one-line docstring should:

Be enclosed in triple double quotes (""").
Fit on a single line.
Begin with a capital letter and end with a period.

Example

def add(x, y):
    """Return the sum of x and y."""
    return x + y

Multi-Line Docstrings

Multi-line docstrings start with a one-line summary, followed by a blank line, then a detailed description. The summary should be concise enough for indexing tools and clearly separated. The entire docstring should match the indentation of the code it documents.
Docstrings should always be followed by a blank line, especially in classes, to visually separate them from methods. Avoid using uppercase argument names in docstrings; list each argument on its own line using correct case-sensitive identifiers.
Different constructs have tailored docstring needs:

Scripts: Docstrings should serve as usage/help messages, explaining syntax, arguments, and environment variables.
Modules: Should list exported objects (classes, functions, etc.) with brief summaries.
Functions/Methods: Must describe behavior, arguments, return values, side effects, exceptions, and constraints.
Classes: Should summarize behavior, public members, and subclassing interfaces. If subclassing is involved, describe differences and use “override” or “extend” where appropriate.

Example

def complex(real=0.0, imag=0.0):
    """Form a complex number.

    Keyword arguments:
    real -- the real part (default 0.0)
    imag -- the imaginary part (default 0.0)
    """
    if imag == 0.0 and real == 0.0:
        return complex_zero
    ...

Comments vs Docstrings

Comments are descriptions that help programmers better understand the intent and functionality of the program. They are completely ignored by the Python interpreter.
As mentioned above, Python docstrings are strings used right after the definition of a function, method, class, or module. They are used to document our code.

We can access these docstrings using the doc attribute, for example:

class Person:
    """
    A class to represent a person.

    ...

    Attributes
    ----------
    name : str
        first name of the person
    surname : str
        family name of the person
    age : int
        age of the person

    Methods
    -------
    info(additional=""):
        Prints the person's name and age.
    """

    def __init__(self, name, surname, age):
        """
        Constructs all the necessary attributes for the person object.

        Parameters
        ----------
            name : str
                first name of the person
            surname : str
                family name of the person
            age : int
                age of the person
        """

        self.name = name
        self.surname = surname
        self.age = age

    def info(self, additional=""):
        """
        Prints the person's name and age.

        If the argument 'additional' is passed, then it is appended after the main info.

        Parameters
        ----------
        additional : str, optional
            More info to be displayed (default is None)

        Returns
        -------
        None
        """

        print(f'My name is {self.name} {self.surname}. I am {self.age} years old.' + additional)

Here, we can use the following code to access only the docstrings of the Person class:

print(Person.__doc__)

Output

 A class to represent a person.

    ...

    Attributes
    ----------
    name : str
        first name of the person
    surname : str
        family name of the person
    age : int
        age of the person

    Methods
    -------
    info(additional=""):
        Prints the person's name and age

We can also use the help() function to read the docstrings associated with various objects. Using the previous example, we can use the help function on the person class as:

help(Person)

Output

Help on class Person in module __main__:

class Person(builtins.object)
 |  Person(name, surname, age)
 |  
 |  A class to represent a person.
 |  
 |  ...
 |  
 |  Attributes
 |  ----------
 |  name : str
 |      first name of the person
 |  surname : str
 |      family name of the person
 |  age : int
 |      age of the person
 |  
 |  Methods
 |  -------
 |  info(additional=""):
 |      Prints the person's name and age.
 |  
 |  Methods defined here:
 |  
 |  __init__(self, name, surname, age)
 |      Constructs all the necessary attributes for the person object.
 |      
 |      Parameters
 |      ----------
 |          name : str
 |              first name of the person
 |          surname : str
 |              family name of the person
 |          age : int
 |              age of the person
 |  
 |  info(self, additional='')
 |      Prints the person's name and age.
 |      
 |      If the argument 'additional' is passed, then it is appended after the main info.
 |      
 |      Parameters
 |      ----------
 |      additional : str, optional
 |          More info to be displayed (default is None)
 |      
 |      Returns
 |      -------
 |      None
 |  
 |  ----------------------------------------------------------------------
 |  Data descriptors defined here:
 |  
 |  __dict__
 |      dictionary for instance variables (if defined)
 |  
 |  __weakref__
 |      list of weak references to the object (if defined)

Here, we can see that the help() function retrieves the docstrings of the Person class along with the methods associated with that class.

We can write docstring in many formats like the reStructured text (reST) format, Google format or the NumPy documentation format. To learn more, visit https://stackoverflow.com/questions/3898572/what-is-the-standard-python-docstring-format
We can also generate documentation from docstrings using tools like Sphinx. To learn more, visit https://www.sphinx-doc.org/en/master/

Smarter Databases with SQL Views and Triggers

James K. — Mon, 12 May 2025 08:50:32 +0000

The majority of developers become proficient in SQL's fundamental CRUD operations, but that's like saying you're a pro driver just because you can brake and steer. The advanced toolkit of SQL, which includes views, triggers, indexes, and cursors, is explored in this article. These features turn your database from a basic storage container into a more responsive, intelligent system. The fundamentals of data manipulation are INSERT, SELECT, UPDATE, and DELETE, but we're going to learn how these sophisticated features let your database handle half of the work.

Views

A view in SQL is a virtual table that is based on the result set of a SQL SELECT query. It does not store the data itself but rather provides a way to access and manipulate the data stored in one or more tables. Views can be used to simplify complex queries, enhance security by restricting access to specific data (columns or rows), and present data in a specific format. Some advantages of using views are;

Simplify Complex Queries: Encapsulate complex joins and conditions into a single object.
Enhance Security: Restrict access to specific columns or rows.
Present Data Flexibly: Provide tailored data views for different users.

Views are used in queries like regular tables but do not contain data themselves; they fetch data from the underlying tables. They do not accept parameters and they can often be updated, but this depends on the complexity of the view. Some views are read-only.

Key Operations with Views

1. Creating a View

Views are created using the CREATE VIEW statement.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

2. Usage:

Once created, views can be queried like regular tables.

SELECT * FROM view_name;

SQL allows us to delete an existing View. We can delete or drop View using the DROP statement. Here’s how to remove the MarksView:

DROP VIEW view_name;

Example Usage

Assume we have the following table employees;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

To create a view to display employees from the Sales department;

CREATE VIEW sales_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

You can then query the view like this;

SELECT * FROM sales_employees;

To update the view;

ALTER VIEW sales_employees AS
SELECT id, name, salary, ROUND(salary * 0.1, 2) AS bonus
FROM employees
WHERE department = 'Sales';

When dropping the view, it is a safer approach to use the IF EXISTS clause to prevent errors if the view does not exist. Thus, to drop the view;

DROP VIEW IF EXISTS sales_employees;

Conclusion

SQL Views provides an efficient solution for simplifying complex queries, improving security, and presenting data in a more accessible format. By mastering the creation, management, and updating of views, we can improve the maintainability and performance of our database systems.

Triggers

An SQL trigger is a database object containing SQL logic that is automatically executed when a specific database event occurs. In other words, a database trigger is "triggered" by a particular event.

Types of Triggers

DML Triggers (Data Manipulation Language): These triggers fire in response to data manipulation events (INSERT, UPDATE, DELETE).
DDL Triggers (Data Definition Language): These triggers fire in response to changes in the database schema (CREATE, ALTER, DROP).
Logon Triggers: These triggers fire in response to logon events to the SQL Server.

Timing of Triggers

BEFORE Triggers: These triggers execute before the triggering action is performed.
AFTER Triggers: These triggers execute after the triggering action is performed.
INSTEAD OF Triggers: These triggers replace the triggering action.

Key Operations with Triggers

1. Creating Triggers

The basic syntax of an SQL trigger includes the creation statement, the event that activates the trigger, and the SQL statements that define the trigger's actions. Here’s a general template for creating a trigger.

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

2. Disabling Triggers

PostgreSQL provides an easy way to disable triggers on a per-table basis using the ALTER TABLE command.

ALTER TABLE table_name DISABLE TRIGGER trigger_name;

Sample Usage

Using the Employees table schema in the previous section, the following are examples of triggers in action.

The BEFORE trigger below checks if the salary of a new employee is below 1000 before insertion. If it is, the trigger raises an error to prevent the insert, ensuring a minimum salary requirement is enforced.

-- Create a trigger function
CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the new salary is below 1000
    IF NEW.salary < 1000.00 THEN
        -- If it is, raise an exception and block the insert
        RAISE EXCEPTION 'Salary must be at least 1000.00';
    END IF;

    -- Otherwise, allow the insert to proceed
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create the BEFORE INSERT trigger using the function
CREATE TRIGGER before_insert_employees
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary();

The AFTER trigger runs after a new employee is added, and if they're in the IT department, it creates a new audit table with a timestamped name. This simulates a DDL operation triggered by specific conditions.

-- Create a trigger function
CREATE OR REPLACE FUNCTION create_audit_table()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the inserted employee is in the 'IT' department
    IF NEW.department = 'IT' THEN
        -- Dynamically generate a new audit table with a timestamped name
        -- This simulates a DDL operation inside the trigger
        EXECUTE 'CREATE TABLE IF NOT EXISTS audit_employees_' || to_char(NOW(), 'YYYYMMDD_HH24MISS') || ' (
            log_id SERIAL PRIMARY KEY,
            emp_id INT,
            action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )';
    END IF;

    -- Return NULL for AFTER trigger (since we don't need to modify row data)
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create the AFTER INSERT trigger using the function
CREATE TRIGGER after_insert_employees
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION create_audit_table();

Advantages of Triggers

Data Integrity: Triggers help enforce consistency and business rules, ensuring that data follows the correct format.
Automation: Triggers eliminate the need for manual intervention by automatically performing tasks such as updating, inserting, or deleting records when certain conditions are met.
Audit Trail: Triggers can track changes in a database, providing an audit trail of INSERT, UPDATE, and DELETE operations.
Performance: By automating repetitive tasks, triggers improve SQL query performance and reduce manual workload.

Conclusion

SQL triggers are extremely useful tools that can really enhance your database's performance because they automate tasks, ensure data integrity, and provide error handling and logging capabilities.

Summary

Understanding CRUD is only the first step in learning SQL; real efficiency comes from utilizing the strong, sometimes underutilized features that SQL has to offer. Views can streamline complicated searches and triggers can automate integrity checks. You're not just generating queries when you use these tools; you're creating more intelligent, self-sufficient data systems. Your entire stack will become more maintainable, which will reduce the amount of work your application must do.

SQL vs NoSQL: Choosing the Right Database for Your Needs

James K. — Tue, 29 Apr 2025 08:09:46 +0000

Introduction

When choosing a modern database, one of the biggest questions is whether to pick a relational or non-relational database. For most of the last 40 years, relational database management systems (RDBMSs) that used the programming language SQL have been preferred.
However, NoSQL-based non-relational database management systems are becoming more popular — particularly because data scientists want to expose their machine learning models and tools to more unstructured data.
Let's explore these database types in greater detail, examining their uses, advantages, and popular implementations.

Relational Databases

Relational databases organize data into interrelated tables, using a structured format that defines rows and columns within a schema. They connect information from various tables with keys. They use Structured Query Language (SQL) to store and retrieve data, hence the name SQL databases. A table utilizes columns to define the types of information being stored and rows to hold the actual data. Each table contains a column that must have unique values, referred to as the primary key. This primary key can be used in other tables to establish relationships between them. The images below represent how data is stored in these types of databases

Advantages of Relational Databases
• ACID compliance: They satisfy a set of priorities that measure the atomicity, consistency, isolation, and durability of database systems. The general principle is if one change fails, the whole transaction will fail, and the database will remain in the state it was in before the transaction was attempted.
• Better support options: Because RDBMS databases have been around for over 40 years, it's easier to get support and integrate data from other systems.
• Normalization: The process of normalization involves organizing the data so that data anomalies are reduced or eliminated. This, in turn, reduces storage costs.

Challenges
• Scalability: RDMSs are historically intended to be run on a single machine. This means that if the machine's requirements are insufficient due to data size or an increase in the frequency of access, you will have to improve the hardware, also known as vertical scaling. This can be incredibly expensive and has a ceiling, as eventually, the costs outweigh the benefits.
• Performance: The performance of the database is tightly linked to the number of tables, the complexity of their architecture, and the volume of data in each table. Query performance often degrades as RDMs scale.
• Flexibility: In relational databases, the schema is rigid. You define the columns and data types for those columns, including any restraints such as format or length.
• Difficulties with sharding (the process of dividing a large database into smaller parts for easier management)

Popular SQL Database Systems
MySQL
• Free and open-source
• An extremely established database with a huge community, extensive testing, and lots of stability
• Supports all major platforms
• Replication and sharding are available
• Simple to understand, very beginner-friendly

PostgreSQL
• Object-oriented database management system, functioning as a hybrid SQL/NoSQL database solution
• Free and open-source
• Compatibility with a wide range of operating systems
• Active community and many third-party service providers
• High ACID compliance
• Uses pure SQL
• Works best for use cases where data doesn't support a relational model, extra-large databases, and when running complicated queries

Oracle
• Commercial database with frequent updates, professional management, and excellent customer support
• Works with huge databases
• Simple upgrades
• Transaction control
• Compatible with all operating systems
• Suitable for enterprises and organizations with demanding workloads

NoSQL/Non-Relational Databases

Non-relational databases let you organize information in a looser fashion. They are also referred to as "Not Only SQL" (NoSQL) databases. Their storage models are optimized for specific types of data, allowing storage in diverse formats such as documents, key-value pairs, graphs, or columns. Below is an example of how data is stored in non-relational databases;

Types of Non-Relational Databases

Graph Stores
• Make data visualization easier
• Great at storing relationships between diverse data points with the help of nodes
• Common examples: Neo4j and JanusGraph

Column Stores
• Schema-agnostic databases that can handle the querying of non-sequential data in real-time
• Common use cases: web analytics and analyzing data from sensors
• Popular implementations: Apache Cassandra and HBase

Key-Value Stores
• Simple, fast database management systems that store key-value pairs
• Designed to fetch basic data quickly
• Common use cases: leaderboards and shopping cart data
• Popular implementations: Redis and Couchbase Server

Document Stores
• Databases with flexible schemas
• Best suited to store semi-structured data and can handle dynamic querying
• Common use cases: customer data, user-generated content, and order data
• Examples: MongoDB and PostgreSQL (which can function as both relational and document store)

Advantages of NoSQL Databases
• Excellent for handling "big data" analytics: NoSQL databases remove the bottleneck of needing to categorize and apply strict structures to massive amounts of information.
• No limits on types of data you can store: NoSQL databases give you unlimited freedom to store diverse types of data in the same place, offering flexibility to add new and different types at any time.
• Easier to scale: NoSQL databases are designed to be fragmented across multiple data centers without much difficulty.

Disadvantages of NoSQL Databases
• Support challenges: It can be more difficult to find experienced users when you need to troubleshoot.
• Compatibility and standardization issues: Newer NoSQL database systems lack the high degree of compatibility and standardization offered by SQL-based alternatives.

By far the most popular NoSQL database, MongoDB offers:
• Free to use
• Dynamic schema
• Horizontal scalability
• Excellent performance with simple queries
• Ability to add new columns and fields without impacting existing rows or application performance
• Ideal for companies experiencing rapid growth or those with substantial unstructured data
Other notable NoSQL alternatives include Apache Cassandra, Google Cloud BigTable, and Apache HBase.

Use Cases

When to Use Relational Databases
• Structured Data: For well-organized data that fits neatly into rows and columns with a standard format
• Complex Queries: Perfect for applications (like CRMs and financial systems) that require sophisticated queries and join operations
• ACID Transactions: Necessary for sectors where data accuracy, consistency, and reliability are crucial, such as finance or e-commerce
• Mature Ecosystem: Relational databases offer decades of tools, support, and integration options for enterprise-level requirements

When to Use Non-Relational (NoSQL) Databases
• Flexible Data Models: Ideal for semi-structured or unstructured data that may change over time, such as social media content
• Scalability: Designed to scale horizontally across multiple servers, making them excellent for big data, IoT applications, and growing businesses
• High Performance with Simple Queries: Great for real-time applications requiring quick reads and writes
The table below summarizes the differences between SQL and NoSQL databases.

Conclusion

The choice between SQL and NoSQL databases ultimately depends on your specific use case, data structure requirements, scalability needs, and performance considerations. Many modern applications even utilize both types of databases to leverage the strengths of each approach. Understanding the fundamentals of both paradigms will help you make informed decisions when designing your data architecture.