<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sibabalwe Sinyaniso on Medium]]></title>
        <description><![CDATA[Stories by Sibabalwe Sinyaniso on Medium]]></description>
        <link>https://medium.com/@sibabalwesinyaniso?source=rss-6b4bbe2935b6------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*qJhtjsrhhAIBpsMS1rUKEg.png</url>
            <title>Stories by Sibabalwe Sinyaniso on Medium</title>
            <link>https://medium.com/@sibabalwesinyaniso?source=rss-6b4bbe2935b6------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 09:11:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sibabalwesinyaniso/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Why Perform Database Operations with Python?]]></title>
            <link>https://sibabalwesinyaniso.medium.com/why-perform-database-operations-with-python-c97f18c9482d?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/c97f18c9482d</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Mon, 14 Sep 2026 22:44:17 GMT</pubDate>
            <atom:updated>2026-09-14T22:44:17.176Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_co3QXd9t2rCKglT.png" /></figure><p>Connecting Python to MySQL is only the beginning.</p><p>Once the connection works, the next challenge is learning how to work with the database efficiently when the amount of data grows or when several operations need to happen together.</p><p>Instead of inserting one record at a time or writing fragile database code, Python gives us tools for handling larger operations in a cleaner and more reliable way.</p><p>In this chapter, we’ll build on what we already know and look at bulk inserts, larger result sets, transactions, stored procedures, and database error handling.</p><h3>Before We Start</h3><p>To follow along, you should already have:</p><ul><li>Python installed</li><li>MySQL Server running</li><li>mysql-connector-python installed</li><li>The company_db database from the previous chapters</li><li>A basic understanding of connecting Python to MySQL</li></ul><p>We’ll continue using the same employees and departments tables from the previous articles.</p><h3>Setting Up Our Connection</h3><p>We’ll keep using a reusable function to connect to MySQL.</p><pre>import mysql.connector<br><br>def get_connection():<br>    &quot;&quot;&quot;Create and return a MySQL database connection.&quot;&quot;&quot;<br>    return mysql.connector.connect(<br>        host=&quot;localhost&quot;,<br>        user=&quot;root&quot;,<br>        password=&quot;yourpassword&quot;,<br>        database=&quot;company_db&quot;<br>    )</pre><p>In a real project, credentials should come from environment variables rather than being stored directly in the source code.</p><p>For now, we’ll keep the focus on database operations.</p><h3>Bulk Inserting Data</h3><p>In the previous chapter, we inserted one record at a time.</p><p>But what happens when we need to insert several employees?</p><p>Instead of repeatedly calling execute(), we can use executemany().</p><pre>insert_query = &quot;&quot;&quot;<br>    INSERT INTO employees (name, salary, department_id)<br>    VALUES (%s, %s, %s)<br>&quot;&quot;&quot;<br><br>employees = [<br>    (&quot;John&quot;, 72000, 1),<br>    (&quot;Emma&quot;, 68000, 2),<br>    (&quot;Michael&quot;, 85000, 3)<br>]<br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Insert multiple rows using one operation<br>        cursor.executemany(insert_query, employees)<br>        # Save the inserted records<br>        connection.commit()<br>        print(f&quot;{cursor.rowcount} employees inserted.&quot;)</pre><p>executemany() is useful when the same SQL statement needs to be executed for multiple sets of values.</p><p>It helps us:</p><ul><li>Avoid writing repeated INSERT statements</li><li>Keep the code cleaner</li><li>Handle batches of records more efficiently</li></ul><h3>Working with Larger Result Sets</h3><p>For small queries, using methods such as fetchone() or fetchall() is usually fine.</p><p>But when a query returns many rows, loading everything into memory at once may not always be necessary.</p><p>We can instead process the result one row at a time.</p><pre>select_query = &quot;&quot;&quot;<br>    SELECT<br>        employees.name,<br>        departments.department_name,<br>        employees.salary<br>    FROM employees<br>    JOIN departments<br>        ON employees.department_id = departments.id<br>&quot;&quot;&quot;<br><br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        cursor.execute(select_query)<br>        # Process each returned record individually<br>        for row in cursor:<br>            print(row)</pre><p>This approach is useful when we want to process records progressively rather than creating one large collection first.</p><p>The right fetching strategy depends on the amount of data and what the application needs to do with it.</p><h3>Using Transactions for Multiple Operations</h3><p>Transactions become especially important when several database changes belong to the same operation.</p><p>Imagine increasing the salaries of everyone in the Finance department.</p><pre>update_query = &quot;&quot;&quot;<br>    UPDATE employees<br>    JOIN departments<br>        ON employees.department_id = departments.id<br>    SET employees.salary = employees.salary * 1.10<br>    WHERE departments.department_name = %s<br>&quot;&quot;&quot;<br><br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        try:<br>            # Increase Finance salaries by 10%<br>            cursor.execute(update_query, (&quot;Finance&quot;,))<br>            # Save the transaction<br>            connection.commit()<br>            print(&quot;Salaries updated successfully.&quot;)<br>        except mysql.connector.Error as error:<br>            # Undo uncommitted changes if the operation fails<br>            connection.rollback()<br>            print(&quot;Transaction failed:&quot;, error)</pre><p>The important idea here is not the salary increase itself.</p><p>It is the relationship between:</p><p>execute() → commit() → rollback()</p><p>If the operation succeeds, we commit it.</p><p>If something goes wrong before the transaction is committed, we can roll it back.</p><p>This helps protect the database from incomplete operations.</p><h3>Calling Stored Procedures from Python</h3><p>A stored procedure is a named set of SQL statements stored inside MySQL.</p><p>Instead of sending the same SQL logic repeatedly from an application, some database logic can be defined once and called when needed.</p><p>For example, we could create a procedure that returns our employees.</p><pre>DELIMITER //<br><br>CREATE PROCEDURE GetEmployees()<br>BEGIN<br>    SELECT<br>        employees.name,<br>        departments.department_name,<br>        employees.salary<br>    FROM employees<br>    JOIN departments<br>        ON employees.department_id = departments.id;<br>END //<br>DELIMITER ;</pre><p>Once the procedure exists in MySQL, Python can call it.</p><pre>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Execute the stored procedure<br>        cursor.callproc(&quot;GetEmployees&quot;)<br><br># Read the result sets returned by the procedure<br>        for result in cursor.stored_results():<br>            for row in result:<br>                print(row)<br></pre><p>Stored procedures can be useful when database logic needs to be:</p><ul><li>Reused</li><li>Centralised inside the database</li><li>Exposed through controlled database operations</li></ul><p>They are not required for every application, but it is useful to understand how Python can interact with them.</p><h3>Handling MySQL Errors</h3><p>Database operations can fail for many reasons.</p><p>The server might be unavailable, a query might contain an error, or a constraint might reject the data being inserted.</p><p>Instead of allowing the program to fail without explanation, we can handle MySQL-specific exceptions.</p><pre>try:<br>    with get_connection() as connection:<br>        with connection.cursor() as cursor:<br>            # Example query<br>            cursor.execute(<br>                &quot;SELECT * FROM employees WHERE salary &gt; %s&quot;,<br>                (70000,)<br>            )<br><br>            for row in cursor:<br>                            print(row)<br>            except mysql.connector.Error as error:<br>                print(&quot;MySQL error:&quot;, error)</pre><p>Handling database exceptions allows us to:</p><ul><li>Understand what went wrong</li><li>Log useful error information</li><li>Roll back failed transactions when necessary</li><li>Decide how the application should respond</li></ul><p>In larger applications, we would normally log errors rather than relying only on print() statements.</p><h3>Connection Pooling</h3><p>So far, our examples create a new database connection whenever get_connection() is called.</p><p>That is perfectly reasonable while learning and for small scripts.</p><p>Applications receiving many database requests may instead use <strong>connection pooling</strong>.</p><p>A connection pool keeps a collection of database connections available so they can be reused.</p><p>MySQL Connector/Python provides pooling support.</p><pre>from mysql.connector import pooling<br><br>connection_pool = pooling.MySQLConnectionPool(<br>    pool_name=&quot;company_pool&quot;,<br>    pool_size=5,<br>    host=&quot;localhost&quot;,<br>    user=&quot;root&quot;,<br>    password=&quot;yourpassword&quot;,<br>    database=&quot;company_db&quot;<br>)<br><br>with connection_pool.get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Count the employees in the database<br>        cursor.execute(&quot;SELECT COUNT(*) FROM employees&quot;)<br>        total = cursor.fetchone()[0]<br>        print(&quot;Total employees:&quot;, total)</pre><p>You do not need connection pooling for every Python script.</p><p>It becomes more relevant when building applications that repeatedly communicate with the database.</p><h3>A Few Good Habits</h3><p>As our database code becomes more complex, a few habits become increasingly important:</p><ul><li>Use parameterized queries instead of building SQL with string concatenation.</li><li>Use executemany() when inserting multiple records with the same statement.</li><li>Commit changes only when an operation succeeds.</li><li>Roll back failed transactions when necessary.</li><li>Handle mysql.connector.Error when working with database operations.</li><li>Use context managers to close cursors and connections.</li><li>Keep database credentials outside source code.</li><li>Avoid retrieving more data than the application actually needs.</li></ul><p>These practices help make database code easier to maintain and safer to run.</p><h3>Practice: Put the Operations Together</h3><p>Try extending the company_db example yourself.</p><p>Using Python:</p><ul><li>Insert several employees with executemany()</li><li>Retrieve employees earning more than 70,000</li><li>Update salaries inside a transaction</li><li>Roll back the transaction if an error occurs</li><li>Call the GetEmployees stored procedure</li></ul><p>The goal is to become comfortable with the patterns rather than memorising individual commands.</p><h3>My Progress</h3><p>This is the fourth chapter in my <strong>MySQL in Python</strong> series.</p><p>We’ve now moved beyond basic CRUD operations and started working with database operations that are closer to what we may encounter in real applications.</p><p>At this point, we’ve covered:</p><ul><li>Bulk inserts</li><li>Processing query results</li><li>Transactions</li><li>Commit and rollback</li><li>Stored procedures</li><li>Error handling</li><li>Connection pooling</li></ul><p>The next step is to look at a different way of working with relational databases from Python.</p><h3>What’s Next?</h3><p>In the next chapter, we’ll introduce <strong>SQLAlchemy ORM with MySQL</strong> and see how Python objects can be used to work with relational database tables. Let’s keep building. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c97f18c9482d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why Connect MySQL with Python?]]></title>
            <link>https://sibabalwesinyaniso.medium.com/why-connect-mysql-with-python-c488271aa245?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/c488271aa245</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Sun, 13 Sep 2026 23:34:02 GMT</pubDate>
            <atom:updated>2026-09-13T23:34:02.140Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*lHOhRjfQIId-Ppkg.png" /></figure><p>Databases are useful on their own, but their real value appears when applications can interact with them programmatically.</p><p>A Python application might need to store users, retrieve products, update transactions, process business records, or save results generated by a script.</p><p>Instead of manually opening MySQL and running every query ourselves, Python can communicate with the database for us.</p><p>That is what we are going to start doing in this chapter.</p><p>We’ll connect Python to MySQL, execute SQL queries, retrieve data, make changes, and handle database transactions safely.</p><h3>Before We Start</h3><p>To follow along, you should have:</p><ul><li>Python installed</li><li>MySQL Server installed and running</li><li>The company_db database from the previous chapter</li><li>Basic knowledge of Python and SQLh</li></ul><p>We also need a Python package that allows our code to communicate with MySQL.</p><h3>Installing MySQL Connector for Python</h3><p>We’ll use <strong>MySQL Connector/Python</strong>.</p><p>Install it with pip:</p><pre>pip install mysql-connector-python</pre><p>Once installed, we can import it into Python:</p><pre>import mysql.connector</pre><p>This package gives Python the tools it needs to connect to a MySQL server and execute SQL commands.</p><h3>Creating a Reusable Database Connection</h3><p>Instead of repeating our connection details every time we need to access MySQL, we can create a reusable function.</p><pre>import mysql.connector<br><br>def get_connection():<br>    &quot;&quot;&quot;Create and return a connection to the MySQL database.&quot;&quot;&quot;<br>    return mysql.connector.connect(<br>        host=&quot;localhost&quot;,<br>        user=&quot;root&quot;,<br>        password=&quot;yourpassword&quot;,<br>        database=&quot;company_db&quot;<br>    )</pre><p>The connection requires a few important details:</p><ul><li>host tells Python where MySQL is running.</li><li>user specifies the MySQL account.</li><li>password authenticates the account.</li><li>database selects the database we want to work with.</li></ul><p>Because MySQL is running on the same computer in this example, we use localhost.</p><p>Whenever we need a connection, we can now call:</p><pre>connection = get_connection()</pre><h3>Testing the Connection</h3><p>Before performing database operations, it is useful to confirm that Python can actually connect to MySQL.</p><pre>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Ask MySQL which database is currently selected<br>        cursor.execute(&quot;SELECT DATABASE();&quot;)<br>        database_name = cursor.fetchone()<br>        print(&quot;Connected to database:&quot;, database_name[0])</pre><p>A <strong>cursor</strong> is the object we use to send SQL statements to MySQL.</p><p>The flow is essentially:</p><p><strong>Python → Connection → Cursor → SQL Query → MySQL → Result</strong></p><p>Once the connection works, we can start interacting with our data.</p><h3>Retrieving Data from MySQL</h3><p>Let’s retrieve the employees we created in the previous chapter.</p><pre>query = &quot;&quot;&quot;<br>    SELECT<br>        employees.name,<br>        departments.department_name,<br>        employees.salary<br>    FROM employees<br>    JOIN departments<br>        ON employees.department_id = departments.id<br>&quot;&quot;&quot;<br><br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Execute the SQL query<br>        cursor.execute(query)<br>        # Process each returned row<br>        for row in cursor:<br>            print(row)</pre><p>Python sends the query to MySQL, and MySQL returns the matching records.</p><p>The result could look something like:</p><pre>(&#39;Alice&#39;, &#39;HR&#39;, Decimal(&#39;60000.00&#39;))<br>(&#39;Bob&#39;, &#39;Finance&#39;, Decimal(&#39;75000.00&#39;))<br>(&#39;Charlie&#39;, &#39;Engineering&#39;, Decimal(&#39;90000.00&#39;))</pre><p>The data still lives inside MySQL, but Python can now access and work with it.</p><p>We could display it in an application, transform it, analyse it, or use it as part of a larger program.</p><h3>Inserting Data from Python</h3><p>Python can also add records to the database.</p><p>Suppose we want to add another employee.</p><pre>insert_query = &quot;&quot;&quot;<br>    INSERT INTO employees (name, salary, department_id)<br>    VALUES (%s, %s, %s)<br>&quot;&quot;&quot;<br><br>employee = (&quot;David&quot;, 68000, 2)<br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Insert the employee using parameterized values<br>        cursor.execute(insert_query, employee)<br>        # Save the change to the database<br>        connection.commit()<br>        print(&quot;Employee added successfully.&quot;)</pre><p>Notice that we are using %s placeholders instead of directly inserting the values into the SQL string.</p><p>This is called a <strong>parameterized query</strong>.</p><p>Parameterized queries are important because they:</p><ul><li>Handle values safely</li><li>Reduce formatting errors</li><li>Help protect applications against SQL injection</li></ul><p>Values coming from users or Python variables should generally be passed as parameters rather than manually joined into an SQL statement.</p><h3>Updating Data</h3><p>Updating existing records follows the same pattern.</p><pre>update_query = &quot;&quot;&quot;<br>    UPDATE employees<br>    SET salary = %s<br>    WHERE name = %s<br>&quot;&quot;&quot;<br><br>values = (72000, &quot;David&quot;)<br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Update David&#39;s salary<br>        cursor.execute(update_query, values)<br>        # Save the change<br>        connection.commit()<br>        print(&quot;Employee updated successfully.&quot;)</pre><p>Python supplies the values, while SQL describes what should happen inside the database.</p><h3>Deleting Data</h3><p>We can also delete records from Python.</p><pre>delete_query = &quot;&quot;&quot;<br>    DELETE FROM employees<br>    WHERE name = %s<br>&quot;&quot;&quot;<br><br>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        # Delete the matching employee<br>        cursor.execute(delete_query, (&quot;David&quot;,))<br>        # Save the change<br>        connection.commit()<br>        print(&quot;Employee deleted successfully.&quot;)</pre><p>Be especially careful when working with DELETE and UPDATE.</p><p>For example:</p><pre>DELETE FROM employees;</pre><p>would remove every row from the table.</p><p>Adding a WHERE clause allows us to target specific records.</p><pre>DELETE FROM employees<br>WHERE name = &#39;David&#39;;</pre><p>Before running a destructive query, always understand which records it will affect.</p><h3>Understanding Commit and Rollback</h3><p>Operations such as INSERT, UPDATE, and DELETE change the database.</p><p>To permanently save those changes, we use:</p><pre>connection.commit()</pre><p>Sometimes, however, an operation fails.</p><p>In that situation, we can use rollback() to undo changes that have not yet been committed.</p><pre>with get_connection() as connection:<br>    with connection.cursor() as cursor:<br>        try:<br>            # Attempt to update an employee<br>            cursor.execute(<br>                &quot;UPDATE employees SET salary = %s WHERE name = %s&quot;,<br>                (75000, &quot;David&quot;)<br>            )<br>          # Save the transaction<br>            connection.commit()<br>        except mysql.connector.Error as error:<br>            # Undo uncommitted changes if something goes wrong<br>            connection.rollback()<br>            print(&quot;Database error:&quot;, error)</pre><p>This becomes especially important when one operation contains several related database changes.</p><p>We do not want part of an operation to succeed while another part fails.</p><h3>Avoid Hardcoding Credentials</h3><p>Hardcoding database credentials is convenient while learning:</p><pre>password=&quot;yourpassword&quot;</pre><p>But real passwords should not be stored directly inside source code.</p><p>A better approach is to use environment variables.</p><pre>import os<br>import mysql.connector<br><br>def get_connection():<br>    &quot;&quot;&quot;Create a connection using environment variables.&quot;&quot;&quot;<br>    return mysql.connector.connect(<br>        host=&quot;localhost&quot;,<br>        user=os.getenv(&quot;MYSQL_USER&quot;),<br>        password=os.getenv(&quot;MYSQL_PASSWORD&quot;),<br>        database=&quot;company_db&quot;<br>    )</pre><p>This keeps sensitive information outside the Python script.</p><p>It is particularly important when working with Git and GitHub.</p><p>Never push real database passwords or other secrets into a public repository.</p><h3>The Pattern to Remember</h3><p>Most MySQL operations from Python follow the same general process:</p><ul><li>Open a database connection</li><li>Create a cursor</li><li>Write the SQL query</li><li>Execute the query</li><li>Pass values as parameters when needed</li><li>Retrieve results or commit changes</li><li>Close the database resources</li></ul><p>Once this pattern becomes familiar, interacting with databases from Python becomes much easier.</p><p>The SQL does not disappear.</p><p>Python simply becomes responsible for sending those SQL commands and working with the results.</p><h3>Practice: Perform CRUD Operations from Python</h3><p>Try putting everything together yourself.</p><p>Using the company_db database:</p><ul><li>Insert a new employee</li><li>Retrieve that employee</li><li>Update the employee’s salary</li><li>Retrieve the record again to confirm the change</li><li>Delete the employee</li></ul><p>Use parameterized queries whenever you pass values into SQL.</p><p>This gives you practice with the four basic database operations:</p><p><strong>Create, Read, Update, and Delete — CRUD.</strong></p><h3>My Progress</h3><p>This is the third chapter in my <strong>MySQL in Python</strong> series.</p><p>So far, we have moved from understanding MySQL, to designing tables, and now to interacting with the database directly from Python.</p><p>At this point, we can:</p><ul><li>Connect Python to MySQL</li><li>Execute SQL using a cursor</li><li>Retrieve records</li><li>Insert data</li><li>Update data</li><li>Delete data</li><li>Commit and roll back transactions</li><li>Use parameterized queries</li><li>Keep database credentials outside our source code</li></ul><p>This is where Python and MySQL finally start working together as part of the same application.</p><h3>What’s Next?</h3><p>In the next chapter, we’ll explore <strong>MySQL Database Operations Using Python</strong> and build on what we’ve learned here.</p><p>Follow me here on Medium if you’d like to continue the series. Let’s keep building. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c488271aa245" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating Databases and Tables in MySQL]]></title>
            <link>https://sibabalwesinyaniso.medium.com/creating-databases-and-tables-in-mysql-67a388b8f4e1?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/67a388b8f4e1</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Sat, 12 Sep 2026 23:07:12 GMT</pubDate>
            <atom:updated>2026-09-12T23:07:12.111Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*V-_dyFI6yUOwNRpT.png" /></figure><p>A database is only as useful as the structure behind it.</p><p>You can store thousands of records, but if your tables are poorly designed or your data is duplicated everywhere, working with that database becomes difficult very quickly.</p><p>In the previous chapter, I introduced MySQL and looked at why relational databases are useful.</p><p>Now it is time to start building one.</p><p>In this chapter, we’ll create a simple company database, define related tables, introduce constraints, and add some sample data.</p><h3>Before We Start</h3><p>To follow along, you should have:</p><ul><li>MySQL installed</li><li>MySQL Server running</li><li>MySQL Workbench, the MySQL command line, or another SQL client</li><li>A basic understanding of SQL</li></ul><p>We will keep the database simple so that the focus stays on structure rather than complexity.</p><h3>Creating a Database</h3><p>A database acts as a container for the tables and data used by an application.</p><p>For this example, we’ll create a database called company_db.</p><pre>-- Create a new database<br>CREATE DATABASE company_db;<br><br>-- Select the database we want to work with<br>USE company_db;</pre><p>Once USE company_db has been executed, the tables we create will belong to that database.</p><h3>Creating the Departments Table</h3><p>Let’s start with departments.</p><p>A company could have departments such as Finance, Human Resources, Engineering, or Marketing.</p><pre>-- Create a table for company departments<br>CREATE TABLE departments (<br>    id INT AUTO_INCREMENT PRIMARY KEY,<br>    department_name VARCHAR(50) NOT NULL UNIQUE<br>);</pre><p>There are already several important concepts inside this table:</p><ul><li>INT stores whole numbers.</li><li>AUTO_INCREMENT automatically generates the next ID.</li><li>PRIMARY KEY uniquely identifies each department.</li><li>VARCHAR(50) stores text up to 50 characters.</li><li>NOT NULL means the value is required.</li><li>UNIQUE prevents duplicate department names.</li></ul><p>For example, the database should not contain two separate departments both called Finance.</p><h3>Creating the Employees Table</h3><p>Now we can create the employees table.</p><p>Each employee can belong to a department.</p><pre>-- Create employees and link them to departments<br>CREATE TABLE employees (<br>    id INT AUTO_INCREMENT PRIMARY KEY,<br>    name VARCHAR(100) NOT NULL,<br>    salary DECIMAL(10,2) CHECK (salary &gt; 0),<br>    department_id INT,<br>    FOREIGN KEY (department_id)<br>        REFERENCES departments(id)<br>        ON DELETE SET NULL<br>);</pre><p>The main columns are:</p><ul><li>id uniquely identifies each employee.</li><li>name stores the employee&#39;s name.</li><li>salary stores the employee&#39;s salary.</li><li>department_id stores the ID of the employee&#39;s department.</li></ul><p>The important part is the foreign key:</p><pre>-- Link department_id to the departments table<br>FOREIGN KEY (department_id)<br>    REFERENCES departments(id)</pre><p>This tells MySQL that department_id refers to a record in the departments table.</p><p>Instead of repeatedly storing the word Finance or Engineering inside the employees table, we store the ID of the related department.</p><p>That is one of the main ideas behind relational database design.</p><h3>Why Constraints Matter</h3><p>Constraints are rules applied to a database structure.</p><p>They help prevent invalid or inconsistent data from being stored.</p><p>The main constraints we are using are:</p><ul><li><strong>PRIMARY KEY</strong> — uniquely identifies each row.</li><li><strong>NOT NULL</strong> — requires a value.</li><li><strong>UNIQUE</strong> — prevents duplicate values.</li><li><strong>FOREIGN KEY</strong> — creates and protects relationships between tables.</li><li><strong>CHECK</strong> — validates values before they are stored.</li></ul><p>For example:</p><pre>-- Only allow salary values greater than zero<br>salary DECIMAL(10,2) CHECK (salary &gt; 0)</pre><p>Instead of relying entirely on the application to validate salary values, the database can also enforce the rule itself.</p><p>This helps protect data integrity.</p><h3>Understanding the Relationship</h3><p>Our database now contains two tables.</p><p>The departments table:</p><ul><li>Stores department information.</li><li>Gives each department a unique ID.</li></ul><p>The employees table:</p><ul><li>Stores employee information.</li><li>References a department using department_id.</li></ul><p>This creates a <strong>one-to-many relationship</strong>.</p><p>One department can have many employees, while each employee can reference one department.</p><p>For example, the Finance department could contain several employees, but the Finance department itself only needs to be stored once.</p><h3>Adding Some Data</h3><p>Now that the structure exists, we can insert a few records.</p><p>First, let’s add some departments.</p><pre>-- Add departments<br>INSERT INTO departments (department_name)<br>VALUES<br>    (&#39;HR&#39;),<br>    (&#39;Finance&#39;),<br>    (&#39;Engineering&#39;);</pre><p>Now we can add employees and associate them with those departments.</p><pre>-- Add employees and reference their departments<br>INSERT INTO employees (name, salary, department_id)<br>VALUES<br>    (&#39;Alice&#39;, 60000, 1),<br>    (&#39;Bob&#39;, 75000, 2),<br>    (&#39;Charlie&#39;, 90000, 3);</pre><p>The employee records do not need to repeat all the department information.</p><p>They only store the ID of the related department.</p><h3>Retrieving Related Data</h3><p>Because the information is stored across separate tables, we can combine it when we need it.</p><p>This is where SQL joins become useful.</p><pre>-- Retrieve employees together with their department names<br>SELECT<br>    employees.name,<br>    departments.department_name,<br>    employees.salary<br>FROM employees<br>JOIN departments<br>    ON employees.department_id = departments.id;</pre><p>The result would contain information similar to this:</p><p>namedepartment_namesalaryAliceHR60000BobFinance75000CharlieEngineering90000</p><p>The data comes from two different tables, but SQL allows us to retrieve it as one result.</p><h3>A Few Good Database Design Habits</h3><p>Good database design starts before you write large amounts of SQL.</p><h3>Use Meaningful Names</h3><p>Table and column names should clearly describe what they represent.</p><p>Good examples include:</p><ul><li>employees</li><li>departments</li><li>department_id</li><li>salary</li></ul><p>Names such as table1, data, or value2 make a database much harder to understand later.</p><h3>Avoid Repeating the Same Data</h3><p>If the same information appears repeatedly, it may belong in its own table.</p><p>That is why we created a departments table instead of repeatedly storing department names for every employee.</p><h3>Use Constraints</h3><p>Constraints allow the database itself to enforce important rules.</p><p>Useful constraints include:</p><ul><li>PRIMARY KEY</li><li>FOREIGN KEY</li><li>NOT NULL</li><li>UNIQUE</li><li>CHECK</li></ul><h3>Think About Relationships Early</h3><p>Before creating tables, think about how the different pieces of data relate to one another.</p><p>Ask questions such as:</p><ul><li>Can one record be related to many other records?</li><li>Does a value belong in its own table?</li><li>Which table should contain the foreign key?</li></ul><p>Planning relationships early can prevent unnecessary schema changes later.</p><h3>Practice: Design a Small Library Database</h3><p>A good way to understand database structure is to build a small one yourself.</p><p>Try creating a database called library_db.</p><p>Your database should contain:</p><ul><li>An authors table</li><li>A books table</li><li>A relationship connecting each book to its author</li></ul><p>Think about:</p><ul><li>Which columns need primary keys?</li><li>Which values should be required?</li><li>Where should the foreign key go?</li><li>Which values should be unique?</li></ul><p>Once the tables are created, insert a few authors and books and try retrieving each book together with its author.</p><p>The goal is not to build a large system.</p><p>The goal is to practise thinking about tables, relationships, and data integrity.</p><h3>My Progress</h3><p>This is the second chapter in my <strong>MySQL in Python</strong> series.</p><p>So far, we’ve moved from understanding what MySQL is to actually creating a relational database.</p><p>We’ve now covered:</p><ul><li>Creating a database</li><li>Creating tables</li><li>Choosing basic data types</li><li>Using primary and foreign keys</li><li>Applying constraints</li><li>Creating relationships between tables</li><li>Adding and retrieving sample data</li></ul><p>The next step is where Python starts entering the picture.</p><h3>What’s Next?</h3><p>In the next chapter, we’ll look at <strong>Connecting to MySQL Using Python</strong>.</p><p>We’ll start learning how Python can:</p><ul><li>Connect to a MySQL server</li><li>Access a database</li><li>Send SQL commands</li><li>Work with query results</li><li>Close database connections correctly</li></ul><p>This is where the MySQL and Python sides of the series finally begin coming together.</p><p>Follow me here on Medium if you’d like to continue the series.</p><p>Let’s keep building. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=67a388b8f4e1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why MySQL Matters in Modern Databases]]></title>
            <link>https://sibabalwesinyaniso.medium.com/why-mysql-matters-in-modern-databases-5ceca9971040?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/5ceca9971040</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Fri, 11 Sep 2026 23:06:56 GMT</pubDate>
            <atom:updated>2026-09-11T23:06:56.323Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bpoB8sm-PIbgSkvcJjck8w.png" /></figure><p>Most applications eventually need somewhere reliable to store their data.</p><p>User accounts, transactions, products, orders, and application settings all need to be stored in a structured way so they can be retrieved and updated when needed.</p><p>That is where databases come in.</p><p>As I start working with <strong>MySQL in Python</strong>, I want to begin with the database itself. Before connecting Python to MySQL, it makes sense to understand what MySQL is, how it works, and why developers still use it.</p><h3>What Is MySQL?</h3><p>MySQL is an <strong>open-source relational database management system</strong>, commonly referred to as an RDBMS.</p><p>It uses <strong>Structured Query Language (SQL)</strong> to store, retrieve, update, and manage data.</p><p>In a relational database, data is organised into tables containing rows and columns.</p><p>For example, an employees table could contain:</p><ul><li>Employee ID</li><li>Name</li><li>Department</li><li>Salary</li></ul><p>Each row represents an individual record, while each column describes the information stored about that record.</p><p>Tables can also be related to one another.</p><p>An employees table could, for example, be connected to a department table rather than storing the same department information repeatedly.</p><p>This relational structure is one of the main ideas behind MySQL.</p><h3>How MySQL Works</h3><p>MySQL follows a <strong>client-server model</strong>.</p><p>The MySQL server stores and manages the database, while a client connects to the server and sends requests using SQL.</p><p>The process is straightforward:</p><ul><li>A client sends a query to MySQL.</li><li>MySQL receives and processes the query.</li><li>The requested database operation is performed.</li><li>The result is returned to the client.</li></ul><p>For example:</p><pre>-- Retrieve all employees from the table<br>SELECT *<br>FROM employees;</pre><p>The client could be:</p><ul><li>MySQL Workbench</li><li>A database management tool</li><li>A web application</li><li>A backend service</li><li>A Python program</li></ul><p>That last one is where this series is heading.</p><p>Instead of manually entering queries into MySQL, we will eventually use Python to communicate with the database for us.</p><h3>Important MySQL Concepts</h3><p>Before we start creating databases and tables, there are a few concepts worth understanding.</p><h3>Primary Keys</h3><p>A <strong>primary key</strong> uniquely identifies each record in a table.</p><p>For example, an employee_id could be used to distinguish one employee from another.</p><p>Even if two employees have the same name, their IDs should still be different.</p><h3>Foreign Keys</h3><p>A <strong>foreign key</strong> creates a relationship between two tables.</p><p>For example, an employee could reference a department stored in a separate departments table.</p><p>This allows us to organise related data without repeatedly storing the same information.</p><h3>Indexes</h3><p>Indexes help MySQL find data faster.</p><p>They become especially useful when tables grow and queries need to search through large amounts of data.</p><h3>Transactions</h3><p>Transactions allow multiple database operations to be treated as one unit of work.</p><p>This is important when several related operations need to succeed together.</p><p>A banking transaction is a good example. If money is removed from one account but fails to reach another, the database should not leave the transaction half-complete.</p><h3>Why Use MySQL?</h3><p>MySQL remains popular because it combines simplicity with features needed by real applications.</p><p>Some of its main advantages include:</p><ul><li>It is open source.</li><li>It runs on Windows, Linux, and macOS.</li><li>It has extensive documentation.</li><li>It has a large developer community.</li><li>It supports relational database features such as keys, indexes, and transactions.</li><li>It integrates with many programming languages.</li><li>It is available through major cloud providers.</li></ul><p>MySQL has also been around for decades, which means its ecosystem is mature.</p><p>There are plenty of tools, libraries, frameworks, tutorials, and hosting options available for developers working with it.</p><h3>The Role of InnoDB</h3><p>MySQL supports different storage engines, but for most modern applications, <strong>InnoDB</strong> is the default.</p><p>InnoDB supports important features such as:</p><ul><li>Transactions</li><li>Foreign keys</li><li>Crash recovery</li><li>Row-level locking</li></ul><p>For someone starting with MySQL, InnoDB is usually the main storage engine worth knowing about.</p><p>There are other storage engines available, but we do not need to explore them in depth yet.</p><h3>Where Is MySQL Used?</h3><p>MySQL can be used in many different types of systems.</p><p>Common examples include:</p><ul><li>Web applications</li><li>E-commerce platforms</li><li>Business systems</li><li>Content management systems</li><li>APIs</li><li>Backend applications</li><li>Reporting systems</li></ul><p>An application might use MySQL to store:</p><ul><li>Users</li><li>Customers</li><li>Products</li><li>Orders</li><li>Transactions</li><li>Business records</li><li>Application settings</li></ul><p>WordPress is one well-known example of software that commonly uses a MySQL-compatible database.</p><p>The use case may change, but the basic idea remains the same: the application handles the logic while MySQL handles the persistent data.</p><h3>MySQL and Performance</h3><p>As applications grow, database performance becomes more important.</p><p>MySQL provides several ways to improve how efficiently data is handled.</p><p>Some of the main areas include:</p><ul><li>Indexing</li><li>Query optimisation</li><li>Replication</li><li>Proper database design</li></ul><p>One useful MySQL tool is EXPLAIN.</p><p>It helps us understand how MySQL plans to execute a query.</p><pre>-- Show how MySQL plans to execute this query<br>EXPLAIN<br>SELECT *<br>FROM employees<br>WHERE salary &gt; 50000;</pre><p>This can be useful when trying to understand why a query is slow.</p><p>At this stage, though, it is enough to know that MySQL gives us tools for analysing and improving database performance.</p><h3>Security in MySQL</h3><p>Databases often contain sensitive or important information, so access should be controlled carefully.</p><p>MySQL includes features such as:</p><ul><li>User authentication</li><li>Roles and privileges</li><li>Encrypted connections</li><li>Database-level permissions</li><li>Table-level permissions</li></ul><p>One important principle is <strong>least privilege</strong>.</p><p>A user or application should only have the permissions it actually needs.</p><p>For example, an application that only needs to read data should not automatically be given permission to delete tables.</p><p>This becomes especially important when an application moves from development into production.</p><h3>Why MySQL Matters for Python</h3><p>This is the part that connects everything back to the main goal of this series.</p><p>Knowing SQL is useful on its own, but Python allows us to start interacting with databases programmatically.</p><p>Eventually, a Python program will be able to:</p><ul><li>Connect to a MySQL server</li><li>Send SQL queries</li><li>Retrieve records</li><li>Insert new data</li><li>Update existing data</li><li>Delete records</li><li>Process query results inside Python</li></ul><p>This is where Python and SQL stop feeling like separate skills.</p><p>They start working together as part of the same application.</p><p>A Python application can handle the logic, while MySQL takes care of storing and managing the data.</p><h3>My Progress</h3><p>This chapter marks the beginning of my <strong>MySQL in Python</strong> series.</p><p>I’m starting with the MySQL fundamentals before bringing Python into the picture.</p><p>The learning path will gradually move through:</p><ul><li>Understanding MySQL</li><li>Creating databases and tables</li><li>Working with SQL</li><li>Connecting Python to MySQL</li><li>Performing database operations from Python</li></ul><p>The goal is to build the foundation first and then connect the pieces together.</p><h3>What’s Next?</h3><p>In the next chapter, we’ll look at <strong>Creating Databases and Tables in MySQL</strong>.</p><p>We’ll cover:</p><ul><li>Creating a database</li><li>Creating tables</li><li>Choosing the data we want to store</li><li>Introducing basic constraints</li><li>Keeping our data structured and organised</li></ul><p>From there, we’ll move closer to connecting MySQL with Python and interacting with our databases programmatically.</p><p>Follow me here on Medium if you’d like to continue the series. Let’s keep building. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5ceca9971040" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Joining Tables Using SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/joining-tables-using-sqlalchemy-orm-ee2629469ed8?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/ee2629469ed8</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Thu, 10 Sep 2026 22:42:18 GMT</pubDate>
            <atom:updated>2026-09-11T22:25:52.257Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JDAUBSE-p5xFVFv3" /></figure><h3>Why Table Joins Matter in Databases</h3><p>Relational databases store data in multiple tables to ensure <strong>data integrity, avoid redundancy, and improve efficiency</strong>. However, to retrieve meaningful insights, we often need to <strong>combine data from multiple tables</strong>.</p><p>SQLAlchemy ORM provides a <strong>Pythonic and efficient way to join tables</strong>, making it easy to perform complex queries using relationships and built-in join methods. This guide covers:</p><p>✅ <strong>INNER JOIN</strong> → Fetch only matching records from both tables<br> ✅ <strong>LEFT OUTER JOIN</strong> → Fetch all records from one table and matching records from the other<br> ✅ <strong>RIGHT OUTER JOIN</strong> → Fetch all records from the right table and matching records from the left<br> ✅ <strong>FULL OUTER JOIN</strong> → Fetch all records from both tables, with NULL values where no match exists<br> ✅ <strong>SELF JOIN</strong> → Join a table with itself</p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database with ORM models set up (e.g., Employee and Department)</p><h3>Setting Up the Database Connection</h3><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br><br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining ORM Models with Relationships</h3><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, ForeignKey<br><br>Base = declarative_base()<br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;))<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>Performing Joins Using SQLAlchemy ORM</h3><h3>INNER JOIN (Default ORM Join Behavior)</h3><p>An <strong>INNER JOIN</strong> returns only records with matching values in both tables.</p><pre>employees = session.query(Employee).join(Department).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} works in {employee.department.department_name}&quot;)</pre><h3>LEFT OUTER JOIN (Retrieve All from Left Table, Matching from Right)</h3><p>A <strong>LEFT JOIN</strong> retrieves all records from the left table and matching ones from the right table.</p><pre>from sqlalchemy.orm import joinedload<br><br>employees = session.query(Employee).options(joinedload(Employee.department)).all()<br>for employee in employees:<br>    department = employee.department.department_name if employee.department else &quot;No Department&quot;<br>    print(f&quot;{employee.name} - {department}&quot;)</pre><h3>RIGHT OUTER JOIN (Simulating in SQLite)</h3><p>SQLite does not support <strong>RIGHT JOIN</strong> natively, but we can <strong>swap table order in a LEFT JOIN</strong> to achieve the same effect.</p><pre>departments = session.query(Department).outerjoin(Employee).all()<br><br>for department in departments:<br>    employee_names = [emp.name for emp in department.employees]<br>    print(f&quot;{department.department_name} - Employees: {&#39;, &#39;.join(employee_names) if employee_names else &#39;No Employees&#39;}&quot;)</pre><h3>FULL OUTER JOIN (Simulating in SQLite)</h3><p>SQLite <strong>does not support FULL OUTER JOIN</strong>, but we can <strong>combine a LEFT and RIGHT JOIN using UNION</strong>.</p><pre>from sqlalchemy import union_all<br><br># Left join<br>left_stmt = session.query(Employee.name, Department.department_name).join(Department)<br><br># Right join (swapping table order)<br>right_stmt = session.query(Department.department_name, Employee.name).outerjoin(Employee)<br><br># Combine results<br>full_outer_stmt = union_all(left_stmt, right_stmt)<br>results = session.execute(full_outer_stmt)<br>for row in results:<br>    print(row)</pre><h3>SELF JOIN (Joining a Table with Itself)</h3><p>A <strong>SELF JOIN</strong> is used when comparing rows in the same table.</p><h3>Example: Finding Employees with the Same Department</h3><pre>from sqlalchemy import aliased<br><br>e1 = aliased(Employee)<br>e2 = aliased(Employee)<br>same_department = session.query(e1.name, e2.name).join(e2, e1.department_id == e2.department_id).filter(e1.id != e2.id).all()<br>for emp1, emp2 in same_department:<br>    print(f&quot;{emp1} and {emp2} work in the same department&quot;)</pre><h3>Filtering Joined Data</h3><p>We can filter records after joining tables.</p><pre>hr_employees = session.query(Employee).join(Department).filter(Department.department_name == &quot;HR&quot;).all()<br><br>for employee in hr_employees:<br>    print(f&quot;{employee.name} works in HR&quot;)</pre><h3>Sorting Joined Data</h3><p>To sort joined results, use order_by().</p><pre>employees = session.query(Employee).join(Department).order_by(Department.department_name).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name}&quot;)</pre><h3>Fetching Only Specific Columns</h3><p>Instead of retrieving full objects, fetch only selected fields.</p><pre>results = session.query(Employee.name, Department.department_name).join(Department).all()<br><br>for name, department in results:<br>    print(f&quot;{name} - {department}&quot;)</pre><h3>Using Eager Loading for Performance Optimization</h3><p>Lazy loading (default) fetches data only when accessed, whereas <strong>eager loading retrieves all data in one query</strong>, reducing database calls.</p><pre>employees = session.query(Employee).options(joinedload(Employee.department)).all()</pre><h3>Best Practices for Joining Tables in SQLAlchemy ORM</h3><h3>Use join() for Efficient Queries</h3><p>Using join() avoids multiple queries and speeds up data retrieval.</p><h3>Use joinedload() for Eager Loading to Reduce Queries</h3><p>Instead of multiple queries per relationship, use eager loading for efficiency.</p><h3>Use outerjoin() When Dealing with Missing Data</h3><p>When some records may not have a relationship, use outerjoin().</p><pre>employees = session.query(Employee).outerjoin(Department).all()</pre><h3>Practice These Concepts</h3><h3>Challenge: Query Data Using Joins in SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Retrieve all employees with their department names.</li><li>Fetch only employees from the “Finance” department.</li><li>Retrieve employees sorted by department names.</li><li>Implement a self-join to find employees in the same department.</li></ol><h3>Example Solution:</h3><pre>employees = session.query(Employee).join(Department).all()<br><br>print(&quot;\nAll Employees with Departments:&quot;)<br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name}&quot;)<br>finance_employees = session.query(Employee).join(Department).filter(Department.department_name == &quot;Finance&quot;).all()<br><br><br>print(&quot;\nEmployees in Finance:&quot;)<br>for employee in finance_employees:<br>    print(employee.name)<br>sorted_employees = session.query(Employee).join(Department).order_by(Department.department_name).all()<br><br>print(&quot;\nEmployees Sorted by Department:&quot;)<br>for employee in sorted_employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name}&quot;)<br>e1 = aliased(Employee)<br>e2 = aliased(Employee)<br>same_department = session.query(e1.name, e2.name).join(e2, e1.department_id == e2.department_id).filter(e1.id != e2.id).all()<br><br>print(&quot;\nEmployees in the Same Department:&quot;)<br>for emp1, emp2 in same_department:<br>    print(f&quot;{emp1} and {emp2} work in the same department&quot;)</pre><p>Follow me for more SQL and Python insights, practical tutorials, and hands-on coding exercises.</p><p>In the next chapter, we’ll start a new <strong>MySQL in Python</strong> series, where we’ll explore how to connect Python to MySQL and begin working with databases directly from Python. 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ee2629469ed8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building Relationships in SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/building-relationships-in-sqlalchemy-orm-9ec1f1d5185e?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/9ec1f1d5185e</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Tue, 08 Sep 2026 21:17:21 GMT</pubDate>
            <atom:updated>2026-09-11T22:25:54.901Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5Ny13vSNreggHlxy" /></figure><h3>Why Database Relationships Matter</h3><p>In relational databases, data is often stored in multiple tables to <strong>avoid redundancy, improve data integrity, and optimize queries</strong>. Establishing <strong>relationships between tables</strong> is essential for linking related records.</p><p>SQLAlchemy ORM provides a <strong>Pythonic way to define and manage relationships</strong>, allowing developers to:<br> ✅ Establish <strong>One-to-One, One-to-Many, and Many-to-Many</strong> relationships<br> ✅ Use <strong>Foreign Keys and Cascading Deletes</strong> to maintain referential integrity<br> ✅ Efficiently <strong>query related data using ORM relationships</strong></p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database connection set up with SQLAlchemy ORM</p><h3>Setting Up the Database Connection</h3><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining Relationships in SQLAlchemy ORM</h3><h3>One-to-Many Relationship</h3><p>A <strong>One-to-Many</strong> relationship means that one record in Table A can have multiple corresponding records in Table B.</p><h3>Example: One Department Has Many Employees</h3><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, ForeignKey<br><br>Base = declarative_base()<br><br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;, cascade=&quot;all, delete&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;))<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>How This Works</h3><p>✅ <strong>ForeignKey(&quot;departments.id&quot;)</strong> → Establishes a relationship between employees.department_id and departments.id.<br> ✅ <strong>relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;)</strong> → Enables accessing employees from a department object.<br> ✅ <strong>Cascading Delete (</strong><strong>cascade=&quot;all, delete&quot;)</strong> → Deleting a department removes all employees in that department.</p><h3>One-to-One Relationship</h3><p>A <strong>One-to-One</strong> relationship means that each record in Table A <strong>relates to only one</strong> record in Table B.</p><h3>Example: Each Employee Has One Profile</h3><pre>class Profile(Base):<br>    __tablename__ = &quot;profiles&quot;<br>    id = Column(Integer, primary_key=True)<br>    employee_id = Column(Integer, ForeignKey(&quot;employees.id&quot;), unique=True)<br>    bio = Column(String, nullable=True)<br>    employee = relationship(&quot;Employee&quot;, back_populates=&quot;profile&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    profile = relationship(&quot;Profile&quot;, back_populates=&quot;employee&quot;, uselist=False, cascade=&quot;all, delete&quot;)</pre><h3>How This Works</h3><p>✅ <strong>ForeignKey(&quot;employees.id&quot;, unique=True)</strong> → Ensures each profile is linked to only one employee.<br> ✅ <strong>uselist=False</strong> → Ensures the relationship is <strong>One-to-One</strong>, not One-to-Many.</p><h3>Many-to-Many Relationship</h3><p>A <strong>Many-to-Many</strong> relationship means that multiple records in Table A can relate to multiple records in Table B.</p><h3>Example: Employees Can Work on Multiple Projects</h3><pre>from sqlalchemy import Table<br><br>employee_project = Table(<br>    &quot;employee_project&quot;, Base.metadata,<br>    Column(&quot;employee_id&quot;, Integer, ForeignKey(&quot;employees.id&quot;)),<br>    Column(&quot;project_id&quot;, Integer, ForeignKey(&quot;projects.id&quot;))<br>)<br>class Project(Base):<br>    __tablename__ = &quot;projects&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, secondary=employee_project, back_populates=&quot;projects&quot;)<br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    projects = relationship(&quot;Project&quot;, secondary=employee_project, back_populates=&quot;employees&quot;)</pre><h3>How This Works</h3><p>✅ <strong>secondary=employee_project</strong> → Uses an association table to link employees and projects.<br> ✅ <strong>back_populates</strong> → Enables accessing related records from both tables.</p><h3>Querying Data Using Relationships</h3><h3>Query Employees and Their Department</h3><pre>employees = session.query(Employee).join(Department).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} works in {employee.department.department_name}&quot;)</pre><h3>Query Employees with Their Projects (Many-to-Many)</h3><pre>employees = session.query(Employee).all()<br><br>for employee in employees:<br>    project_names = [project.name for project in employee.projects]<br>    print(f&quot;{employee.name} is working on: {&#39;, &#39;.join(project_names)}&quot;)</pre><h3>Deleting Parent Records and Handling Cascades</h3><p>To delete a department along with its employees:</p><pre>department = session.query(Department).filter_by(department_name=&quot;HR&quot;).first()<br><br>if department:<br>    session.delete(department)<br>    session.commit()<br>    print(&quot;Department and all related employees deleted!&quot;)</pre><h3>Best Practices for Building Relationships in SQLAlchemy ORM</h3><h3>Use Foreign Keys for Data Integrity</h3><p>Foreign keys prevent orphaned records and ensure valid relationships.</p><h3>Use Cascading Deletes to Maintain Consistency</h3><p>Define cascade=&quot;all, delete&quot; to ensure dependent records are deleted.</p><h3>Use back_populates Instead of backref for Explicit Relationships</h3><p>✅ <strong>back_populates=&quot;employees&quot;</strong> (Preferred)<br> ❌ <strong>backref=&quot;employees&quot;</strong> (Implicit and less readable)</p><h3>Enable Indexing for Faster Relationship Queries</h3><pre>from sqlalchemy import Index<br><br>Index(&quot;idx_department_id&quot;, Employee.department_id)</pre><h3>Practice These Concepts</h3><h3>Challenge: Implement Relationship Queries Using SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Retrieve all employees and their respective department names.</li><li>Fetch all employees working on a specific project.</li><li>Delete a department and ensure its employees are also deleted.</li></ol><h3>Example Solution:</h3><pre>employees = session.query(Employee).join(Department).all()<br><br>print(&quot;\nAll Employees and Departments:&quot;)<br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name}&quot;)<br>project_employees = session.query(Employee).join(employee_project).join(Project).filter(Project.name == &quot;AI Research&quot;).all()<br>print(&quot;\nEmployees in AI Research Project:&quot;)<br>for employee in project_employees:<br>    print(employee.name)<br>department = session.query(Department).filter_by(department_name=&quot;Finance&quot;).first()<br>if department:<br>    session.delete(department)<br>    session.commit()<br>    print(&quot;Finance department and all employees deleted!&quot;)</pre><h3>What’s Next?</h3><p>In my next post, we’ll explore <strong>Joining Tables Using SQLAlchemy ORM</strong>, where we’ll learn how to perform <strong>INNER JOINs, LEFT JOINs, and complex queries</strong> to retrieve related data efficiently. Follow me for more SQL and Python insights, tutorials, and hands-on coding exercises. Let’s keep building efficient, scalable, and data-driven applications together! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9ec1f1d5185e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deleting Data from a Database Using SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/deleting-data-from-a-database-using-sqlalchemy-orm-35f1ca28a680?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/35f1ca28a680</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Sat, 05 Sep 2026 22:29:08 GMT</pubDate>
            <atom:updated>2026-09-11T22:23:15.153Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*13yFc0dBQvkCJ636" /></figure><h3>Why Deleting Data Matters</h3><p>Removing data from a database is as important as inserting and updating it. Whether you need to <strong>delete outdated records, remove incorrect entries, or enforce data retention policies</strong>, efficiently managing deletions ensures <strong>data consistency and prevents unnecessary storage use</strong>.</p><p>SQLAlchemy ORM provides a <strong>structured and safe way to delete records</strong>, minimizing the risk of accidental data loss while maintaining referential integrity through <strong>cascading deletes and foreign key constraints</strong>.</p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database with ORM models set up (e.g., Employee and Department)</p><h3>Setting Up the Database Connection</h3><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br><br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining ORM Models</h3><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, Float, ForeignKey<br><br>Base = declarative_base()<br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;, cascade=&quot;all, delete&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;, ondelete=&quot;CASCADE&quot;))<br>    salary = Column(Float, nullable=False)<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>Deleting Data Using SQLAlchemy ORM</h3><h3>Deleting a Single Record</h3><p>To delete a specific record, <strong>retrieve it first, then delete it</strong> using session.delete().</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()<br><br>if employee:<br>    session.delete(employee)<br>    session.commit()<br>    print(&quot;Employee record deleted successfully!&quot;)</pre><h3>Deleting Multiple Records</h3><p>To delete multiple records at once, <strong>use </strong><strong>filter() along with </strong><strong>delete()</strong>.</p><pre>session.query(Employee).filter(Employee.department_id == 2).delete()<br>session.commit()<br>print(&quot;All employees in Department 2 deleted!&quot;)</pre><h3>Cascading Deletes for Related Data</h3><p>By defining cascade=&quot;all, delete&quot;, deleting a department will also delete all its associated employees.</p><pre>department = session.query(Department).filter_by(department_name=&quot;HR&quot;).first()<br><br>if department:<br>    session.delete(department)<br>    session.commit()<br>    print(&quot;Department and all related employees deleted!&quot;)</pre><h3>Deleting All Records from a Table</h3><p>To remove <strong>all records</strong> without dropping the table, use:</p><pre>session.query(Employee).delete()<br>session.commit()<br>print(&quot;All employee records deleted!&quot;)</pre><h3>Dropping a Table Using SQLAlchemy ORM</h3><p>To delete an entire table along with its structure, use .drop().</p><pre>Employee.__table__.drop(engine)<br>print(&quot;Employees table dropped successfully!&quot;)</pre><h3>Using Transactions to Prevent Data Loss</h3><p>If an error occurs during deletion, use rollback() to restore data.</p><pre>try:<br>    employee = session.query(Employee).filter_by(name=&quot;Jane Smith&quot;).first()<br>    session.delete(employee)<br>    session.commit()<br>except Exception as e:<br>    session.rollback()<br>    print(f&quot;Delete operation failed: {e}&quot;)</pre><h3>Best Practices for Deleting Data Using SQLAlchemy ORM</h3><h3>Use filter() Instead of Retrieving Records Manually</h3><p>❌ Inefficient approach:</p><pre>employees = session.query(Employee).filter(Employee.salary &lt; 40000).all()<br>for employee in employees:<br>    session.delete(employee)<br>    session.commit()</pre><p>✅ Efficient approach:</p><pre>session.query(Employee).filter(Employee.salary &lt; 40000).delete()<br>session.commit()</pre><h3>Use Cascading Deletes to Maintain Referential Integrity</h3><p>Defining cascade=&quot;all, delete&quot; ensures dependent records are deleted automatically when a parent record is removed.</p><h3>Use Foreign Key Constraints to Prevent Orphaned Records</h3><p>Setting ondelete=&quot;CASCADE&quot; ensures related records are deleted when a referenced record is removed.</p><h3>Enable SQL Logging for Debugging</h3><p>Use echo=True in create_engine() to log all SQL queries.</p><pre>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)</pre><h3>Practice These Concepts</h3><h3>Challenge: Implement Safe Deletion Using SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Remove an employee named “Emily Davis” from the database.</li><li>Delete all employees in the Finance department.</li><li>Ensure all employees with a salary below 40,000 are removed.</li></ol><h3>Example Solution:</h3><pre>session.query(Employee).filter(Employee.name == &quot;Emily Davis&quot;).delete()<br>session.query(Employee).filter(Employee.department_id == 2).delete()<br>session.query(Employee).filter(Employee.salary &lt; 40000).delete()<br>session.commit()<br>print(&quot;Records deleted successfully!&quot;)</pre><h3>What’s Next?</h3><p>In my next post, we’ll explore <strong>Building Relationships in SQLAlchemy ORM</strong>, where we’ll learn how to set up <strong>one-to-one, one-to-many, and many-to-many relationships</strong> between tables, manage foreign keys efficiently, and optimize queries using ORM relationships.</p><p>Follow me for more SQL and Python insights, tutorials, and hands-on coding exercises. Let’s keep building efficient, scalable, and data-driven applications together! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=35f1ca28a680" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Updating Data Using SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/updating-data-using-sqlalchemy-orm-b772fe3928f2?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/b772fe3928f2</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Fri, 04 Sep 2026 22:10:32 GMT</pubDate>
            <atom:updated>2026-09-11T22:27:50.520Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*a-qDDSorC7H9I_G_" /></figure><h3>Why Updating Data Matters</h3><p>Databases store dynamic data, meaning records often need to be modified as circumstances change. Whether updating <strong>employee salaries, modifying user profiles, or changing order statuses</strong>, <strong>efficiently updating data</strong> is crucial for maintaining an accurate and responsive application.</p><p>SQLAlchemy ORM provides a structured and Pythonic way to update records, making it easy to perform <strong>single and bulk updates while maintaining data integrity and avoiding performance bottlenecks</strong>.</p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database with ORM models set up (e.g., Employee and Department)</p><h3>Setting Up the Database Connection</h3><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br><br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining ORM Models</h3><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, Float, ForeignKey<br><br>Base = declarative_base()<br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;))<br>    salary = Column(Float, nullable=False)<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>Updating Data Using SQLAlchemy ORM</h3><h3>Updating a Single Record</h3><p>To update a record, fetch it from the database, modify the object’s attributes, and commit the transaction.</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()<br><br>if employee:<br>    employee.salary = 85000<br>    session.commit()<br>    print(&quot;Employee salary updated successfully!&quot;)</pre><h3>Updating Multiple Records Using update()</h3><p>To update multiple rows at once, use session.query().update().</p><pre>session.query(Employee).filter(Employee.department_id == 1).update({&quot;salary&quot;: Employee.salary * 1.1})<br>session.commit()<br>print(&quot;All HR employee salaries increased by 10%.&quot;)</pre><h3>Updating Using Conditional Logic with case()</h3><p>Use case() when updating different records with different conditions.</p><pre>from sqlalchemy import case<br><br>session.query(Employee).update({<br>    Employee.salary: case([<br>        (Employee.salary &lt; 50000, Employee.salary * 1.2),<br>        (Employee.salary.between(50000, 80000), Employee.salary * 1.1)<br>    ], else_=Employee.salary * 1.05)<br>})<br>session.commit()<br>print(&quot;Employee salaries updated based on salary brackets!&quot;)</pre><h3>Using Transactions to Prevent Data Loss</h3><p>If an error occurs during an update, use rollback() to revert changes.</p><pre>try:<br>    employee = session.query(Employee).filter_by(name=&quot;Jane Smith&quot;).first()<br>    employee.salary = 90000<br>    session.commit()<br>except Exception as e:<br>    session.rollback()<br>    print(f&quot;Update failed: {e}&quot;)</pre><h3>Best Practices for Updating Data Using SQLAlchemy ORM</h3><h3>Use update() for Bulk Updates Instead of Looping</h3><p>❌ Inefficient approach:</p><pre>employees = session.query(Employee).filter(Employee.department_id == 1).all()<br>for employee in employees:<br>    employee.salary *= 1.1<br>    session.commit()</pre><p>✅ Efficient approach:</p><pre>session.query(Employee).filter(Employee.department_id == 1).update({&quot;salary&quot;: Employee.salary * 1.1})<br>session.commit()</pre><h3>Use Transactions (rollback()) to Handle Errors</h3><p>Ensure data consistency by rolling back changes if an error occurs.</p><pre>try:<br>    session.query(Employee).filter(Employee.salary &lt; 40000).update({&quot;salary&quot;: 40000})<br>    session.commit()<br>except:<br>    session.rollback()</pre><h3>Use Logging to Debug Updates</h3><p>Enable SQL logging with echo=True in create_engine().</p><pre>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)</pre><h3>Practice These Concepts</h3><h3>Challenge: Update Employee Records Using SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Update the department of an employee named “Emily Davis” to IT.</li><li>Increase the salaries of all employees in the Sales department by 7%.</li><li>Ensure all employees earning below 40,000 have a minimum salary of 45,000.</li></ol><h3>Example Solution:</h3><pre>session.query(Employee).filter(Employee.name == &quot;Emily Davis&quot;).update({&quot;department_id&quot;: 2})<br>session.query(Employee).filter(Employee.department_id == 3).update({&quot;salary&quot;: Employee.salary * 1.07})<br>session.query(Employee).filter(Employee.salary &lt; 40000).update({&quot;salary&quot;: 45000})<br>session.commit()<br>print(&quot;Employee records updated successfully!&quot;)</pre><h3>What’s Next?</h3><p>In my next post, we’ll explore <strong>Deleting Data from a Database using SQLAlchemy ORM</strong>, where we’ll learn how to safely remove records, handle cascading deletes, and avoid accidental data loss. Follow me for more SQL and Python insights, tutorials, and hands-on coding exercises. Let’s keep building efficient, scalable, and data-driven applications together! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b772fe3928f2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Filter Operations in SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/filter-operations-in-sqlalchemy-orm-01274ec8768b?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/01274ec8768b</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Wed, 02 Sep 2026 22:33:55 GMT</pubDate>
            <atom:updated>2026-09-11T22:29:17.502Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Zvox5Cz5Cit9H0fC" /></figure><h3>Why Filtering Data Matters</h3><p>When working with databases, retrieving all records is rarely useful. Instead, we often need <strong>specific subsets of data</strong> based on conditions. Whether filtering users by age, transactions by date, or employees by salary, <strong>filtering queries efficiently is crucial for performance and accuracy</strong>.</p><p>SQLAlchemy ORM provides a <strong>Pythonic and structured way to filter records</strong>, eliminating the need for complex raw SQL queries. It allows us to:<br> ✅ Retrieve <strong>specific records based on conditions</strong><br> ✅ Apply <strong>multiple filters using logical operators (</strong><strong>AND, </strong><strong>OR, </strong><strong>NOT)</strong><br> ✅ Use <strong>comparison operators (</strong><strong>=, &gt;, &lt;, LIKE)</strong> for advanced filtering</p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database with ORM models set up (e.g., Employee and Department)</p><h3>Setting Up the Database Connection</h3><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br><br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining ORM Models</h3><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, Float, ForeignKey<br><br>Base = declarative_base()<br><br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;)<br><br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;))<br>    salary = Column(Float, nullable=False)<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>Applying Filter Conditions in SQLAlchemy ORM</h3><h3>Filtering Data Using filter()</h3><p>To retrieve specific records based on conditions, use .filter().</p><pre>employees = session.query(Employee).filter(Employee.salary &gt; 60000).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)</pre><h3>Filtering Using filter_by()</h3><p>Use filter_by() when filtering by a single column <strong>using equality (</strong><strong>=) conditions</strong>.</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()<br><br>if employee:<br>    print(f&quot;Employee found: {employee.name}, Salary: {employee.salary}&quot;)</pre><h3>Using Comparison Operators in Filtering</h3><p>SQLAlchemy supports standard comparison operators:<br> ✅ == (equal to)<br> ✅ != (not equal to)<br> ✅ &lt;, &gt;, &lt;=, &gt;= (less than, greater than, etc.)<br> ✅ .like() (matches patterns)</p><pre>high_earners = session.query(Employee).filter(Employee.salary &gt;= 80000).all()</pre><h3>Filtering with LIKE for Pattern Matching</h3><p>Use .like() to match records based on a pattern.</p><pre>employees = session.query(Employee).filter(Employee.name.like(&quot;J%&quot;)).all()<br><br>for employee in employees:<br>    print(employee.name)</pre><h3>Combining Multiple Conditions with AND and OR</h3><h3>Using AND to Apply Multiple Conditions</h3><p>Use and_() to filter by <strong>multiple conditions simultaneously</strong>.</p><pre>from sqlalchemy import and_<br><br>employees = session.query(Employee).filter(and_(Employee.salary &gt; 60000, Employee.department_id == 1)).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)</pre><h3>Using OR to Match Any of Multiple Conditions</h3><p>Use or_() to retrieve records that match <strong>any of multiple conditions</strong>.</p><pre>from sqlalchemy import or_<br><br>employees = session.query(Employee).filter(or_(Employee.department_id == 1, Employee.salary &gt; 90000)).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)</pre><h3>Filtering Using IN and NOT IN</h3><p>Use .in_() to filter records matching <strong>a list of values</strong>.</p><pre>employees = session.query(Employee).filter(Employee.department_id.in_([1, 3])).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department_id}&quot;)</pre><p>Use notin_() to exclude certain values.</p><pre>employees = session.query(Employee).filter(~Employee.department_id.in_([1, 3])).all()</pre><h3>Filtering Using BETWEEN for Ranges</h3><p>Use between() to filter values <strong>within a specific range</strong>.</p><pre>employees = session.query(Employee).filter(Employee.salary.between(50000, 90000)).all()</pre><h3>Filtering NULL and NOT NULL Values</h3><p>Use is_() to check for NULL values.</p><pre>employees = session.query(Employee).filter(Employee.department_id.is_(None)).all()</pre><p>Use isnot() for <strong>NOT NULL</strong> values.</p><pre>employees = session.query(Employee).filter(Employee.department_id.isnot(None)).all()</pre><h3>Sorting and Filtering Together</h3><p>To <strong>filter and sort records</strong>, chain .filter() with .order_by().</p><pre>employees = session.query(Employee).filter(Employee.salary &gt; 60000).order_by(Employee.salary.desc()).all()</pre><h3>Using .limit() with Filters</h3><p>To fetch <strong>only a specific number of records</strong>, use .limit().</p><pre>top_employees = session.query(Employee).filter(Employee.salary &gt; 60000).order_by(Employee.salary.desc()).limit(3).all()</pre><h3>Best Practices for Filtering Data in SQLAlchemy ORM</h3><h3>Use first() Instead of all() When Fetching a Single Record</h3><p>Fetching all records when you only need one is inefficient.</p><p>❌ <strong>Inefficient approach:</strong></p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).all()[0]</pre><p>✅ <strong>Efficient approach:</strong></p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()</pre><h3>Use yield_per() for Large Query Results</h3><p>If querying a large dataset, use yield_per() to avoid loading all results into memory.</p><pre>for employee in session.query(Employee).yield_per(50):<br>    print(employee.name)</pre><h3>Use Indexing to Speed Up Queries</h3><p>Creating an index on frequently filtered columns improves performance.</p><pre>from sqlalchemy import Index<br><br>Index(&quot;idx_salary&quot;, Employee.salary)</pre><h3>Practice These Concepts</h3><h3>Challenge: Apply Filtering Operations Using SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Retrieve employees with a salary above 70,000 and sort them in descending order.</li><li>Fetch employees in the HR or Finance department using OR.</li><li>Retrieve employees whose names start with “M” using LIKE.</li><li>Count the number of employees earning between 50,000 and 90,000.</li></ol><h3>Example Solution:</h3><pre>employees = session.query(Employee).filter(Employee.salary &gt; 70000).order_by(Employee.salary.desc()).all()<br><br>print(&quot;\nEmployees Earning Over 70,000:&quot;)<br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)<br>from sqlalchemy import or_<br><br>hr_finance_employees = session.query(Employee).filter(or_(Employee.department_id == 1, Employee.department_id == 2)).all()<br>print(&quot;\nHR and Finance Employees:&quot;)<br>for employee in hr_finance_employees:<br>    print(employee.name)<br><br>employees_starting_m = session.query(Employee).filter(Employee.name.like(&quot;M%&quot;)).all()<br>print(&quot;\nEmployees Whose Name Starts With &#39;M&#39;:&quot;)<br>for employee in employees_starting_m:<br>    print(employee.name)<br><br>from sqlalchemy import func<br>salary_range_count = session.query(func.count(Employee.id)).filter(Employee.salary.between(50000, 90000)).scalar()<br>print(f&quot;\nNumber of Employees Earning Between 50,000 and 90,000: {salary_range_count}&quot;)</pre><h3>What’s Next?</h3><p>In my next post, we’ll explore <strong>Updating Data Using SQLAlchemy ORM</strong>, where we’ll learn how to modify existing records, update multiple rows efficiently, and apply best practices for database updates. Follow me for more SQL and Python insights, tutorials, and hands-on coding exercises. Let’s keep building efficient, scalable, and data-driven applications together! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=01274ec8768b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Retrieving Data from a Database Using SQLAlchemy ORM]]></title>
            <link>https://sibabalwesinyaniso.medium.com/retrieving-data-from-a-database-using-sqlalchemy-orm-02dfc73d3ed0?source=rss-6b4bbe2935b6------2</link>
            <guid isPermaLink="false">https://medium.com/p/02dfc73d3ed0</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Sibabalwe Sinyaniso]]></dc:creator>
            <pubDate>Tue, 25 Aug 2026 23:04:23 GMT</pubDate>
            <atom:updated>2026-09-11T22:29:26.241Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*HXgrWa6cgvFK4qm-" /></figure><h3>Why Efficient Data Retrieval Matters</h3><p>After inserting data into a database, the next critical step is retrieving it efficiently. Whether you’re fetching user profiles, generating reports, or analyzing business metrics, retrieving data quickly and correctly is essential for performance and usability.</p><p>SQLAlchemy ORM provides a Pythonic way to query data, allowing developers to retrieve records without writing raw SQL. With its session-based querying system, SQLAlchemy ORM makes it easier to perform basic queries, apply filters, sort results, and retrieve related data using relationships.</p><h3>Prerequisites</h3><p>To follow along, ensure you have:<br> ✅ Python installed (3.7 or later recommended)<br> ✅ SQLAlchemy installed (pip install sqlalchemy)<br> ✅ A database with ORM models set up (e.g., Employee and Department)</p><h3>Setting Up the Database Connection and ORM Models</h3><h3>Connecting to the Database</h3><p>Before querying data, we must set up a session to interact with the database.</p><pre>from sqlalchemy import create_engine<br>from sqlalchemy.orm import sessionmaker<br><br>engine = create_engine(&quot;sqlite:///company.db&quot;, echo=True)<br>Session = sessionmaker(bind=engine)<br>session = Session()</pre><h3>Defining the ORM Models</h3><p>We’ll use the Employee and Department tables for our queries.</p><pre>from sqlalchemy.orm import declarative_base, relationship<br>from sqlalchemy import Column, Integer, String, Float, ForeignKey<br><br>Base = declarative_base()<br>class Department(Base):<br>    __tablename__ = &quot;departments&quot;<br>    id = Column(Integer, primary_key=True)<br>    department_name = Column(String, unique=True, nullable=False)<br>    employees = relationship(&quot;Employee&quot;, back_populates=&quot;department&quot;)<br>class Employee(Base):<br>    __tablename__ = &quot;employees&quot;<br>    id = Column(Integer, primary_key=True)<br>    name = Column(String, nullable=False)<br>    department_id = Column(Integer, ForeignKey(&quot;departments.id&quot;))<br>    salary = Column(Float, nullable=False)<br>    department = relationship(&quot;Department&quot;, back_populates=&quot;employees&quot;)</pre><h3>Retrieving Data Using SQLAlchemy ORM</h3><h3>Fetching All Records</h3><p>To retrieve all records from a table, we use session.query(Model).all().</p><pre>employees = session.query(Employee).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.id}: {employee.name} - {employee.salary}&quot;)</pre><h3>Retrieving a Single Record</h3><p>To fetch a specific record, use filter_by() or filter().</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()<br><br>if employee:<br>    print(f&quot;Employee found: {employee.name}, Salary: {employee.salary}&quot;)<br>else:<br>    print(&quot;Employee not found.&quot;)</pre><h3>Sorting Query Results</h3><p>To sort results, use order_by().</p><pre>employees = session.query(Employee).order_by(Employee.salary.desc()).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)</pre><h3>Limiting Query Results</h3><p>To fetch a specific number of records, use limit().</p><pre>top_employees = session.query(Employee).order_by(Employee.salary.desc()).limit(3).all()<br><br>for employee in top_employees:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)</pre><h3>Retrieving Related Data Using Relationships</h3><p>Since Employee and Department have a relationship, we can retrieve employee records along with their department names.</p><pre>employees = session.query(Employee).join(Department).all()<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name}&quot;)</pre><h3>Using count() to Get the Number of Records</h3><p>To count the number of employees in the company:</p><pre>employee_count = session.query(Employee).count()<br>print(f&quot;Total employees: {employee_count}&quot;)</pre><p>To count employees in a specific department:</p><pre>hr_count = session.query(Employee).filter(Employee.department_id == 1).count()<br>print(f&quot;Total employees in HR: {hr_count}&quot;)</pre><h3>Best Practices for Retrieving Data in SQLAlchemy ORM</h3><h3>Use first() Instead of all() When Fetching a Single Record</h3><p>Fetching all records when you only need one is inefficient.</p><p>❌ Inefficient approach:</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).all()[0]</pre><p>✅ Efficient approach:</p><pre>employee = session.query(Employee).filter_by(name=&quot;John Doe&quot;).first()</pre><h3>Use yield_per() for Large Query Results</h3><p>If querying a large dataset, use yield_per() to avoid loading all results into memory.</p><pre>for employee in session.query(Employee).yield_per(50):<br>    print(employee.name)</pre><h3>Use lazy=&#39;joined&#39; to Optimize Relationship Queries</h3><p>To reduce database queries when fetching related data:</p><pre>from sqlalchemy.orm import joinedload<br><br>employees = session.query(Employee).options(joinedload(Employee.department)).all()</pre><h3>Practice These Concepts</h3><h3>Challenge: Retrieve and Analyze Data Using SQLAlchemy ORM</h3><h3>Tasks:</h3><ol><li>Retrieve all employees and display their names, departments, and salaries.</li><li>Fetch employees earning more than 70,000 and sort them by salary (highest first).</li><li>Count the total number of employees in the company.</li><li>Retrieve the highest salary in each department.</li></ol><h3>Example Solution:</h3><pre>employees = session.query(Employee).join(Department).all()<br><br>print(&quot;\nAll Employees:&quot;)<br><br>for employee in employees:<br>    print(f&quot;{employee.name} - {employee.department.department_name} - {employee.salary}&quot;)<br>high_earners = session.query(Employee).filter(Employee.salary &gt; 70000).order_by(Employee.salary.desc()).all()<br>print(&quot;\nEmployees Earning Over 70,000:&quot;)<br><br>for employee in high_earners:<br>    print(f&quot;{employee.name} - {employee.salary}&quot;)<br>employee_count = session.query(Employee).count()<br>print(f&quot;\nTotal Employees: {employee_count}&quot;)<br><br>from sqlalchemy import func<br>highest_salary_per_department = session.query(Department.department_name, func.max(Employee.salary)).join(Employee).group_by(Department.id).all()<br>print(&quot;\nHighest Salary Per Department:&quot;)<br>for department, salary in highest_salary_per_department:<br>    print(f&quot;{department}: {salary}&quot;)</pre><h3>What’s Next?</h3><p>In my next post, we’ll explore <strong>Filter Operations in SQLAlchemy ORM</strong>, where we’ll learn how to apply complex filtering using multiple conditions, logical operators, and advanced query techniques. Follow me for more SQL and Python insights, tutorials, and hands-on coding exercises. Let’s keep building efficient, scalable, and data-driven applications together! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=02dfc73d3ed0" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>